php - 设置然后按案例选择订单

Question

我想做的是编写一个查询来获取所有行，但将与特定值匹配的复合主键放在返回列表的顶部。

但是，当涉及到 MySQL 和 PHP 时，我是一个巨大的 n00b。这实际上是我第一次尝试使用 SET 并执行 SELECT FROM WHERE ORDER BY 或 INSERT INTO WHERE 以外的查询。

我尝试了以下代码，但我收到一个 fatal error ，内容为 SQLSTATE[42S02]: Base table or view not found: 1109 Unknown table，我认为这与第一个 execute 语句。我以前从未SET变量，所以我很茫然...

include "includes/connect_to_database.php";

$sql = "SET @url = " . $_SERVER['HTTP_HOST'];
$stmt = $connection->prepare($sql);
$stmt->execute();

$sql = "SELECT sld, tld, ask, floor FROM table ORDER BY CASE WHEN CONCAT (sld, '.', tld) = @url THEN 0 ELSE 1 END, CONCAT (sld, '.', tld)";
$stmt = $connection->prepare($sql);
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

我哪里错了？

Answer 1

如果你一次使用变量@url，你可以这样做

include "includes/connect_to_database.php";

$sql = "SELECT sld, tld, ask, floor FROM table ORDER BY CASE WHEN CONCAT (sld, '.', tld) = :host THEN 0 ELSE 1 END, CONCAT (sld, '.', tld)";
$stmt = $connection->prepare($sql);
$stmt->execute([':host' => $_SERVER['HTTP_HOST']]);

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);