PHP - Using PDO with IN clause array
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);
$stm->execute($in_array);
$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]);
$stm->execute($params);
$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 .= ($in ? "," : "") . $key; // :id0,:id1,:id2
$in_params[$key] = $item; // collecting values into a key-value array
}
$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.
PDO with WHERE... IN queries
Since you can't mix Values (the Numbers) with control flow logic (the commas) with prepared statements you need one placeholder per Value.
$idlist = array('260','201','221','216','217','169','210','212','213');
$questionmarks = str_repeat("?,", count($idlist)-1) . "?";
$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` IN ($questionmarks)");
and loop to bind the parameters.
PDO Query with in clause that contain an Array won't work
You don't want to bind the imploded list as one element but rather each of the values individually using ?
so the end of the statement would be WHERE EMAIL IN (?,?)
:
$values = ["Email1","Email2"];
# This should give you ?,?
$bindstr = implode(",",array_fill(0,count($values),'?'));
$query = $connection->prepare("SELECT ID FROM USERS WHERE EMAIL IN({$bindstr})");
# Use the raw values individually in the execute
$query->execute($values);
Hopefully that should get results back you are looking for.
PDO with WHERE... IN queries
Since you can't mix Values (the Numbers) with control flow logic (the commas) with prepared statements you need one placeholder per Value.
$idlist = array('260','201','221','216','217','169','210','212','213');
$questionmarks = str_repeat("?,", count($idlist)-1) . "?";
$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` IN ($questionmarks)");
and loop to bind the parameters.
PHP PDO with IN clause and LIMIT
This seems a simpler (and infinitely faster) way of doing much the same thing:
SELECT t.*
FROM following f
JOIN user_tweets t
ON t.user_id = f.following_user_id
WHERE f.user_id=?
ORDER
BY t.tweet_date DESC
MYSQL - PDO with multiple IN clause
Your current query comes out as
SELECT * FROM my_table WHERE (my_value1 IN (?,?,?)) AND (my_value2 IN (?,?,?))
So your execute
usage is incorrect, http://php.net/manual/en/pdostatement.execute.php. It should only be passing one array with values inside it.
An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.
I think using array_merge, http://php.net/manual/en/function.array-merge.php, will allow you to accomplish what you are trying
$stm->execute(array_merge($in_array1,$in_array2));
This way the execute is the equivalent of
$stm->execute(array(1,2,3,4,5,1));
This may seem incorrect because the array pairings are now gone but placeholder 1 (the first question mark) will map to 1
, placeholder 4 to 4
, and so on.
Use PDO to bind array
It's not possible to bind an array to a list used in an IN
clause. You can however bind multiple scalar values like so:
where location.region in (:region1, :region2, :region3, [...])
$params = array(
':region1' => $regions[0],
':region2' => $regions[1],
':region3' => $regions[2]
[...]
);
$stmt->execute($params);
You'd need to programatically build up the IN ($placeholders)
part of the query to handle a dynamic number of parameters.
PHP - Using PDO with IN clause array
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);
$stm->execute($in_array);
$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]);
$stm->execute($params);
$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 .= ($in ? "," : "") . $key; // :id0,:id1,:id2
$in_params[$key] = $item; // collecting values into a key-value array
}
$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.
Related Topics
Codeigniter Call to a Member Function Num Rows on Boolean
Should We Ever Check For MySQLi_Connect() Errors Manually
Comparing Two Dates in a SQL Request
Windows 10 Docker:/Usr/Bin/Env: 'Php\R': No Such File or Directory
Codeigniter - Call to a Member Function Result() on Boolean In
Laravel 5.5 Error 500 in Cpanel Shared Hosting
How to Configure Xampp to Send Mail from Localhost
Reference - Frequently Asked Questions About Pdo
How to Parameterize the Table Name in a Prepared Statement
Call to a Member Function on a Non-Object
How to Get Comma Separated Values from a Table in Laravel
Reference - How to Handle Namespaces (Tags and Attributes With a Colon in Their Name) in Simplexml
How to Convert Array to Simplexml
How to Ping a Server Port With PHP
How to Iterate Utf-8 String in PHP