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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
