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

Question

Here is my code below:

$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.";
}

Every time it's run, $fetch[0] ALWAYS returns 0, despite the $stmt query returning more than 0. What's going on?

Answer 1

I suggest you run PDO in exception mode...

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

Then you would see the query error.

The problem is, you can't use interpolate params like you have in your LIKE comparison. Try this instead...

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

There's also an easier way to check for existence...

$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