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: PDO prepared statements and value list for MySQL "IN"

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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: PDO prepared statements and value list for MySQL "IN"

--------------020304050004010202070100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>
>
>
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';

--
Thodoris


--------------020304050004010202070100--

CURL and ASP

Has anyone here experience of CURLing .asp pages which use session
cookies as I am having difficulty doing so on two different sites, both asp.

Thanks,

John

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

CURL and ASP

On Wed, Jul 9, 2008 at 9:35 AM, ioannes wrote:
> Has anyone here experience of CURLing .asp pages which use session cookies
> as I am having difficulty doing so on two different sites, both asp.

Forwarded to PHP-General, John. If you're not already subscribed
there, please subscribe to continue to follow this thread.

If you're referring to using cURL from PHP to grab or spider pages
written in ASP on a remote server, no, I've had no problems at all.
In fact, once the content is served on the web, server-side language
matters nil. It all comes out in the standard HTML/JavaScript/etc.
format, and all HTTP policies and procedures (such as session
handling) are [pretty-much] universal.

--

Dedicated Servers - Intel 2.4GHz w/2TB bandwidth/mo. starting at just
$59.99/mo. with no contract!
Dedicated servers, VPS, and hosting from $2.50/mo.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

CURL and ASP

On Wed, Jul 9, 2008 at 9:35 AM, ioannes wrote:
> Has anyone here experience of CURLing .asp pages which use session cookies
> as I am having difficulty doing so on two different sites, both asp.

Forwarded to PHP-General, John. If you're not already subscribed
there, please subscribe to continue to follow this thread.

If you're referring to using cURL from PHP to grab or spider pages
written in ASP on a remote server, no, I've had no problems at all.
In fact, once the content is served on the web, server-side language
matters nil. It all comes out in the standard HTML/JavaScript/etc.
format, and all HTTP policies and procedures (such as session
handling) are [pretty-much] universal.

--

Dedicated Servers - Intel 2.4GHz w/2TB bandwidth/mo. starting at just
$59.99/mo. with no contract!
Dedicated servers, VPS, and hosting from $2.50/mo.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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: 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: PDO prepared statements and value list for MySQL "IN"

--------------080406080209060901050402
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit



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:


$a = array('string1','string2');

$str = implode("','",$a);

$str = "'".$str."'";

print $str;

?>


I will print out:

'string1','string2'

--

Thodoris


--------------080406080209060901050402--

Re: 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

Re: PDO prepared statements and value list for MySQL "IN"

------=_Part_12723_19675586.1215699811459
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

As you already know, you will dynamically create the $contents.

See if you can dynamically create the entire prepare statement which
includes the $contents, as well as dynamically create the bindValue
statement; then see if you can "eval" those dynamically created statements.

$commandPrepare = '$stmt = $pdo->prepare(\'
select *
from mytable
where myfield IN ('.$dynamicallyCreatedContents.')\'
);';
eval($commandPrepare);

$commandBind = '$stmt->bindValue... {escape quotes as needed -- I haven't
tested it}
eval($commandBind);
$stmt->execute();

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

------=_Part_12723_19675586.1215699811459--