CODEWITHSUNDEEP
mysql
label
label

Reputation: 11

MySQL Query Question?

Lets say I display the first 10 images of a user in the MySQL database and then I run a second query to display the rest of the other users images from where the last query left off. How would my second query look like?

Here is how my first query below looks like.

SELECT *
FROM images
WHERE images.user_id = '$user_id'
LIMIT 0, 10

Upvotes: 1

Views: 73

Answers (3)

Carl
Carl

Reputation: 7564

Take a look at the OFFSET keyword (googling "mysql offset" got me to enough documentation). Not entirely portable, but if you know you're using MySQL/PostGreSQL, it should be fine.

Another (potentially better) way to go:

SELECT * FROM images WHERE images.user_id = '$user_id' AND someprimarykey NOT IN ( ...first query, only selecting someprimarykey...)

Of course, if you have an auto-incrementing primary key for the table (which is often a good idea), that AND just becomes AND primarykey > '$highest_p_key_from_first_call' which I suspect you could store somewhere temporarily after the first call.

Upvotes: 1

Ian Lesperance
Ian Lesperance

Reputation: 5139

You effectively want to run the same query again with OFFSET 10 but without specifying a limit. Unfortunately it's not possible to specify an offset without a limit. It's also not possible to specify a limit of infinity, e.g. LIMIT ∞ OFFSET 10 or LIMIT 10, ∞.

But while you can't ask for infinite rows, you can get close enough. This is discussed in another question:

Mysql Offset Infinite rows

Upvotes: 0

James Davies
James Davies

Reputation: 9869

The simplest solution would be

SELECT *
FROM images
WHERE images.user_id = '$user_id'
LIMIT 11, 20

(Keeping in mind if the database changes between calls, you might miss a row or get a duplicate this way)

Upvotes: 0

Related Questions