MySQL Query Question?

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

LIMIT 11, 20 Also, you don't need a group by if you have no aggregate functions (count, sum, etc). With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. — MJB, Jul 01 '10 at 02:58
@MJB I don't want to limit the images from the last query I want to display them all — label, Jul 01 '10 at 02:59
So anything after the first 10? That would be LIMIT 11, 99999 or whatever maxint is. The MySQL Docs [http://dev.mysql.com/doc/refman/5.0/en/select.html] has pretty clear directions. — MJB, Jul 01 '10 at 03:02

Carl · Answer 1 · 2010-07-01T13:07:19.937

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.

score 0 · Answer 2 · answered Jul 01 '10 at 03:27

0

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)

answered Jul 01 '10 at 03:27

James Davies

9,224
5
36
41

I don't want to limit the images from the last query I want to display them all starting from where I left off from the second query. – label Jul 01 '10 at 03:32
LIMIT 11, 99999999999 would do the trick. Bit of a hack, but would work. – James Davies Jul 05 '10 at 01:43

score 0 · Answer 3 · edited May 23 '17 at 12:13

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

MySQL Query Question?

3 Answers3