Array to String Conversion Error / PDO execute array in array

Question

So i'm having an issue executing a query using PDO execute. The error I keep getting back from the server is that I have an array to string conversion. It then follows up by telling me that there is also an invalid number of parameters entered into the execute(array()) line of code. I also get a 1 buffer in my error code, which means there may be some bad concatenation.

The function is a search page where the user inputs up to 6 parameters to search for a book by. There are 6 if statements to check for emptys and if not empty then the variable is added to the array params and part of an sql query concatenated to the end of the beginning statement. Here is the pertinent code:

$title = $_POST['title'];
$author = $_POST['author'];
$publisher = $_POST['publisher'];
$isbn = $_POST['isbn'];
$condition = $_POST['condition'];
$status = $_POST['status'];

//array to hold variables
$params = array();
$concat_stmt_orig = "SELECT * FROM Book WHERE ";
$concat_stmt = "SELECT * FROM Book WHERE ";

//Check for Title
if(!empty($title)){
    //add title to the end
    $concat_stmt = $concat_stmt." `Title` = ?";
    //add variable to print list
    array_push($params, $title);

}
//Check for Author
if(!empty($author)){
    if(strlen($concat_stmt) !== strlen($concat_stmt_orig)){
        //add author to the end
        $concat_stmt = $concat_stmt." AND `Author1` = ?";
        //add variable to print list
        array_push($params, $author);
    }
    else{
        //add author to the end
        $concat_stmt = $concat_stmt." `Author1` = ?";
        //add variable to print list
        array_push($params, $author);
    }
}

//Check for publisher
if(!empty($publisher)){
    if(strlen($concat_stmt) !== strlen($concat_stmt_orig)){
        //add publisher to the end
        $concat_stmt = $concat_stmt." AND `Publisher` = ?";
        //add variable to print list
        array_push($params, $publisher);
    }
    else{
        //add pubisher to the end
        $concat_stmt = $concat_stmt." `Publisher` = ?";
        //add publisher to print list
        array_push($params, $publisher);
    }
}
//check for ISBN
if(!empty($isbn)){
    if(strlen($concat_stmt) !== strlen($concat_stmt_orig)){
        //add isbn to the end
        $concat_stmt = $concat_stmt." AND `ISBN` = ?";
        //add variable to print list
        array_push($params, $isbn);
    }
    else{
        //add pubisher to the end
        $concat_stmt = $concat_stmt." `ISBN` = ?";
        //add publisher to print list
        array_push($params, $isbn);
    }
}
//check for condition
if(!empty($condition)){
    if(strlen($concat_stmt) !== strlen($concat_stmt_orig)){
        //add condition to the end
        $concat_stmt = $concat_stmt." AND `BookCondition` = ?";
        //add condition to print list
        array_push($params, $condition);
    }
    else{
        //add condition to the end
        $concat_stmt = $concat_stmt." `BookCondition` = ?";
        //add condition to print list
        array_push($params, $condition);
    }
}

//check for status
if(!empty($status)){
    if(strlen($concat_stmt) !== strlen($concat_stmt_orig)){
        //add status to the end
        $concat_stmt = $concat_stmt." AND `Status` = ?";
        //add variable to print list
        array_push($params, $status);
    }
    else{
        //add condition to the end
        $concat_stmt = $concat_stmt." `Status` = ?";
        //add condition to print list
        array_push($params, $status);
    }
}

 //prepare statement for querying
 $stmt_retrieve = $db->prepare($concat_stmt);

 echo print_r($stmt_retrieve);
 var_dump($params);

 //exectue query
 $stmt_retrieve->execute(array($params));
}

echo and var dump were for testing purposes to check for the sql statement and the correct number of array values

Here are the errors

PDOStatement Object ( [queryString] => SELECT * FROM Book WHERE Title = ? AND Author1 = ? ) 1array(2) { [0]=> string(13) "Example Title" [1]=> string(14) "Example Author" } Notice: Array to string conversion in...."yadayadayada"

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens'...."yadayadayada"

I tried entering the post values as '$title', '$author'...etc but that still produced the errors, I've also tried printf and others.

I only get these errors when I try to input more than one variable to search by. When I only search by one variable the program has no errors but still won't return anything.

Any help or guidance is appreciated :)

This would probably be a bit easier to follow using an associative array and named placeholders. Also, try and compile your conditions in an array then join with "AND" as a last step. Having double branches the whole way down is massive duplication of effort and asking for trouble. — tadman
– tadman, Commented Nov 25, 2014 at 17:56
Thanks for the tip @tadman , will be sure to do it that way in the future :) — Michael J Caboose
– Michael J Caboose, Commented Nov 25, 2014 at 19:34

Wrikken · Accepted Answer · 2014-11-25 18:53:50Z

2

Use $stmt_retrieve->execute($params);

Currently, you have the equivalent of:

$stmt_retrieve->execute(array(array('title','author1',....)));

See the double array there?

@tadman is right that your current code has a massive code duplication which quickly becomes unmaintainable.

Something like this would help:

$fields = array(
   'Title'   => $_POST['title'],
   'Author1' => $_POST['author'],
   ....);

$params = array();
$wheres = array();

foreach($fields as $fieldname => $value){
    if(!empty($value)){
        $wheres[] = "`$fieldname` = ?";
        $params[] = $value;
    }
}
$stmt = 'SELECT * FROM Book';
if(!empty($wheres)) $stmt .= ' WHERE '.implode(' AND ',$wheres);
$stmt_retrieve = $db->prepare($stmt);
$stmt_retrieve->execute($params);

edited Nov 25, 2014 at 18:53

answered Nov 25, 2014 at 18:47

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael J Caboose Over a year ago

Good catch, fixed that issue with the array in an array and it worked. I agree, my method is extremely sloppy. Thanks for putting up a better way to do it, I didn't know that the implode() and explode() methods existed. Gotta go through the documentation more. Thanks again.

tadman Over a year ago

Nice fix there with the implode`.

Collectives™ on Stack Overflow

Array to String Conversion Error / PDO execute array in array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related