SQL startrow endrow?

sonuku

eep, im trying to make my SQL statemtnes as efficient as possible, so as not to select all the rows from the database, when its relly not needed...

once again, im a coldfusion kiddy, and i learnt everything backwards... so im not too amazing with SQL statements...

what im trying to do, is select * from table, but only 20 rows, starting at row 51

example of my script:

<?php
$q = "SELECT * FROM gallery where catagory = '$cat' ORDER by id desc limit '$max' startrow = '$start'";

$gallery = mysql_query($q); 
?>

but it no work 😛

any help out there?
(the vars work fine, and output their values.... so i suppose it could look like this:

<?php
$q = "SELECT * FROM gallery where catagory = 'user/random' ORDER by id desc limit '40' startrow = '80'";

$gallery = mysql_query($q); 
?>

so... yeah...
any ideas on how to define startrow in the SQL statement, unstead of doing an i++ until the row i want it displayed?

;_;

lurv sonuku

The_Chancer

Look at the limit function again - you would use LIMIT 50,20 to give the next 20 rows after 50.... ie 51,52....70

Look up the SELECT syntax in the http://www.mysql.com manual

seby

$q = "SELECT * FROM gallery where catagory = '$cat' ORDER by id DESC LIMIT 51,20";

The_Chancer

It needs to be LIMIT 50,20 as the offset is from 0 not 1....

http://www.mysql.com/doc/en/SELECT.html#IDX1409
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. The arguments must be integer constants. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): To be compatible with PostgreSQL MySQL also supports the syntax: LIMIT # OFFSET #.
mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15

sonuku

oooooh

ok, thanks ^_
maybe i should rtfm 🙂

The_Chancer

Aye - 'tis a good place to start.....