O/H TK ??????:
At 03:21 AM 7/9/2008, Thodoris wrote:
At 04:16 PM 7/8/2008, Philip Thompson wrote:
On Jul 8, 2008, at 11:55 AM, TK wrote:
I'd like to use a PDO prepared statement to perform a MySQL query
that uses the IN function.
I.e.:
$stmt = $pdo->prepare('
select *
from mytable
where myfield IN (:contents)
);
$stmt->bindValue(':contents', $contents);
$stmt->execute();
Here's the problem:
If $contents is set to a single value, everything's fine:
$contents = 'mystring';
How can I include multiple values in here? If $contents is set to
an array, PHP throws an "Array to string conversion" notice.
i.e. $contents = array('mystring1', 'mystring2');
If $contents is set to a comma-separated list, no matches are
returned (probably because the entire "list" is being passed to
MySQL as a single value, not multiple values).
I.e. $contents = 'mystring1,mystring2';
What's the proper way to do this? Can it be done? (Note that I do
not know how many elements will be in $contents ahead of time, so
something like IN (:contents1, :contents2) wouldn't make sense.)
$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = "SELECT * FROM `table` WHERE `field` IN ($ins)";
I'm not sure if the IN function only works on numeric values or if it
also works on strings as well. If contents contains strings, you may
need to put single quotes around each item. If so, change the above
to.....
$ins = "'" . implode ("','", $contents) . "'";
Hope that helps,
Thanks. That's how to use the IN function in the first place, which I already know. What I was asking about was how to do this using PDO and prepared statements. It's the PDO prepared statement functionality that's got me stuck. The issue is that I can't figure out how to bindParam or bindValue in this situation, where there is potentially a list of values (of arbitrary size).
Perhaps the implode suggested above is the solution for the prepared statement. I think that the problem is that you cannot bind a value with an array.
So you have to implode the array into a string before binding it to a value. The other goes as you suggested:
$stmt = $pdo->prepare('
select *
>from mytable
where myfield IN (:contents)
);
$stmt->bindValue(':contents', $contents);
$stmt->execute();
Where contents is:
$pre_contents = array('mystring1', 'mystring2');
$contents = implode(',',$pre_contents);
or
$contents = 'mystring';
As per my original question, that does not work:
If $contents is set to a comma-separated list, no matches are
returned (probably because the entire "list" is being passed to
MySQL as a single value, not multiple values).
I.e. $contents = 'mystring1,mystring2';
Binding a comma-separated list (i.e. what implode creates) does not do what is wanted here - it appears instead to treat the whole "list" as one entry, i.e. it's like doing this:
where myfield IN ('mystring1,mystring2')
which is obviously not achieving the desired result of:
where myfield IN ('mystring1','mystring2')
Hence, my original question! Is there a way to accomplish this with PDO and prepared statements?
- TK
Why don't you work this around. Since you may do anything with strings in php using the (.) operator try this:
<?php
$a = array('string1','string2');
$str = implode("','",$a);
$str = "'".$str."'";
print $str;
?>
I will print out:
'string1','string2'
--
Thodoris
