Viewed   1.2k times

I'm using PDO to execute a statement with an IN clause that uses an array for its values:

$in_array = array(1, 2, 3);
$in_values = implode(',', $in_array);
$my_result = $wbdb->prepare("SELECT * FROM my_table WHERE my_value IN (".$in_values.")");
$my_results = $my_result->fetchAll();

The above code works perfectly fine, but my question is why this doesn't:
 $in_array = array(1, 2, 3);
    $in_values = implode(',', $in_array);
    $my_result = $wbdb->prepare("SELECT * FROM my_table WHERE my_value IN (:in_values)");
    $my_result->execute(array(':in_values' => $in_values));
    $my_results = $my_result->fetchAll();

This code will return the item whose my_value equals the first item in the $in_array (1), but not the remaining items in the array (2, and 3).



PDO is not good with such things. You need to create a string with placeholders dynamically and insert it into the query, while binding array values the usual way. With positional placeholders it would be like this:

$in  = str_repeat('?,', count($in_array) - 1) . '?';
$sql = "SELECT * FROM my_table WHERE my_value IN ($in)";
$stm = $db->prepare($sql);
$data = $stm->fetchAll();

In case there are other placeholders in the query, you could use the following approach (the code is taken from my PDO tutorial):

You could use array_merge() function to join all the variables into a single array, adding your other variables in the form of arrays, in the order they appear in your query:

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE foo=? AND column IN ($in) AND bar=? AND baz=?";
$stm = $db->prepare($sql);
$params = array_merge([$foo], $arr, [$bar, $baz]);
$data = $stm->fetchAll();

In case you are using named placeholders, the code would be a little more complex, as you have to create a sequence of the named placeholders, e.g. :id0,:id1,:id2. So the code would be:

// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];

$ids = [1,2,3];
$in = "";
$i = 0;// we are using an external counter 
// because the actual array keys could be dangerous
foreach ($ids as $item)
    $key = ":id".$i++;
    $in .= "$key,";
    $in_params[$key] = $item; // collecting values into a key-value array
$in = rtrim($in,","); // :id0,:id1,:id2

$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stm = $db->prepare($sql);
$stm->execute(array_merge($params,$in_params)); // just merge two arrays
$data = $stm->fetchAll();

Luckily, for the named placeholders we don't have to follow the strict order, so we can merge our arrays in any order.

Sunday, December 25, 2022

You can use

show databases

or a query on the information_schema:

select schema_name from information_schema.schemata
Monday, September 5, 2022

Not near a terminal to check, but I believe you have to type bind it to INT and send it in as an INT, not as "b010101" (or whatever):

$sql='INSERT INTO test(id,data) VALUES(:id,:bit)';
$stmt->bindValue('id', null, PDO::PARAM_NULL);
$stmt->bindValue('bit', (int)$value, PDO::PARAM_INT);

Quick check on Google brought up this similar previous answer.

Monday, November 7, 2022

Some good old dynamic SQL query cobbling-together...

$sql = sprintf('SELECT * FROM user WHERE name LIKE :name %s %s',
               !empty($_GET['city'])   ? 'AND city   = :city'   : null,
               !empty($_GET['gender']) ? 'AND gender = :gender' : null);


if (!empty($_GET['city'])) {
    $stmt->bindParam(':city', '%'.$_GET['city'].'%', PDO::PARAM_STR);


You can probably express this nicer and wrap it in helper functions etc. etc, but this is the basic idea.

Wednesday, August 31, 2022

I FIXED THE ERROR. The solution for anyone out there is simple, the PHP explode function was used to split the contents of a textarea into separate lines but it doesn't work if you use explode() with PHP_EOL. PHP EOL tells you the server's newline character from what I understand. I used the preg_split instead to perform the splitting and it works on both my localhost that runs on Windows and my server that runs on Linux. Thank you everyone for your help!!!

$conn = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                if(isset($_POST["busnumber"], $_POST["busroute"])){
                    $stops = preg_split("/\r\n|\r|\n/", $_POST['busroute']);
                    $sql = 'SELECT * FROM stops WHERE stop_name LIKE :stop';
                    $statement = $conn->prepare($sql);
                    foreach($stops as $stop){
                        $statement->bindValue(':stop', $stop);
                        while($result = $statement->fetch()){
                            echo $result['stop_id'].' '.$result['stop_name'].'</br>';
Wednesday, November 2, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :