Binding multiple arrays in pdo

Question

There are 3 different filters: books, authors and stores (select lists), and I may use their all together at once or only one or two of them, so I use UNION to get together all queries

require('database.php');

if(isset($_POST['books'])){
    $books_ids = $_POST["books"];
}
if(isset($_POST['authors'])){
    $authors_ids = $_POST["authors"];
}
if(isset($_POST['stores'])){
    $stores_ids = $_POST["stores"];
}

$query = "";

if( !empty( $books_ids ))
{
    $books_ids_in = implode(',', array_fill(0, count($books_ids), '?'));

    $query .= "SELECT
        b.id,
        b.`name`,
        b.`year`,
        GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
        GROUP_CONCAT(DISTINCT s.`name`) AS store_names,
        'book' as param
    FROM
        books AS b
        LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
        LEFT JOIN authors AS a ON a.id = b_a.author_id
        LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id
        LEFT JOIN stores AS s ON s.id = b_s.store_id
    WHERE
        b.id IN (". $books_ids_in .")
    GROUP BY b.id
    ORDER BY b.id";
}

if( !empty( $authors_ids ) )
{
    $authors_ids_in = implode(',', array_fill(0, count($authors_ids), '?'));

    if (!empty($query)) {
         $query .= " UNION ";
    }

    $query .= "SELECT
        b.id,
        b.`name`,
        b.`year`,
        GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
        GROUP_CONCAT(DISTINCT s.`name`) AS store_names,
        'author' as param
    FROM
        books AS b
        LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
        LEFT JOIN authors AS a ON a.id = b_a.author_id
        LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id
        LEFT JOIN stores AS s ON s.id = b_s.store_id
    WHERE
        b.id IN (
            SELECT DISTINCT book_id FROM books_authors WHERE author_id IN (". $authors_ids_in .")
            )
    GROUP BY b.id
    ORDER BY b.id";
}

if( !empty( $stores_ids ) )
{
    $stores_ids_in = implode(',', array_fill(0, count($stores_ids), '?'));

    if (!empty($query)) {
         $query .= " UNION ";
    }

    $query .= "SELECT
        b.id,
        b.`name`,
        b.`year`,
        GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
        GROUP_CONCAT(DISTINCT s.`name`) AS store_names,
        'store' as param
    FROM
        books AS b
        LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
        LEFT JOIN authors AS a ON a.id = b_a.author_id
        LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id
        LEFT JOIN stores AS s ON s.id = b_s.store_id
    WHERE
        b.id IN (
            SELECT DISTINCT book_id FROM books_stores WHERE store_id IN (". $stores_ids_in .")
            )
    GROUP BY b.id
    ORDER BY b.id";
}


if( !empty( $query )) {

    $stmt = $conn->prepare($query); 

    if( !empty( $books_ids ))
    {
        foreach ($books_ids as $k => $id) {
            $stmt->bindValue(($k+1), $id);
        }
    }

    if( !empty( $authors_ids ))
    {
        foreach ($authors_ids as $k => $id) {
            $stmt->bindValue(($k+1), $id);
        }
    }

    if( !empty( $stores_ids ))
    {
        foreach ($stores_ids as $k => $id) {
            $stmt->bindValue(($k+1), $id);
        }
    }

    $stmt->execute();
    $results = $stmt->fetchAll();
    echo json_encode($results);
}

$conn = null;

code works just fine when I use only one filter, but when I try to use 2 or more, I get error

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in C:\xampp\htdocs\bookstore\filter.php:123 Stack trace: #0 C:\xampp\htdocs\bookstore\filter.php(123): PDOStatement->execute() #1 {main} thrown in C:\xampp\htdocs\bookstore\filter.php on line 123

I guess, something's wrong with bindValue using but I don't know how to fix that?

UPD var_dump($query) (3 books and 2 authors chosen)
string(1097) "SELECT b.id, b.name, b.year, GROUP_CONCAT(DISTINCT a.name) AS author_names, GROUP_CONCAT(DISTINCT s.name) AS store_names, 'book' as param FROM books AS b LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id LEFT JOIN authors AS a ON a.id = b_a.author_id LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id LEFT JOIN stores AS s ON s.id = b_s.store_id WHERE b.id IN (?,?,?) GROUP BY b.id ORDER BY b.id UNION SELECT b.id, b.name, b.year, GROUP_CONCAT(DISTINCT a.name) AS author_names, GROUP_CONCAT(DISTINCT s.name) AS store_names, 'author' as param FROM books AS b LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id LEFT JOIN authors AS a ON a.id = b_a.author_id LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id LEFT JOIN stores AS s ON s.id = b_s.store_id WHERE b.id IN ( SELECT DISTINCT book_id FROM books_authors WHERE author_id IN (?,?) ) GROUP BY b.id ORDER BY b.id" 01201

Binding multiple arrays in pdo

2 Answers