CODEWITHSUNDEEP
mysqlphpmyadmin
Richard Šimek
Richard Šimek

Reputation: 77

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

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.

Upvotes: 0

Views: 477

Answers (1)

Barmar
Barmar

Reputation: 781255

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;

Upvotes: 2

Related Questions