help with LIMIT x,y

mikawhat

Either LIMIT is a bit tricky or i'm doing something wrong...
I have 21 rows extracted from a db, and i want to display them in 3 table columns... <td>records 1-7</td><td>records 8-14</td><td>records 15-21</td>

$query1 = @mysql_query("SELECT * FROM table ORDER BY id LIMIT 0 , 6");
$query2 = @mysql_query("SELECT * FROM table ORDER BY id LIMIT 7 , 13");
$query3 = @mysql_query("SELECT * FROM table ORDER BY id LIMIT 14 , 20");

Are those queries correct for what i'm trying to do?

stolzyboy

the first is how many you want to limit, the second is the offset, so your first one is saying limit results to 0 starting at 6, second says 7 results starting at 13, third is 14 results starting at 20

what you want is something the following... i can't recall right off if the offset starts at 0 or 1 so you'll have to test that

limit 7, 0
limit 7, 7
limit 7, 14

i think this'll work, without testing it's hard to say

mikawhat

i think it's the opposite... limit 0, 7 - limit 7,7 - limit 14,7...it's working like that anw..
one more thing... there's a case that the total rows are 19...so divided by 3 (number of table columns) using floor it's 6... using ceil it's 7 though. With ceil i'll get 7-7-5, and with floor i'll get 6-6-6...which means 1 row is missing... any help about this?

stolzyboy

not sure how to answer on your second question... but the reason the 7,0 doesn't work is that it's in reverse when you don't use the OFFSET keywords

LIMIT 7 OFFSET 0 is reverse of LIMIT 7, 0

Sxooter

For reasons I'll never understand, MySQL chose to make the order

limit <offset>, <limit>

when

limit <limit>, <offset>

seems so much more natural to me.

PostgreSQL used to support the second version, but now only supports the fully spelt out version:

limit <limit> offset <offset>

which is much more readable, and portable between the two databases.

I would suggest using that syntax as it is much more obvious what's happening when you look at it.