ORDER BY date while also using LIMIT in a MySQL query - PHP

Question

HI,

I can't quite figure this out, I'm trying to pull records from MySQL, order them reverse-chronologically and limit the results to four per page (and using pagination to organize the pages). It is currently returning this error:

Fatal error: SQL in /Users/allan/Sites/4is_site/casestudylist.php on line 126

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
$query = "SELECT * FROM studies  ORDER BY date desc WHERE niche = '{$_GET['niche']}' $limit";

The pagination has been working fine, just now that I've tried to add the ORDER BY that I'm getting problems.

Answer 1

That syntax is incorrect. This should work.

$escapedNiche = mysql_real_escape_string($_GET['niche']);
$query = "SELECT * FROM studies WHERE niche = '$escapedNiche' ".
         "ORDER BY date DESC $limit";

For future reference - SELECT Syntax

Answer 2

BTW To escape $_GET['niche']:

$clean_niche = mysql_escape_string($_GET['niche']);
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
$query = "SELECT * FROM studies  "
        ."WHERE niche = '{$clean_niche}' "
        ."ORDER BY date desc $limit";

This will help to protect you from SQL injection attacks by escaping the characters in $_GET['niche'].

For extra security, consider using bound parameters (google'em) and/or library functions like Zend DB to access the database.