pagination sql query syntax

问题:

I am trying to sort my posts in my discussion board by date. Here is my code:

$query = mysql_query("SELECT * FROM {$statement} 
LIMIT {$startpoint} , {$limit} 
ORDER BY datetime ASC");

Is there anything syntactically wrong with this? If not, what else could be wrong? Basically what is happening, is that the results are not showing up. I remove the Order by, and it works (but of course it's not sorted...)

回答1:

Order by should go before limit:

$query = mysql_query("SELECT * FROM {$statement} 
ORDER BY datetime ASC LIMIT {$startpoint} , {$limit}");

回答2:

try this:

$query = mysql_query("SELECT * FROM {$statement} 
ORDER BY datetime ASC LIMIT {$startpoint} , {$limit} ");

回答3:

May be a bit too late, but mySQL provides OFFSET key word that is very useful...especially for pagination

mysql_query("SELECT * FROM {$statement} ORDER BY datetime ASC LIMIT {$limit} OFFSET {offset}

if 10 records per page is what you are looking for and you want to show records on page 2 i.e. records 11-20 your query will look like:

 mysql_query("SELECT * FROM {$statement} ORDER BY datetime ASC LIMIT 10 OFFSET 10

REF: http://www.petefreitag.com/item/451.cfm