我有一个想要内爆的数组,然后传递给一个查询并吐出所有匹配的值,但我得到的只是一个1064 violation
,我肯定我错过了一些愚蠢的语法。
$filmImplode = implode("','", $filmList);
$query = "
SELECT
watch.film_id,
films.film_api_id
FROM
watch
INNER JOIN films ON films.film_id = watch.film_id
WHERE
films.film_api_id IN ('$filmImplode')
AND watch.user_id = :user_id";
$query_params = array(':user_id' => 1);
try {
$stmt = $db->prepare($query);
$getWatched = $stmt->execute($query_params);
} catch (PDOException $ex) {
echo 'something went wrong' . $ex->getMessage();
}
$getWatched = $stmt->fetchAll();
SQL错误读取
出了问题状态[42000]:
语法错误或访问冲突:1064您的SQL中有错误句法;查看与MySQL服务器版本相对应的手册要在附近使用正确的语法第3行的"item0"、"item1"、"tem2"、"tme3"、"elem4"
我看到两个潜在问题:
- 你可能会把电影名字加上引号,这会打乱你的查询。逃离他们
: user_id
参数中有空格
试试这个:
array_walk($filmList, function(&$film){
$film = $db->quote($film);
});
$filmImplode = implode(",", $filmList);
$query = "
SELECT
watch.film_id,
films.film_api_id
FROM
watch
INNER JOIN films ON films.film_id = watch.film_id
WHERE
films.film_api_id IN ($filmImplode)
AND watch.user_id = :user_id";
$query_params = array(':user_id' => 1);
try {
$stmt = $db->prepare($query);
$getWatched = $stmt->execute($query_params);
} catch (PDOException $ex) {
echo 'something went wrong' . $ex->getMessage();
}
$getWatched = $stmt->fetchAll();
如本文所建议的,一种更好的方法是动态构建IN
条件,写入占位符,然后在prepare
方法中绑定参数。
问题似乎是围绕filmImplode。你的film_api_ids是int吗?如果没有,您应该确保它们在SQL中作为字符串常量传递。
WHERE films.film_api_id IN ('XXX-123-XX', 'YYY-456-YY')
代替
WHERE films.film_api_id IN (XXX-123-XX, YYY-456-YY)
此外,这些单引号看起来很可疑,如果所有filmId都是整数,请尝试不使用单引号。
WHERE films.film_api_id IN ($filmImplode)