MySQL query returns 0 rows in PHP, actual records in phpmyadmin

Question

I am trying to get rows from table - specific range of rows to be clear. My query looks like this (just part of the code):

SET @i=0;
SELECT * FROM images WHERE (@i:=@i+1) BETWEEN 1 AND 10;

What is does is get row number of rows and returns rows, whose row number is in given range. When I run this query in phpmyadmin, I can see the result of the query (the actual rows in that range), however the number of rows from query is 0. I am pretty sure the problem is with this part

WHERE (@i:=@i+1)

I have searched throught other answers, but the reason was always in php, not the query itself.

Then why tag it with php if the issue is in mysql/phpmyadmin ? — Epodax
– Epodax, Commented Oct 8, 2015 at 10:36
You should show us how the query is invoked from your php code. Did you check if the above query returned any errors within php? — Shadow
– Shadow, Commented Oct 8, 2015 at 10:40

Barmar · Accepted Answer · 2015-10-08 10:37:58Z

2

When you use phpMyAdmin, each query opens a new database connection, and user variables don't persist between connections. Set the variable in a subquery that you join:

SELECT i.*
FROM images AS i
CROSS JOIN (SELECT @i := 0) AS x
WHERE (@i := @i + 1) BETWEEN 1 AND 10;

answered Oct 8, 2015 at 10:37

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Richard Šimek Over a year ago

That's it! Thanks a million!

Marc Delisle Over a year ago

@barmar well, the i variable persists, because we get 10 results in phpMyAdmin; it's just the row counting that does not work. I tried initializing i to 5 and got only 5 rows in the results.

Barmar Over a year ago

Did you send both queries in one command? Maybe it's just showing the row count for the first query. SET doesn't return anything, so its row count is 0.

Collectives™ on Stack Overflow

MySQL query returns 0 rows in PHP, actual records in phpmyadmin

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related