php resultset,PHP returns the resultset of query by primary key

在PHP中使用PDO从数据库获取按出版日期降序排列的前X条记录时,发现结果总是按照主键顺序返回。代码显示已经设置`ORDER BY publicationDate DESC`,但实际输出仍按主键id升序。问题可能在于预处理语句的`:order`参数绑定。
摘要由CSDN通过智能技术生成

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?

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值