CODEWITHSUNDEEP
phpmysqlpdo
Mohib
Mohib

Reputation: 467

number of bound variables does not match number of tokens with sql "IN"

this is my PHP Code: 

$roomNo = [1,2,8];

        $inQuery = implode(',', array_fill(0, count($roomNo), '?'));



        $totalRoom = $pdo->prepare('SELECT *
                                        FROM room
                                        WHERE categoryId = ?
                                        AND
                                        id NOT IN (' . $inQuery . ')
                                    ');

        $totalRoom->bindValue(1, $id, PDO::PARAM_INT);
        foreach ($roomNo as $key => $value) {
            $totalRoom->bindValue(($key+1), $value);
        }
        $totalRoom->execute();
        $totalRoom = $totalRoom->fetchAll();

Here is the error :

Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /home/mohib/MEGA/PHP/Core Project/Hotel-Room-Booking/controller/booking-check-form.controller.php:66

Upvotes: 1

Views: 878

Answers (1)

nospi
nospi

Reputation: 121

In your foreach($roomNo as $key => $value)... the first $key==0. $key+1 will re-bind categoryId.

Try this:

$totalRoom->bindValue(($key+2), $value);

EDIT

Forgot to mention, no need for the loop if you're using IN(), you can just bind a string.

$roomNo = [1,2,8];
$inQuery = implode(',', $roomNo);

$query = "SELECT * FROM room WHERE categoryId = ? AND id NOT IN (?)";
$totalRoom = $pdo->prepare($query);

$totalRoom->bindValue(1, $id, PDO::PARAM_INT);
$totalRoom->bindValue(2, $inQuery, PDO::PARAM_STR);

$totalRoom->execute();
$totalRoom = $totalRoom->fetchAll();

To avoid mixing up indices you can also use named bind references:

$query = "SELECT * FROM rooms WHERE categoryId = :catId AND id NOT IN (:roomIds)";
$totalRoom->bindValue(":catId", $id, PDO::PARAM_INT);
$totalRoom->bindValue(":roomIds", $inQuery, PDO::PARAM_STR);

Upvotes: 1

Related Questions