CODEWITHSUNDEEP
phpmysqlmysqlirowsmysql-num-rows
user3160134
user3160134

Reputation: 199

MySQL num_rows doesnt return value (SELECT INTO)

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?

Upvotes: 0

Views: 351

Answers (5)

xzag
xzag

Reputation: 604

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.

Upvotes: 1

aaaaahhhhh
aaaaahhhhh

Reputation: 151

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.

Upvotes: 0

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

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}';");

Upvotes: 1

Pradeep
Pradeep

Reputation: 46

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.

Upvotes: 0

Alireza Fallah
Alireza Fallah

Reputation: 4607

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...

Upvotes: 0

Related Questions