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

The error means that you have less/more bind values than are expected in the query. I would echo out counts of each bindValue loop to find the culprit. — Vladimir Ramik
– Vladimir Ramik, Commented Mar 12, 2016 at 18:46
No, number of arguments is the same as in the query, I've already checked. I chose 3 books and 2 authors and in my query WHERE b.id IN (?,?,?) and WHERE author_id IN (?,?) ) — Heidel
– Heidel, Commented Mar 12, 2016 at 18:53
Can you run the query directly as it should be with values? Binding 'IN' may not be working correctly. — Vladimir Ramik
– Vladimir Ramik, Commented Mar 12, 2016 at 19:53

Community · Accepted Answer · 2017-05-23 11:59:45Z

There are problems with your code for building a dynamic query. When building a dynamic query you need to separate those parts of the query that are static from those that are dynamic.

You can see that the following code is static.

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

And also

" GROUP BY b.id
  ORDER BY b.id";

The rest of the code is dynamic. When filtering records the WHERE clause is used and the AND & OR operators are used to filter records based on more than one condition. The AND operator displays a record if both the first condition AND the second condition are true. The OR operator displays a record if either the first condition OR the second condition is true. so for the first condition WHERE is used but after that AND or OR must be used(using OR in your example)

// Static code
sql = "SELECT * FROM `table`"
// Set initial condition to WHERE       
clause = "WHERE";       
if( !empty( filter )){
    Add clause to sql 
    Add condition to sql
    change clause to OR or AND as required
}
Repeat for each filter
Note the filter is not changed until a filter is  not empty and remains changed once changed.
The remaining static code is added after all the filters have been handled

To allow different filters to be applied you can use a flag.

$flag = 0;
if(isset($_POST['books'])){
    $books_ids = $_POST["books"];
    $flag += 1;

}
if(isset($_POST['authors'])){
    $authors_ids = $_POST["authors"];
    $flag += 10;
}
if(isset($_POST['stores'])){
    $stores_ids = $_POST["stores"];
    $flag += 100;
}

Use "lazy" binding when possible - passing data into execute will dramatically shorten your code. See PDO info You require to merge array to perform this. Using switch statement with the flag you merge the arrays required.

switch ($flag) {
    case 1:
        $param_array = $books_ids;
        break;
    case 10:
        $param_array = $authors_ids;
        break;
    case 100:
        $param_array = $stores_ids;
        break;
    case 11://books & authors
        $param_array = array_merge($books_ids, $authors_ids); 
        break;
    case 101://books & stores
        $param_array = array_merge($books_ids, $stores_ids); 
        break;
    case 110://authors & stores
        $param_array = array_merge($authors_ids, $stores_ids); 
        break;
     case 111://books & authors & stores
        $param_array = array_merge(array_merge($books_ids,$authors_ids),$stores_ids);
        break;  

}

if( !empty( $query )) {
    $stmt = $conn->prepare($query); 
    $stmt->execute($param_array);
    $results = $stmt->fetchAll();
    echo json_encode($results);
}

The following code uses the above points. I have echoed some lines to indicate results which can be removed once testing is done.Also some code has been commented out for testing.

//Set flag
$flag = 0;
if(isset($_POST['books'])){
    $books_ids = $_POST["books"];
    $flag += 1;
}
if(isset($_POST['authors'])){
    $authors_ids = $_POST["authors"];
    $flag += 10;
}
if(isset($_POST['stores'])){
    $stores_ids = $_POST["stores"];
    $flag += 100;
}
echo $flag. " <BR>";//Remove after testing

//Basic SQL statement 
$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 ";
// Set initial condition to WHERE       
$clause = "WHERE";      

if( !empty( $books_ids ))
{
    $books_ids_in = implode(',', array_fill(0, count($books_ids), '?'));
    $query .= $clause;
    $query .= " b.id IN (". $books_ids_in .")";
    // Set condition to OR for additional condition
    $clause = " OR ";   
}
if( !empty( $authors_ids ) )
{
    $authors_ids_in = implode(',', array_fill(0, count($authors_ids), '?'));

    /*  This part commented out as I don't see relevance
        if (!empty($query)) {
         $query .= " UNION ";
    }
    */
    $query .= $clause;
    $query .= "    b.id IN (
            SELECT DISTINCT book_id FROM books_authors WHERE author_id IN (". $authors_ids_in .")
            )";
    // Set condition to OR for additional condition     
    $clause = " OR ";       

}


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

    /* if (!empty($query)) {
         $query .= " UNION ";
    }
    */
    $query .= $clause;
    $query .= " b.id IN (
            SELECT DISTINCT book_id FROM books_stores WHERE store_id IN (". $stores_ids_in .")
            )";
    $clause = " OR ";
}

//Add GROUP & ORDER
    $query .= " GROUP BY b.id
    ORDER BY b.id";
echo $query; //Remove after testing
//building $param_array
switch ($flag) {
    case 1:
        $param_array = $books_ids;
        break;
    case 10:
        $param_array = $authors_ids;
        break;
    case 100:
        $param_array = $stores_ids;
        break;
    case 11://books & authors
        $param_array = array_merge($books_ids, $authors_ids); 
        break;
    case 101://books & stores
        $param_array = array_merge($books_ids, $stores_ids); 
        break;
    case 110://authors & stores
        $param_array = array_merge($authors_ids, $stores_ids); 
        break;
     case 111://books & authors & stores
        $param_array = array_merge(array_merge($books_ids,$authors_ids),$stores_ids);
        break;  

}
echo "<br>";
print_r($param_array);// remove after testing
/*
if( !empty( $query )) {
    $stmt = $conn->prepare($query); 
    $stmt->execute($param_array);
    $results = $stmt->fetchAll();
    echo json_encode($results);
}

$conn = null;

The problem is, I can use all 3 filters or only 1 or 2 of them, so I can have $books_ids, $authors_ids and $stores_ids arrays all together or only 1 or 2 of them. In this case I have got errors like Notice: Undefined variable: books_ids in C:\xampp\htdocs\bookstore\filter.php on line 130 Warning: array_merge(): Argument #1 is not an array in C:\xampp\htdocs\bookstore\filter.php on line 130

itzmukeshy7 · Accepted Answer · 2016-03-14 07:42:47Z

0

Don't use same $k; use a variable and increment it with each bind; See below

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

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

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

answered Mar 14, 2016 at 7:42

itzmukeshy7

2,6781 gold badge24 silver badges30 bronze badges

Collectives™ on Stack Overflow

Binding multiple arrays in pdo

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related