0

When I run this code in PHP the values are returned correctly

$result = $mysqli->query("SELECT `id`, `last_key_ID` FROM `customers` WHERE `email` = '{$email}';");

But as soon as I add the INTO-syntax to it, the num_rows returns no value:

$result = $mysqli->query("SELECT `id`, `last_key_ID` INTO @id, @last_key_id FROM `customers` WHERE `email` = '{$email}';");

The echo just one line after SELECT-INTO code prints nothing

echo $result->num_rows;

PS: The same SELECT-INTO code seems to run in MySQL-console perfectly, why doesnt it work in PHP?

user3160134
  • 199
  • 11

5 Answers5

1

try:

$mysqli->query('SET @id := 0');
$mysqli->query('SET @last_key_id := 0');
$result = $mysqli->query("SELECT @id:=id, @last_key_id:=last_key_ID FROM `customers` WHERE `email` = '{$email}';");
Sudhir Bastakoti
  • 99,167
  • 15
  • 158
  • 162
1

Since SELECT ... INTO statement doesn't return data directly (even in MySQL console it will not diplay the result of the SELECT statement and only something like 

   Query OK, 1 row affected (0.01 sec)

you can't use num_rows function.

So use echo $result->affected_rows instead.

xzag
  • 594
  • 3
  • 12
0

MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension.

Instead, MySQL Server supports the INSERT INTO ... SELECT standard SQL syntax, which is basically the same thing.

Example...

Read more...

Alireza Fallah
  • 4,609
  • 3
  • 31
  • 57
0

reason is simple, You are assigning simple query output into an session variable into mysql.

Assignment is taking place on backend. Now you have to fetch that mysql session variable.

Try this query :

$result = $mysqli->query("SELECT `id`, `last_key_ID` INTO @id, @last_key_id FROM `customers` WHERE `email` = '{$email}';SELECT @id, @last_key_id;");

Hope this will help you.

Pradeep
  • 46
  • 5
0

Pradeep posted one answer that seems good enough to make it work. But i still don't see any particular reason of making use of SELECT INTO statement.

I'll say unless you have special reason to use it, it's better to use normal SELECT statement like this

$result = $mysqli->query("SELECT `id`, `last_key_ID` FROM `customers` WHERE `email` = '{$email}';");

And then, use mysql_fetch_assoc to retrieve the data from different columns. This will give you slightly better performance and less workload for DB server.

aaaaahhhhh
  • 151
  • 3
  • The reason I use variables, is beause I need to use them in a few more MySQL statements, pradeeps answer didnt help me return correct num_rows values, but sudhirs answer did. – user3160134 Jan 20 '14 at 09:56
  • Got it. It's good that you get what you want though. :)@user3160134 – aaaaahhhhh Jan 20 '14 at 09:57