I am trying to fetch the first X rows from a database by their publication date in descending order, however I found that while the query works perfectly well if executed directly on my local database, in PHP the resultset is always in the order of the primary keys. Here is the piece of code:
public static function getList( $numRows=1000000, $order="publicationDate DESC" ) {
$conn = new PDO( db_host, db_user, db_pw );
$sql = "SELECT SQL_CALC_FOUND_ROWS *, UNIX_TIMESTAMP(publicationDate) AS publicationDate FROM blogs
ORDER BY " . ":order" . " LIMIT :numRows";
$st = $conn->prepare( $sql );
$st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
$st->bindValue( ":order", $order, PDO::PARAM_STR);
$st->execute();
$list = array();
while ( $row = $st->fetch() ) {
echo $row['id'];
$article = new Blogpost( $row );
$list[] = $article;
}
// Now get the total number of articles that matched the criteria
$sql = "SELECT FOUND_ROWS() AS totalRows";
$totalRows = $conn->query( $sql )->fetch();
$conn = null;
return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );
}
When this runs, the output from the echos is as follows: 50 51 52 53 54
The database was created using this query (to show you structure):
DROP TABLE IF EXISTS blogs;
CREATE TABLE blogs
(
id smallint unsigned NOT NULL auto_increment,
publicationDate date NOT NULL,
title varchar(255) NOT NULL,
summary text NOT NULL,
content mediumtext NOT NULL,
views int(11) NOT NULL,
previewPic varchar(255) NOT NULL,
PRIMARY KEY (id)
);
I've tried running different queries and it is always returned in ascending order of the primary key. Why?