我正在构建一个自动完成下拉框,我有一个功能SQL调用,将找到我正在搜索的东西,但当我试图将其集成到一个适当的PDO语句时,我无法动摇这个错误信息:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND city LIKE CONCAT('van', '%') ORDER BY display_value' at line 1' in /home/asdf/index-ajax.php:61
Stack trace:
#0 /home/asdf/index-ajax.php(61): PDOStatement->execute(Array)
#1 {main}
thrown in /home/asdf/index-ajax.php on line 61
下面是我的代码:
$qry = $CFG["DBH"]->prepare("SELECT DISTINCT CONCAT( city, ', ', prov_stat, ', ', country ) AS display_value FROM `contact` AND city LIKE CONCAT('%', :contactCity, '%') ORDER BY display_value;");
$qry->execute(array(':contactCity' => "van"));
我试着找了一些关于这条信息的参考资料,为什么会发生以及如何处理它,但它似乎很常见,而且我还没有读到任何包含灵丹妙药的东西。有人能看到我的代码中的问题吗?
我写SQL已经有一段时间了,但是你的列列表似乎有些不对劲:
$qry = $CFG["DBH"]->prepare("SELECT DISTINCT CONCAT( city, ', ', prov_stat, ', ', country ) AS display_value FROM `contact` AND city LIKE CONCAT('%', :contactCity, '%') ORDER BY display_value;");
$qry->execute(array(':contactCity' => "van"));
我不认为你可以用AND
连接两个列,特别是在你选择了表之后。此外,据我所知,您不能直接在SELECT
中过滤查询(WHERE
)。
像这样可以工作:
$qry = $CFG["DBH"]->prepare(
"SELECT DISTINCT
CONCAT( city, ', ', prov_stat, ', ', country ) AS display_value,
city
FROM `contact`
WHERE city LIKE CONCAT('%', :contactCity, '%')
ORDER BY display_value;"
);
$qry->execute(array(':contactCity' => "van"));