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注入的吸引力较小

$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=后没有值，查询无效。