PHP/MySQL using ORDER BY with a LIMIT

Question

I created a pagination by roughly following this link:

http://www.awcore.com/dev/1/3/Create-Awesome-PHPMYSQL-Pagination_en#toggle

quite cool. Although I have an issue with my query.

It works fine like this:

require 'includes/function.php';

    $page = (int) (!isset($_GET["page"]) ? 1 : $_GET["page"]);
    $limit = 8;
    $startpoint = ($page * $limit) - $limit;

    $statement = "cars WHERE deleted = 'no'";

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

while ($row = mysql_fetch_assoc($query)) {

However when I try to add an ORDER BY to this, like so:

require 'includes/function.php';

    $page = (int) (!isset($_GET["page"]) ? 1 : $_GET["page"]);
    $limit = 8;
    $startpoint = ($page * $limit) - $limit;

    $statement = "cars WHERE deleted = 'no'";

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

while ($row = mysql_fetch_assoc($query)) {

or just change the statement like this:

$statement = "rcr_cars WHERE deleted = 'no' ORDER BY model DESC";

I get this error:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in filepath/myfile.php on line 79.

Line 79 is this line:

 while ($row = mysql_fetch_assoc($query)) {

Can anyone tell me how I am not using the ORDER BY correctly, its got me puzzled. :/

You are not using it correctly, `ORDER BY` must come before `LIMIT` statement. Switch them and it should work. — N.B., Feb 05 '13 at 12:14
http://stackoverflow.com/questions/4708708/mysql-order-by-limit — Déjà vu, Feb 05 '13 at 12:15

score 3 · Accepted Answer · edited May 23 '17 at 12:24

3

Try the query as below

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

Where you have gone wrong is LIMIT should come after ORDER BY. Read more

edited May 23 '17 at 12:24

Community

1
1

answered Feb 05 '13 at 12:19

Techie

44,706
42
157
243

score 0 · Answer 2 · answered Feb 05 '13 at 12:16

0

Change the query:

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

answered Feb 05 '13 at 12:16

Romain

407
2
9
20

score 0 · Answer 3 · answered Feb 05 '13 at 12:27

0

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

answered Feb 05 '13 at 12:27

Aboelseoud

556
1
7
22

PHP/MySQL using ORDER BY with a LIMIT

3 Answers3