Is it possible to use prepared statements with either MySQLi or PDO and still be able to dynamically add items to the IN part of the query, for example...
$somearray = ['tagvalue1', 'tagvalue2', 'tagvalue3'];
$sql = "SELECT foo FROM bar
WHERE tag IN(?)";
I ask this because I have a situation whereby the number of elements in the IN part is not known until runtime.
You asked:
Is it possible to use prepared statements with either MySQLi or PDO and still be able to dynamically add items to the IN part of the query, for example...
No, unfortunately it is not. It happens that ColdFusion does this, but not php.
While you can't do exactly what you want with a prepared query, you can dynamically generate the $sql string for the query to accomplish the same thing.
Given some array $array = (n, n1, n2, ... nN)
$sql = "SELECT foo FROM bar WHERE tag IN (";
foreach($array as $value) {
$sql .= "'" . $value . "', ";
}
// Strip off the last comma and space from the IN clause
$sql = substr($sql, 0, strlen($sql) - 2);
$sql .= ")";
It's certainly not the most elegant solution and you'll have to do some more data validation or escaping of dangerous characters that a prepared query would handle better, but it will do the job.
As a side note, there are ORM (Object Relational Mapper) libraries that support things like accepting an array of values to generate the IN clause in a database statement. Propel is the one I have most experience with, but I'm sure others like Doctrine would have a similar method.
A propel-ish example would be like
$results = BarQuery::create()
->select('foo')
->filterByTag(array($value1, $value2, ..., $vauleN)
->find();
Lots of added functionality and support, but does increase your initial set up time for a project.
