[PHP-DB] PDO prepared statements and value list for MySQL "IN"
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.)
Thanks for any help!
- TK
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"
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.)
>>
>>Thanks for any help!
>>
>>- TK
>
>
>$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,
>
>~Philip
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).
- TK
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"
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
Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"
At 05:53 PM 7/8/2008, TK wrote: >>>I'd like to use a PDO prepared statement to perform a MySQL query >>>that uses the IN function. I may have found my answer (in the PHP manual, under PDO->prepare(). Go figure!): "You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement." Perhaps this means it cannot be done. - TK -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PDO prepared statements and value list for MySQL "IN"
At 03:23 AM 7/10/2008, Thodoris wrote:
>Why don't you work this around. Since you may do anything with strings in php
>using the (.) operator try this:
>
>$a = array('string1','string2');
>$str = implode("','",$a);
>$str = "'".$str."'";
>print $str;
>?>
Thanks, but this is missing the entire point of my question, which is *can it
be done with PDO prepared statements*. These solutions you're proposing do not
involve PDO's prepared statements. I already know how to do SQL queries by
hand.
Anyway, per my latest message, I found where in the PHP manual it implies that
PDO prepared statements cannot be used with the MySQL IN function, which speaks
to my original question.
Thanks for trying!
- TK
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
