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
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
[PHP-DB] 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
[PHP-DB] CURL and ASP
On Wed, Jul 9, 2008 at 9:35 AM, ioannes [EMAIL PROTECTED] 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. -- /Daniel P. Brown 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 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
[PHP-DB] Apache crashes every time I call mysqli_fetch_assoc()
Hi,
I have set up an environment on my Vista laptop comprising of Apache 2.2,
MySQL Server 5.1 and Php 5.2.5.
I'm using a simple php program that I found in a PHP/SQL book. The PHP
program queries a MySQL table and traverses the results array of the query
and should display on screen. However, there seems to be a problem with my
environment and as every time I run the program I get the error *Apache
HTTP server stopped working and was closed*.
Below is the code I am using, but I have managed to narrow the problem down
to *$row = mysqli_fetch_assoc($result);*
For some reason this is causing the crash. Would there be any reason that
this function cant be found perhaps? I really have no clue!!!
?php
/* Program: petDisplay.php
* Desc:Displays all pets in selected category.
*/
?
html
headtitlePet Catalog/title/head
body
?php
$user=root;
$host=localhost;
$password=PASSWORD;
$database = am;
$cxn = mysqli_connect($host,$user,$password,$database)
or die (couldn't connect to server);
$pettype = horse; //horse was typed in a form by user
$query = SELECT * FROM Pet WHERE petType='$pettype';
$result = mysqli_query($cxn,$query)
or die (Couldn't execute query.);
/*Display results in a table */
$pettype = ucfirst($pettype).s;
echo h1$pettype/h1;
echo table cellspacing='15';
echo trtd colspan='3'hr //td/tr;
while($row = mysqli_fetch_assoc($result))
{
extract($row);
$f_price = number_format($price,2);
echo tr\n
td$petName/td\n
td$petDescription/td\n
td style='text-align: right'\$$f_price/td\n
/tr\n;
echo trtd colspan='3'hr //td/tr\n;
}
echo /table\n;
?
/body/html
Also, please see messages I have copied from the Apache error log:
[Wed Jul 09 19:30:09 2008] [notice] Parent: child process exited with
status 3221225477 -- Restarting.
[Wed Jul 09 19:30:12 2008] [notice] Apache/2.2.6 (Win32) PHP/5.2.5
configured -- resuming normal operations
[Wed Jul 09 19:30:12 2008] [notice] Server built: Sep 5 2007 08:58:56
[Wed Jul 09 19:30:12 2008] [notice] Parent: Created child process 4936
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Child process is running
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Acquired the start mutex.
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Starting 250 worker
threads.
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Starting thread to listen
on port 80.
Any help would be much apprecieted.
Thanks,
Bonger
Re: [PHP-DB] Apache crashes every time I call mysqli_fetch_assoc()
On Wed, Jul 9, 2008 at 1:32 PM, Bonger O [EMAIL PROTECTED] wrote:
Hi,
I have set up an environment on my Vista laptop comprising of Apache 2.2,
MySQL Server 5.1 and Php 5.2.5.
I'm using a simple php program that I found in a PHP/SQL book. The PHP
program queries a MySQL table and traverses the results array of the query
and should display on screen. However, there seems to be a problem with my
environment and as every time I run the program I get the error *Apache
HTTP server stopped working and was closed*.
Below is the code I am using, but I have managed to narrow the problem down
to *$row = mysqli_fetch_assoc($result);*
For some reason this is causing the crash. Would there be any reason that
this function cant be found perhaps? I really have no clue!!!
?php
/* Program: petDisplay.php
* Desc:Displays all pets in selected category.
*/
?
html
headtitlePet Catalog/title/head
body
?php
$user=root;
$host=localhost;
$password=PASSWORD;
$database = am;
$cxn = mysqli_connect($host,$user,$password,$database)
or die (couldn't connect to server);
$pettype = horse; //horse was typed in a form by user
$query = SELECT * FROM Pet WHERE petType='$pettype';
$result = mysqli_query($cxn,$query)
or die (Couldn't execute query.);
/*Display results in a table */
$pettype = ucfirst($pettype).s;
echo h1$pettype/h1;
echo table cellspacing='15';
echo trtd colspan='3'hr //td/tr;
while($row = mysqli_fetch_assoc($result))
{
extract($row);
$f_price = number_format($price,2);
echo tr\n
td$petName/td\n
td$petDescription/td\n
td style='text-align: right'\$$f_price/td\n
/tr\n;
echo trtd colspan='3'hr //td/tr\n;
}
echo /table\n;
?
/body/html
Also, please see messages I have copied from the Apache error log:
[Wed Jul 09 19:30:09 2008] [notice] Parent: child process exited with
status 3221225477 -- Restarting.
[Wed Jul 09 19:30:12 2008] [notice] Apache/2.2.6 (Win32) PHP/5.2.5
configured -- resuming normal operations
[Wed Jul 09 19:30:12 2008] [notice] Server built: Sep 5 2007 08:58:56
[Wed Jul 09 19:30:12 2008] [notice] Parent: Created child process 4936
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Child process is running
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Acquired the start mutex.
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Starting 250 worker
threads.
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Starting thread to listen
on port 80.
Any help would be much apprecieted.
Thanks,
Bonger
Have you checked that mysqli is compiled into PHP?
--
Bastien
Cat, the other other white meat
Re: [PHP-DB] Apache crashes every time I call mysqli_fetch_assoc()
Thanks for the reply Bastien.
How do you mean compiled into PHP? I have *extension=php_mysqli.dll* enabled
in my *php.ini* file. Is that what you mean?
I'm pretty sure it's intalled ok as I don't have any problems with other
mysqli functions.
On Wed, Jul 9, 2008 at 8:02 PM, Bastien Koert [EMAIL PROTECTED] wrote:
On Wed, Jul 9, 2008 at 1:32 PM, Bonger O [EMAIL PROTECTED] wrote:
Hi,
I have set up an environment on my Vista laptop comprising of Apache 2.2,
MySQL Server 5.1 and Php 5.2.5.
I'm using a simple php program that I found in a PHP/SQL book. The PHP
program queries a MySQL table and traverses the results array of the query
and should display on screen. However, there seems to be a problem with my
environment and as every time I run the program I get the error *Apache
HTTP server stopped working and was closed*.
Below is the code I am using, but I have managed to narrow the problem
down
to *$row = mysqli_fetch_assoc($result);*
For some reason this is causing the crash. Would there be any reason that
this function cant be found perhaps? I really have no clue!!!
?php
/* Program: petDisplay.php
* Desc:Displays all pets in selected category.
*/
?
html
headtitlePet Catalog/title/head
body
?php
$user=root;
$host=localhost;
$password=PASSWORD;
$database = am;
$cxn = mysqli_connect($host,$user,$password,$database)
or die (couldn't connect to server);
$pettype = horse; //horse was typed in a form by user
$query = SELECT * FROM Pet WHERE petType='$pettype';
$result = mysqli_query($cxn,$query)
or die (Couldn't execute query.);
/*Display results in a table */
$pettype = ucfirst($pettype).s;
echo h1$pettype/h1;
echo table cellspacing='15';
echo trtd colspan='3'hr //td/tr;
while($row = mysqli_fetch_assoc($result))
{
extract($row);
$f_price = number_format($price,2);
echo tr\n
td$petName/td\n
td$petDescription/td\n
td style='text-align: right'\$$f_price/td\n
/tr\n;
echo trtd colspan='3'hr //td/tr\n;
}
echo /table\n;
?
/body/html
Also, please see messages I have copied from the Apache error log:
[Wed Jul 09 19:30:09 2008] [notice] Parent: child process exited with
status 3221225477 -- Restarting.
[Wed Jul 09 19:30:12 2008] [notice] Apache/2.2.6 (Win32) PHP/5.2.5
configured -- resuming normal operations
[Wed Jul 09 19:30:12 2008] [notice] Server built: Sep 5 2007 08:58:56
[Wed Jul 09 19:30:12 2008] [notice] Parent: Created child process 4936
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Child process is running
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Acquired the start
mutex.
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Starting 250 worker
threads.
[Wed Jul 09 19:30:13 2008] [notice] Child 4936: Starting thread to
listen
on port 80.
Any help would be much apprecieted.
Thanks,
Bonger
Have you checked that mysqli is compiled into PHP?
--
Bastien
Cat, the other other white meat
Re: [PHP-DB] Apache crashes every time I call mysqli_fetch_assoc()
Bonger O wrote: Hi, I have set up an environment on my Vista laptop comprising of Apache 2.2, MySQL Server 5.1 and Php 5.2.5. I'm using a simple php program that I found in a PHP/SQL book. The PHP program queries a MySQL table and traverses the results array of the query and should display on screen. However, there seems to be a problem with my environment and as every time I run the program I get the error *Apache HTTP server stopped working and was closed*. Below is the code I am using, but I have managed to narrow the problem down to *$row = mysqli_fetch_assoc($result);* For some reason this is causing the crash. Would there be any reason that this function cant be found perhaps? I really have no clue!!! If the function doesn't exist php gives that error, not a random crash. Does the query work outside of php (type it in to the mysql console directly) ? Does it happen for a particular record? ie it gets up to record #5 before it crashes. What's in record #5? How many records are returned by the query? Maybe you're selecting too much data and the srever is killing it because it's taking too much memory (random guess :P). Do you have another environment you can try it in? What happens there when you: - use the same database - use a different database -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
