php - 在 SELECT 语句中绑定(bind)排序属性

Question

对于带有排序顺序的 SELECT 查询，我准备了这个语句:

$direction = 'DESC';
$project_id = 1;

$query = $this->connection->prepare('SELECT text FROM marker WHERE project_id = :project_id ORDER BY time :direction');
$query->bindValue(':project_id', $project_id, PDO::PARAM_INT);
$query->bindValue(':order', $direction, PDO::PARAM_STR);
$success = $query->execute();

但是失败了。所以我准备了这个声明:

$query = $this->connection->prepare('SELECT text FROM marker WHERE project_id = :project_id ORDER BY time '.$direction.'');
$query->bindValue(':project_id', $project_id, PDO::PARAM_INT);
$success = $query->execute();

而且有效。

不能绑定(bind) ORDER 属性吗？如何正确绑定(bind) ORDER 属性？

Answer 1

您可以使用case 语句来表达您想要的内容。这是一个相对简单的表达式:

order by (case when :order = 'DESC' then time end) DESC,
         time ASC