sql - Mysql 语句不适用于多个 AND 子句

Question

所以这个 sql 旨在从项目表中返回 1 个随机项目

$sql = "select title, id, user_id, priority from projects
where ( closed = 0 and priority between 7 and 9 AND  user_id=$user_id )
ORDER by rand(), priority
limit 1";

无论我如何用括号等重新排列它们，查询都会返回无效错误，例如

Notice: Undefined variable: user_id in /var/www/html/producerswip/includes/functions.php on line 734

Fatal error: Uncaught PDOException: 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 ') ORDER by rand(), priority limit 1' at line 2 in /var/www/html/producerswip/includes/functions.php:737 Stack trace: #0 /var/www/html/producerswip/includes/functions.php(737): PDO->prepare('select title, i...') #1 /var/www/html/producerswip/index.php(72): getRandomProject() #2 {main} thrown in /var/www/html/producerswip/includes/functions.php on line 737

更新
这是一个全脸掌的时刻。我非常专注于 sql 语句错误，我忽略了实际说明错误的第一行。感谢@David 和其他睁大眼睛的人。我还接受了@David 的建议，并将我的 sql 更改为以下内容，这对 sql 注入(inject)的吸引力较小

$sql = "select title, id, user_id, priority from projects
where ( closed = ? and priority between ? and ? AND user_id=? )
ORDER by rand(), priority
limit 1";
$results = $pdo->prepare($sql) or die(mysqli_error($pdo));
$results->execute([0,7,9,$user_id]);

Answer 1

看起来像您的 $user_id变量未定义，这意味着您的结果查询将变为以下内容:

where ( closed = 0 and priority between 7 and 9 AND  user_id= )
ORDER by rand(), priority
limit 1

由于 user_id= 之后没有值，查询无效。