php - 为什么我的 PDO::FETCH 语句返回 0？

Question

下面是我的代码:

$barcode = $_POST['barcode'];
$year = $_POST['year'];
$movietitle = $_POST['movietitle'];
$stmt = $pdo->prepare("SELECT COUNT(movietitle) FROM movies WHERE movietitle LIKE '%:movie%'");
$stmt->bindParam(':movie', $movietitle);
$stmt->execute();
$fetch = $stmt->fetch();
if($fetch[0] == 0) {    
    $displaytitle = $_POST['displaytitle'];
    $media = $_POST['media'];
    $youtube = $_POST['youtube'];
    $genre = $_POST['genre'];
    $youtube = "www.youtube.com/embed/" . $youtube;
    $stmtins = $pdo->prepare("INSERT INTO `movies` VALUES (null, :genre, :movietitle, :displaytitle, :year, :youtube, :media, :barcode)");
    $stmtins->bindParam(':genre', $genre);
    $stmtins->bindParam(':movietitle', $movietitle);
    $stmtins->bindParam(':displaytitle', $displaytitle);
    $stmtins->bindParam(':year', $year);
    $stmtins->bindParam(':youtube', $youtube);
    $stmtins->bindParam(':media', $media);
    $stmtins->bindParam(':barcode', $barcode);
    $stmtins->execute();
    $message = "The movie was added to your database. - $fetch[0]";
} else {
    $message = "Already owned.";
}

每次运行时，$fetch[0] 总是返回 0，尽管 $stmt 查询返回的值大于 0。这是怎么回事？

Answer 1

我建议您在异常模式下运行 PDO...

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

然后你会看到查询错误。

问题是，您不能像在 LIKE 比较中那样使用插值参数。试试这个...

"SELECT COUNT(movietitle) FROM movies WHERE movietitle LIKE CONCAT('%', :movie, '%')"

还有一种更简单的方法来检查是否存在......

$stmt = $pdo->prepare("SELECT 1 FROM movies WHERE movietitle LIKE CONCAT('%', :movie, '%')");
$stmt->bindParam(':movie', $movietitle);
$stmt->execute();
if(!$stmt->fetch()) {
    // fetch will return false if there are no rows