[PHP-DB] Prepared Statements - Search
Dear List -
I am trying to use prepared statements with the following code:
$allowed_fields = array
('Cust_Num' => 'i', 'Fname' => 's', 'Lname' =>
's', 'Street' => 's','City'=> 's', 'State' => 's', 'Zip' => 'i',
'Phone' => 'i', 'Notes' => 's'
);
if(empty($allowed_fields))
{
echo "ouch";
}
// Configure the query and the acceptable params to put
into the WHERE clause
$sql12 = 'SELECT * FROM Customers WHERE 1';
// Magically put everything together
$types = '';
$args = array();
foreach( $allowed_fields as $k => $type )
{
if( !array_key_exists( $k, $allowed_fields ) )
continue;
else
{
if( ($_POST[$k]) != '')
{
$args[] = &$_POST[$k]; // Note the addition
of the ampersand here
$types .= $type;
$sql12 .= " AND ($k = ?)";
}
}
}
$stmt = mysqli_stmt_init($cxn);
mysqli_stmt_prepare( $stmt, $sql12 );
The search fails.
This debug code:
echo "For debugging and demonstration #1";
echo 'Query: ' . $sql12 . PHP_EOL;
echo 'Bind types: ' . $types . PHP_EOL;
echo "arguments";
print_r($args);
gives the following results:
For debugging and demonstration #1
Query: SELECT * FROM Customers WHERE 1 AND (Fname = ?) AND (Lname = ?) AND
(Street = ?) AND (City = ?) AND (State = ?) AND (Zip = ?) AND (Phone = ?)
Bind types: sii
arguments
Array
(
[0] =>
[1] =>
[2] =>
[3] =>
[4] =>
[5] =>
[6] => 845745745
)
If I search the database from the command line, these are the results -
mysql> select * from Customers where Phone=845745745;
+--+-+--+++---+---+---++-+--+--+--+
| Cust_Num | Fname | Lname| Street | City | State | Zip | Phone
| Date | Notes | P1 | P2 | P3 |
+--+-+--+++---+---+---++-+--+--+--+
|10016 | okuibtg | uymkibtvgfrc | p7tvgf | Monsey | NY| 127 |
845745745 | 2012-12-01 | tvgfuyholkijuhy | NULL | NULL | NULL |
|10017 | okuibtg | uymkibtvgfrc | p7tvgf | Monsey | NY| 10952 |
845745745 | 2012-12-01 | tvgfuyholkijuhy | NULL | NULL | NULL |
|10018 | okuibtg | uymkibtvgfrc | p7tvgf | Monsey | NY| 32767 |
845745745 | 2012-12-02 | tvgfuyholkijuhy | NULL | NULL | NULL |
+--+-+--+++---+---+---++-+--+--+--+
3 rows in set (0.00 sec)
This is the output routine:
if(count($errors_array) == 0)
{
?>
Search
Results
Cust_Num
First Name
Last Name
Street
City
State
Zip
Phone
Notes
Re: [PHP-DB] Prepared Statements - Select - Bind Parameters w/ correction
$stmt = mysqli_stmt_prepare( $cxn, $sql12 );// line 507
//Warning: mysqli_stmt_prepare() expects parameter 1 to be mysqli_stmt, object
given in /var/www/x5.php on line 507
$cxn is not a mysqli_stmt. Are you perhaps passing the mysqli database
resource instead of the statement?
See this section of my example:
$stmt = mysqli_prepare( $dbh, $q );
if( !$stmt )
throw new Exception( 'Error preparing statement' );
where $dbh is the result of mysqli_connect() and $q is a string
containing your unbound query.
-Matt
On 09/28/2012 09:46 AM, Ethan Rosenberg, PhD wrote:
Matt -
Thanks.
Here is what I used, and it still generates an error:
$allowed_fields = array
( $_POST['Site'] => 's', $_POST['MedRec'] => 'i',
$_POST['Fname'] => 's', $_POST['Lname'] => 's',
$_POST['Phone'] => 's', $_POST['Height'] => 'i',
$_POST['Sex'] => 's', $_POST['Hx'] => 's',
$_POST['Bday'] => 's', $_POST['Age'] => 'i' );
if(empty($allowed_fields))
{
echo "ouch";
}
// Magically put everything together
$types = '';
$args = array();
foreach( $allowed_fields as $k => $type )
{
if( !array_key_exists( $k, $_POST ) )
continue;
$args[] = &$_POST[$k]; // Note the addition of the ampersand here
$types .= $type;
$sql12 .= " AND ($k = ?)";
}
echo "new query $sql12";
// For debugging and demonstration
echo 'Query: ' . $sql12 . PHP_EOL;
echo 'Bind types: ' . $types . PHP_EOL;
echo 'Arguments:' . PHP_EOL;
print_r($args);
$stmt = mysqli_stmt_prepare( $cxn, $sql12 );// line 507
//Warning: mysqli_stmt_prepare() expects parameter 1 to be mysqli_stmt, object
given in /var/www/x5.php on line 507
if( !$stmt )
throw new Exception( 'Error preparing statement' ); // line 509
//Fatal error: Uncaught exception 'Exception' with message 'Error preparing
statement' in /var/www/x5.php on line 509
//Exception: Error preparing statement in /var/www/x5.php on line 509
// Put the statement and types variables at the front of the
params to pass to mysqli_stmt_bind_param()
array_unshift( $args, $stmt, $types ); // Note that I've moved
this call. Apparently it doesn't pass back the result. I guess
sometimes I just forget these things.
// mysqli_stmt_bind_param()
if( !call_user_func_array( 'mysqli_stmt_bind_param', $args ) )
throw new Exception( 'Failed calling mysqli_stmt_bind_param' );
if( !mysqli_stmt_execute( $stmt ) )
throw new Exception( 'Error while executing statement' );
mysqli_stmt_bind_result( $stmt, $id, $data );
while( mysqli_stmt_fetch($stmt) )
printf( "%d %d\n", $id, $data );
mysqli_stmt_close( $stmt );
// mysqli_close( $cxm );
What did I do wrong???
Ethan
Re: [PHP-DB] Prepared Statements - Select - Bind Parameters w/ correction
Ethan,
Please accept my apologies for the minor errors in the untested code I
previously provided for your edification.
Consider the following tested code:
CREATE TABLE test (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, data
INT UNSIGNED);
INSERT INTO test (data) VALUES (123),(124),(125);
*/
// Faking a POST for testing
$_POST = array(
'id' => "1",
'data' => "123"
);
// Don't forget to put your mysql configuration here
$mysql_host = 'localhost';
$mysql_user = 'user';
$mysql_password = 'password';
$mysql_database = 'test';
$dbh = mysqli_connect( $mysql_host, $mysql_user, $mysql_password,
$database );
if( !$dbh )
die( 'Connect failed: ' . mysqli_connect_error() . PHP_EOL );
// Configure the query and the acceptable params to put into the
WHERE clause
$q = 'SELECT * FROM test WHERE 1';
$allowed_fields = array(
'data' => 'i',
'id' => 'i'
);
// Magically put everything together
$types = '';
$args = array();
foreach( $allowed_fields as $k => $type )
{
if( !array_key_exists( $k, $_POST ) )
continue;
$args[] = &$_POST[$k]; // Note the addition of the ampersand here
$types .= $type;
$q .= " AND ($k = ?)";
}
// For debugging and demonstration
echo 'Query: ' . $q . PHP_EOL;
echo 'Bind types: ' . $types . PHP_EOL;
echo 'Arguments:' . PHP_EOL;
print_r($args);
$stmt = mysqli_prepare( $dbh, $q );
if( !$stmt )
throw new Exception( 'Error preparing statement' );
// Put the statement and types variables at the front of the params
to pass to mysqli_stmt_bind_param()
array_unshift( $args, $stmt, $types ); // Note that I've moved this
call. Apparently it doesn't pass back the result. I guess sometimes I
just forget these things.
// mysqli_stmt_bind_param()
if( !call_user_func_array( 'mysqli_stmt_bind_param', $args ) )
throw new Exception( 'Failed calling mysqli_stmt_bind_param' );
if( !mysqli_stmt_execute( $stmt ) )
throw new Exception( 'Error while executing statement' );
mysqli_stmt_bind_result( $stmt, $id, $data );
while( mysqli_stmt_fetch($stmt) )
printf( "%d %d\n", $id, $data );
mysqli_stmt_close( $stmt );
mysqli_close( $dbh );
/// end code snippet
I would recommend you consider Jim Giner's remarks as well. PHP's error
message was giving you exactly what you needed to solve the problem with
the code I gave you. There is even a note about using
call_user_func_array() in the documentation about
mysqli_stmt_bind_param(). In fact, the first example in the comments on
the mysql_stmt_bind_param() page shows one way of solving the issue you
are having. (http://php.net/manual/en/mysqli-stmt.bind-param.php)
I think you will find people a lot more willing to help if you can show
that you've done basic research like looking at the documentation for
the function you are trying to use :-)
If you don't understand references, I would recommend reading about
them: http://php.net/manual/en/language.references.php
-Matt
On 09/27/2012 09:40 AM, Ethan Rosenberg, PhD wrote:
Dear list -
SEE CORRECTION IN $_POST VARIABLE BELOW.
Thanks to all for your help.
I hope [??] that this question will solve all the remaining problems.
So that we are on the same page, here is what was previously stated.
mysqli_stmt_bind_param expects three variables, in this order
mysqli_stmt_bind_param($stmt, "num", $a, $b, $c)
Where stmt is the query w/ the ?? that is
SELECT Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age
FROM Intake3 where 1 AND (Site = ?) AND (MedRec = ?) AND (Sex = ?)
and num is the number and type of variables is the query, in this
case 'sis'
$a $b and $c are the variables to be inserted, in this case:
$a = $_POST['Site'];
$b = $_POST['MedRec'];
$c = $_POST['Sex'];
As I seem to have found, the variables cannot be a string or
components of an imploded array.
This is a search function that will take patient supplied data and
search the Intake database to determine the Medical Record Number.
There are nine variables in the database, and I never know which
variables the patient will give.
Based on the database, it is easy to set up the correspondence. The
database is searched in the order of the correspondence and the
letters can be
immediately determined...
$a = $_POST['Site']
$b = $_POST['MedRec']
$c = $_POST['Fname']
$d = $_POST['Lname']
$e = $_POST['Phone']
$f = $_POST[Height']
$g = $_POST['Sex']
$h = $_POST['Hx']
$i = $_POST['Bday']
$i = $_POST['Age'] <- Corrected
The challenge is to be able to dynamically select the variables that
will go intomysqli_stmt_bind_param.
Advice and help, please
Ethan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Prepared Statements - Select - Bind Parameters w/ correction
Dear list - SEE CORRECTION IN $_POST VARIABLE BELOW. Thanks to all for your help. I hope [??] that this question will solve all the remaining problems. So that we are on the same page, here is what was previously stated. mysqli_stmt_bind_param expects three variables, in this order mysqli_stmt_bind_param($stmt, "num", $a, $b, $c) Where stmt is the query w/ the ?? that is SELECT Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age FROM Intake3 where 1 AND (Site = ?) AND (MedRec = ?) AND (Sex = ?) and num is the number and type of variables is the query, in this case 'sis' $a $b and $c are the variables to be inserted, in this case: $a = $_POST['Site']; $b = $_POST['MedRec']; $c = $_POST['Sex']; As I seem to have found, the variables cannot be a string or components of an imploded array. This is a search function that will take patient supplied data and search the Intake database to determine the Medical Record Number. There are nine variables in the database, and I never know which variables the patient will give. Based on the database, it is easy to set up the correspondence. The database is searched in the order of the correspondence and the letters can be immediately determined... $a = $_POST['Site'] $b = $_POST['MedRec'] $c = $_POST['Fname'] $d = $_POST['Lname'] $e = $_POST['Phone'] $f = $_POST[Height'] $g = $_POST['Sex'] $h = $_POST['Hx'] $i = $_POST['Bday'] $i = $_POST['Age'] <- Corrected The challenge is to be able to dynamically select the variables that will go intomysqli_stmt_bind_param. Advice and help, please Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Prepared Statements - Select - Bind Parameters
Dear list - Thanks to all for your help. I hope [??] that this question will solve all the remaining problems. So that we are on the same page, here is what was previously stated. mysqli_stmt_bind_param expects three variables, in this order mysqli_stmt_bind_param($stmt, "num", $a, $b, $c) Where stmt is the query w/ the ?? that is SELECT Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age FROM Intake3 where 1 AND (Site = ?) AND (MedRec = ?) AND (Sex = ?) and num is the number and type of variables is the query, in this case 'sis' $a $b and $c are the variables to be inserted, in this case: $a = $_POST['Site']; $b = $_POST['MedRec']; $c = $_POST['Sex']; As I seem to have found, the variables cannot be a string or components of an imploded array. This is a search function that will take patient supplied data and search the Intake database to determine the Medical Record Number. There are nine variables in the database, and I never know which variables the patient will give. Based on the database, it is easy to set up the correspondence. The database is searched in the order of the correspondence and the letters can be immediately determined... $a = $_POST['Site'] $b = $_POST['MedRec'] $c = $_POST['Fname'] $d = $_POST['Lname'] $e = $_POST['Phone'] $f = $_POST[Height'] $g = $_POST['Sex'] $h = $_POST['Hx'] $i = $_POST['Bday']'Age' $i = $_POST[] The challenge is to be able to dynamically select the variables that will go intomysqli_stmt_bind_param. Advice and help, please Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements - Select
Ethan,
I believe the root of your problem is that you are passing $bind3 as a
string to mysqli_stmt_bind_param() where the function was expecting
multiple arguments.
Let's say $binder = array( 'one', 'two', 'three' ); // I'll call this
$arguments in my example below
and $typer = array( 's', 'i', 's' );
Right now you are effectively doing this:
// doesn't work
mysqli_stmt_bind_param( $stmt, "'sis'", "one, two, three" );
Consider using the call_user_func_array() method:
// Pass elements in $arguments array to the
mysqli_stmt_bind_param() method as separate arguments.
call_user_func_array( 'mysqli_stmt_bind_param',
array_unshift($arguments, $stmt, $types) );
In this case, this call_user_func_array() call would effectively be the
same as:
mysqli_stmt_bind_param( $stmt, "sis", 'one', 'two', 'three' ); //
assuming you imploded $typer into $types
Also note that your $binder array seems to have commas as elements. It
would need to have just the actual parameters (so, count($arguments)
would be 3).
It looks like you've been making good progress with your script.
It's a little tough to tell everything that you're doing here, but take
a look at this and see if something like this helps simplify things at all:
/ Begin untested code snippet /
$sql1 = 'SELECT whatever FROM table WHERE 1 ";
$allowed_fields = array(
'Site' => 's', // fieldname is key, bind type is value
'MedRec' => 'i',
...
);
$types = '';
$args = array();
foreach( $allowed_fields as $k => $type )
{
if( !array_key_exists( $k, $_POST ) )
continue;
$args[] = $_POST[$k];
$types .= $type;
$sql1 .= " AND ($key = ?) ";
}
$stmt = mysqli_prepare( $mysql_resource, $sql1 );
if( !call_user_func_array( 'mysqli_stmt_bind_param',
array_unshift($args, $stmt, $types) ) )
throw new Exception( 'Error while binding parameters' );
/ End untested code snippet /
As indicated, I didn't test that script. It's just to illustrate an idea.
Hope this helps,
Matt
On 09/19/2012 05:59 PM, Ethan Rosenberg, PhD wrote:
Dear List -
Thanks to all for your responses.
Here is another one
I wish to accomplish the following
mysqli_stmt_bind_param($stmt, 'sis', $_POST['Site'], $_POST['MedRec'],
$_POST['Sex']);
This statemnt was hand coded. I wish to be able to generalize it.
Therefore -
$sql11 = "SELECT Site, MedRec, Fname, Lname, Phone, Height,
Sex, Hx, Bday, Age FROM Intake3 where 1 ";
$allowed_fields = array
( 'Site' =>$_POST['Site'], 'MedRec' => $_POST['MedRec'],
'Fname' => $_POST['Fname'], 'Lname' => $_POST['Lname'] ,
'Phone' => $_POST['Phone'] , 'Height' =>
$_POST['Height'], 'Sex' => $_POST['Sex'], 'Hx' => $_POST['Hx'],
'Bday' => $_POST['Bday'], 'Age' => $_POST['Age'] );
$z0='$_POST';
$z0 .="['Site']";
$z1='$_POST';
$z1 .="['MedRec']";
$z2='$_POST';
$z2 .="['Fname']";
.
.
.
$indeces = array(
"0" => array
(
'tpe'=> 's',
"val" => $z0
),
"1" => array
(
"tpe" => "i",
"val"=> $z1
),
.
.
$binder = array(); //array to hold variables
$typer = array(); //array to hold variable type
$position = -1;
foreach ( $allowed_fields as $key => $val )
{
$position = $position + 1;
if ($val != '')
{
array_push($binder, $indeces[$position]['val']);
array_push($binder, ", ");
array_push($typer, $indeces[$position]['tpe']);
$sql11 .= " AND ($key = ?) ";
}
}
array_pop($binder);
The above generates the following query:
SELECT Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age
FROM Intake3 where 1 AND (Site = ?) AND (MedRec = ?) AND (Sex = ?)
//Construct the strings for the mysqli_stmt_bind_param statement
$typ2 = implode($typer);
$typ3 = "'";
$typ3 .=$typ2;
$typ3 .= "'";
$bind3 = implode($binder);
//This statement works perfectly. mysqli_stmt_bind_param($stmt,
'sis', $_POST['Site'], $_POST['MedRec'], $_POST['Sex']);
//This one fails
mysqli_stmt_bind_param($stmt, $typ3, $bind3);
With the following error message:
Warning: mysqli_stmt_bind_param(): Number of elements in type
definition string doesn't match number of bind variables
echo "$typ3";'sis'
echo " $bind3"; $_POST['Site'], $_POST['MedRec'],
$_POST['Sex']
Help and Advice, please
Ethan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Prepared Statements - Select
Dear List -
Thanks to all for your responses.
Here is another one
I wish to accomplish the following
mysqli_stmt_bind_param($stmt, 'sis', $_POST['Site'], $_POST['MedRec'],
$_POST['Sex']);
This statemnt was hand coded. I wish to be able to generalize it.
Therefore -
$sql11 = "SELECT Site, MedRec, Fname, Lname, Phone, Height,
Sex, Hx, Bday, Age FROM Intake3 where 1 ";
$allowed_fields = array
( 'Site' =>$_POST['Site'], 'MedRec' => $_POST['MedRec'],
'Fname' => $_POST['Fname'], 'Lname' => $_POST['Lname'] ,
'Phone' => $_POST['Phone'] , 'Height' =>
$_POST['Height'], 'Sex' => $_POST['Sex'], 'Hx' => $_POST['Hx'],
'Bday' => $_POST['Bday'], 'Age' => $_POST['Age'] );
$z0='$_POST';
$z0 .="['Site']";
$z1='$_POST';
$z1 .="['MedRec']";
$z2='$_POST';
$z2 .="['Fname']";
.
.
.
$indeces = array(
"0" => array
(
'tpe'=> 's',
"val" => $z0
),
"1" => array
(
"tpe" => "i",
"val"=> $z1
),
.
.
$binder = array(); //array to hold variables
$typer = array(); //array to hold variable type
$position = -1;
foreach ( $allowed_fields as $key => $val )
{
$position = $position + 1;
if ($val != '')
{
array_push($binder, $indeces[$position]['val']);
array_push($binder, ", ");
array_push($typer, $indeces[$position]['tpe']);
$sql11 .= " AND ($key = ?) ";
}
}
array_pop($binder);
The above generates the following query:
SELECT Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age FROM
Intake3 where 1 AND (Site = ?) AND (MedRec = ?) AND (Sex = ?)
//Construct the strings for the mysqli_stmt_bind_param statement
$typ2 = implode($typer);
$typ3 = "'";
$typ3 .=$typ2;
$typ3 .= "'";
$bind3 = implode($binder);
//This statement works perfectly. mysqli_stmt_bind_param($stmt, 'sis',
$_POST['Site'], $_POST['MedRec'], $_POST['Sex']);
//This one fails
mysqli_stmt_bind_param($stmt, $typ3, $bind3);
With the following error message:
Warning: mysqli_stmt_bind_param(): Number of elements in type definition string
doesn't match number of bind variables
echo "$typ3";'sis'
echo " $bind3"; $_POST['Site'], $_POST['MedRec'], $_POST['Sex']
Help and Advice, please
Ethan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements - Search
Ethan,
9 times out of 10 your answer is in the error statement.
Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't
match number of fields in prepared statement.
GL,
Best,
Karl
On Sep 13, 2012, at 7:09 PM, Ethan Rosenberg, PhD wrote:
Dear List -
Here is another problem I am having with prepared statements. The
last one was INSERT, this one is SELECT.
Here is the database:
mysql> describe Intake3;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| Site | varchar(6) | NO | PRI | | |
| MedRec | int(6) | NO | PRI | NULL| |
| Fname | varchar(15) | YES | | NULL| |
| Lname | varchar(30) | YES | | NULL| |
| Phone | varchar(30) | YES | | NULL| |
| Height | int(4) | YES | | NULL| |
| Sex| char(7) | YES | | NULL| |
| Hx | text| YES | | NULL| |
| Bday | date| YES | | NULL| |
| Age| int(3) | YES | | NULL| |
++-+--+-+-+---+
10 rows in set (0.00 sec)
Here is my code:
// Prepare statement
$stmt = mysqli_stmt_init($cxn);
$sql11 = "SELECT 'Fname', 'Lname', 'Phone', Height, Hx, Bday,
Age FROM Intake3 where 1 and (MedRec = ?) and (Site = ?) and (Sex
= ?)";
// Allocates and initializes a statement object suitable for
mysqli_stmt_prepare().
// Prepare statement, bind result variables, execute and place
results into bound result variables
mysqli_stmt_prepare($stmt, $sql11);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $Site, $MedRec, $Fname, $Lname,
$Phone, $Height, $Sex, $Hx, $Bday, $Age); //The error is in this
statement.
while (mysqli_stmt_fetch($stmt)) {
printf("%s %s %s %s %s %s %s %s %s %s \n", $Site, $MedRec,
$Fname, $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age);
}
I get no output from the printf statement.
I receive the following error:
Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't
match number of fields in prepared statement.
The query, with the values inserted, works on the command line
Help and advice, please.
Ethan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Karl DeSaulniers
Design Drumm
http://designdrumm.com
[PHP-DB] Prepared Statements - Search -- SOLVED!!!
Dear List -
-->> THANKS TO ALL. See below <--
Here is another problem I am having with prepared statements. The last
one was INSERT, this one is SELECT.
Here is the database:
mysql> describe Intake3;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| Site | varchar(6) | NO | PRI | | |
| MedRec | int(6) | NO | PRI | NULL| |
| Fname | varchar(15) | YES | | NULL| |
| Lname | varchar(30) | YES | | NULL| |
| Phone | varchar(30) | YES | | NULL| |
| Height | int(4) | YES | | NULL| |
| Sex| char(7) | YES | | NULL| |
| Hx | text| YES | | NULL| |
| Bday | date| YES | | NULL| |
| Age| int(3) | YES | | NULL| |
++-+--+-+-+---+
10 rows in set (0.00 sec)
Here is my code:
// Prepare statement
$stmt = mysqli_stmt_init($cxn);
$sql11 = "SELECT 'Fname', 'Lname', 'Phone', Height, Hx, Bday, Age
FROM Intake3 where 1 and (MedRec = ?) and (Site = ?) and (Sex = ?)";
// Allocates and initializes a statement object suitable for
mysqli_stmt_prepare().
// Prepare statement, bind result variables, execute and place results
into bound result variables
mysqli_stmt_prepare($stmt, $sql11);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $Site, $MedRec, $Fname, $Lname,
$Phone, $Height, $Sex, $Hx, $Bday, $Age); //The error is in this statement.
while (mysqli_stmt_fetch($stmt)) {
printf("%s %s %s %s %s %s %s %s %s %s \n", $Site, $MedRec,
$Fname, $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age);
}
I get no output from the printf statement.
I receive the following error:
Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't
match number of fields in prepared statement.
The query, with the values inserted, works on the command line
Help and advice, please.
Ethan
***
Here is my revised code:
// Prepare statement
$stmt = mysqli_stmt_init($cxn);
$sql11 = "SELECT MedRec, Site, Sex,Fname, Lname, Phone, Height,
Hx, Bday, Age FROM Intake3 where 1 and (MedRec = ?) and (Site = ?) and
(Sex = ?)";
/* Allocates and initializes a statement object suitable for
mysqli_stmt_prepare(). */
/* Prepare statement, bind result variables, execute and place results
into bound result variables */
mysqli_stmt_prepare($stmt, $sql11);
mysqli_stmt_bind_param($stmt, 'iss', $_POST['MedRec'],
$_POST['Site'], $_POST['Sex']);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $MedRec, $Site, $Sex, $Fname,
$Lname, $Phone, $Height, $Hx, $Bday, $Age);
while (mysqli_stmt_fetch($stmt)) {
printf("%s %s %s %s %s %s %s %s %s %s \n", $Site, $MedRec,
$Fname, $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age);
}
Added the bind_parameters statement and it worked. Stupid me - you
can’t perform a query unless the parameters have been inserted into the
query.
Live and learn.
Ethan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements - Search
Does the list of the SELECT fields not have to match the variables you are
binding? E.g. if you do not include MedRec in your SELECT then you have no
MedRec data to bind from your $sql11 variable to the $MedRec varable and
then nothing to print there... or what? Am I just fabulating? :).
Cheers,
Fjalar
On Friday, September 14, 2012, Ethan Rosenberg, PhD
wrote:
Dear List -
>
> Here is another problem I am having with prepared statements. The last
> one was INSERT, this one is SELECT.
>
> Here is the database:
>
> mysql> describe Intake3;
> ++-+--**+-+-+---+
> | Field | Type| Null | Key | Default | Extra |
> ++-+--**+-+-+---+
> | Site | varchar(6) | NO | PRI | | |
> | MedRec | int(6) | NO | PRI | NULL| |
> | Fname | varchar(15) | YES | | NULL| |
> | Lname | varchar(30) | YES | | NULL| |
> | Phone | varchar(30) | YES | | NULL| |
> | Height | int(4) | YES | | NULL| |
> | Sex| char(7) | YES | | NULL| |
> | Hx | text| YES | | NULL| |
> | Bday | date| YES | | NULL| |
> | Age| int(3) | YES | | NULL| |
> ++-+--**+-+-+---+
> 10 rows in set (0.00 sec)
>
> Here is my code:
>
> // Prepare statement
> $stmt = mysqli_stmt_init($cxn);
> $sql11 = "SELECT 'Fname', 'Lname', 'Phone', Height, Hx, Bday, Age
> FROM Intake3 where 1 and (MedRec = ?) and (Site = ?) and (Sex = ?)";
> // Allocates and initializes a statement object suitable for
> mysqli_stmt_prepare().
> // Prepare statement, bind result variables, execute and place results
> into bound result variables
>mysqli_stmt_prepare($stmt, $sql11);
>mysqli_stmt_execute($stmt);
>mysqli_stmt_bind_result($stmt, $Site, $MedRec, $Fname, $Lname, $Phone,
> $Height, $Sex, $Hx, $Bday, $Age); //The error is in this statement.
> while (mysqli_stmt_fetch($stmt)) {
> printf("%s %s %s %s %s %s %s %s %s %s \n", $Site, $MedRec, $Fname,
> $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age);
> }
>
> I get no output from the printf statement.
>
> I receive the following error:
>
> Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match
> number of fields in prepared statement.
>
> The query, with the values inserted, works on the command line
>
> Help and advice, please.
>
> Ethan
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
On Friday, September 14, 2012, Ethan Rosenberg, PhD wrote:
> Dear List -
>
> Here is another problem I am having with prepared statements. The last
> one was INSERT, this one is SELECT.
>
> Here is the database:
>
> mysql> describe Intake3;
> ++-+--**+-+-+---+
> | Field | Type| Null | Key | Default | Extra |
> ++-+--**+-+-+---+
> | Site | varchar(6) | NO | PRI | | |
> | MedRec | int(6) | NO | PRI | NULL| |
> | Fname | varchar(15) | YES | | NULL| |
> | Lname | varchar(30) | YES | | NULL| |
> | Phone | varchar(30) | YES | | NULL| |
> | Height | int(4) | YES | | NULL| |
> | Sex| char(7) | YES | | NULL| |
> | Hx | text| YES | | NULL| |
> | Bday | date| YES | | NULL| |
> | Age| int(3) | YES | | NULL| |
> ++-+--**+-+-+---+
> 10 rows in set (0.00 sec)
>
> Here is my code:
>
> // Prepare statement
> $stmt = mysqli_stmt_init($cxn);
> $sql11 = "SELECT 'Fname', 'Lname', 'Phone', Height, Hx, Bday, Age
> FROM Intake3 where 1 and (MedRec = ?) and (Site = ?) and (Sex = ?)";
> // Allocates and initializes a statement object suitable for
> mysqli_stmt_prepare().
> // Prepare statement, bind result variables, execute and place results
> into bound result variables
>mysqli_stmt_prepare($stmt, $sql11);
>mysqli_stmt_execute($stmt);
>mysqli_stmt_bind_result($stmt, $Site, $MedRec, $Fname, $Lname, $Phone,
> $Height, $Sex, $Hx, $Bday, $Age); //The error is in this statement.
> while (mysqli_stmt_fetch($stmt)) {
> printf("%s %s %s %s %s %s %s %s %s %s \n", $Site, $MedRec, $Fname,
> $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age);
> }
>
> I get no output from the printf statement.
>
> I receive the following error:
>
> Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match
> number of fields in prepared statement.
>
> The query, with the values inserted, works on the command line
>
> Help and advice, please.
>
> Ethan
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
[PHP-DB] Prepared Statements - Search
Dear List -
Here is another problem I am having with prepared statements. The last
one was INSERT, this one is SELECT.
Here is the database:
mysql> describe Intake3;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| Site | varchar(6) | NO | PRI | | |
| MedRec | int(6) | NO | PRI | NULL| |
| Fname | varchar(15) | YES | | NULL| |
| Lname | varchar(30) | YES | | NULL| |
| Phone | varchar(30) | YES | | NULL| |
| Height | int(4) | YES | | NULL| |
| Sex| char(7) | YES | | NULL| |
| Hx | text| YES | | NULL| |
| Bday | date| YES | | NULL| |
| Age| int(3) | YES | | NULL| |
++-+--+-+-+---+
10 rows in set (0.00 sec)
Here is my code:
// Prepare statement
$stmt = mysqli_stmt_init($cxn);
$sql11 = "SELECT 'Fname', 'Lname', 'Phone', Height, Hx, Bday, Age
FROM Intake3 where 1 and (MedRec = ?) and (Site = ?) and (Sex = ?)";
// Allocates and initializes a statement object suitable for
mysqli_stmt_prepare().
// Prepare statement, bind result variables, execute and place results
into bound result variables
mysqli_stmt_prepare($stmt, $sql11);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $Site, $MedRec, $Fname, $Lname,
$Phone, $Height, $Sex, $Hx, $Bday, $Age); //The error is in this statement.
while (mysqli_stmt_fetch($stmt)) {
printf("%s %s %s %s %s %s %s %s %s %s \n", $Site, $MedRec,
$Fname, $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age);
}
I get no output from the printf statement.
I receive the following error:
Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match
number of fields in prepared statement.
The query, with the values inserted, works on the command line
Help and advice, please.
Ethan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements Insert Problem - Any more ideas?
On Sun, Sep 2, 2012 at 10:41 PM, Ethan Rosenberg, PhD wrote: > On Sun, Sep 2, 2012 at 6:45 AM, Ethan Rosenberg, PhD > wrote: >> >> Dear List - >> >> I wish to accomplish the following with prepared statements: >> >> $stmt = mysqli_stmt_init($cxn); >> if($stmt = mysqli_stmt_prepare($stmt, "INSERT INTO Intake3 (Site, >> MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age) >> VALUES(?,?,?,?,?,?,?,?,?,?")!=0) > > > >> Help and advice, please. >> >> Ethan Rosenberg >> > *+ > > Any more idead? You're still missing the closing parenthesis here, and I told you that mysqli_error should give you some more info. Did you try that? Your reply with 'no error' doesn't really make sense on it's own.. Also, IIRC, I think you need to use one extra set of parenthesis, like this: if(($stmt = mysqli_stmt_prepare()) != 0) - Matijn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements Insert Problem - Any more ideas?
On Sun, Sep 2, 2012 at 6:45 AM, Ethan Rosenberg, PhD wrote: Dear List - I wish to accomplish the following with prepared statements: $stmt = mysqli_stmt_init($cxn); if($stmt = mysqli_stmt_prepare($stmt, "INSERT INTO Intake3 (Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age) VALUES(?,?,?,?,?,?,?,?,?,?")!=0) Help and advice, please. Ethan Rosenberg *+ Any more idead? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements Insert Problem
Ethan Rosenberg, PhD /Pres/CEO/ *Hygeia Biomedical Research, Inc* 2 Cameo Ridge Road Monsey, NY 10952 T: 845 352-3908 F: 845 352-7566 erosenb...@hygeiabiomedical.com On 09/02/2012 08:33 AM, Matijn Woudt wrote: On Sun, Sep 2, 2012 at 6:45 AM, Ethan Rosenberg, PhD wrote: Dear List - I wish to accomplish the following with prepared statements: $stmt = mysqli_stmt_init($cxn); if($stmt = mysqli_stmt_prepare($stmt, "INSERT INTO Intake3 (Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age) VALUES(?,?,?,?,?,?,?,?,?,?")!=0) It seems you're missing a ')' here. Help and advice, please. Ethan Rosenberg === If that doesn't fix it, try printing mysqli_error($cxn) instead of "Ouch".. - Matijn +++ No error. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements Insert Problem
On Sun, Sep 2, 2012 at 6:45 AM, Ethan Rosenberg, PhD wrote: > Dear List - > > I wish to accomplish the following with prepared statements: > > $stmt = mysqli_stmt_init($cxn); > if($stmt = mysqli_stmt_prepare($stmt, "INSERT INTO Intake3 (Site, > MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age) > VALUES(?,?,?,?,?,?,?,?,?,?")!=0) It seems you're missing a ')' here. > Help and advice, please. > > Ethan Rosenberg > If that doesn't fix it, try printing mysqli_error($cxn) instead of "Ouch".. - Matijn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Prepared Statements Insert Problem
Dear List -
I wish to accomplish the following with prepared statements:
FYI -
The Database:
mysql> describe Intake3;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| Site | varchar(6) | NO | PRI | | |
| MedRec | int(6) | NO | PRI | NULL| |
| Fname | varchar(15) | YES | | NULL| |
| Lname | varchar(30) | YES | | NULL| |
| Phone | varchar(30) | YES | | NULL| |
| Height | int(4) | YES | | NULL| |
| Sex| char(7) | YES | | NULL| |
| Hx | text| YES | | NULL| |
| Bday | date| YES | | NULL| |
| Age| int(3) | YES | | NULL| |
++-+--+-+-+---+
The connection to the database was successful
The variables:
$fptr1 = fopen("/home/ethan/PHP/HRecnumSite", "r+");
fscanf($fptr1,"%d %s",$MedRec, $Site);
$_POST['MedRec'] = $MedRec;
$_POST['Site'] = $Site;
$fname = $_POST['Fname'];
$lname = $_POST['Lname'];
$phone = $_POST['Phone'];
$hgt = $_POST['Height'];
$sx= $_POST['Sex'];
$hx= $_POST['Hx'];
$bday = $_POST['Bday'];
$age = $_POST['Age'];
if($sx==1)$_POST['Sex'] = 'F';
if($sx==0)$_POST['Sex'] = 'M';
Statement to convert:
$sql1 = "INSERT INTO Intake3(Site, MedRec, Fname, Lname, Phone, Height,
Sex, Hx, Bday, Age)
VALUES('$Site', $MedRec, '$fname', '$lname', '$phone', $hgt, '$sx',
'$hx', '$bday', '$age')";
My attempt [which failed]
$stmt = mysqli_stmt_init($cxn);
if($stmt = mysqli_stmt_prepare($stmt, "INSERT INTO Intake3 (Site,
MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age)
VALUES(?,?,?,?,?,?,?,?,?,?")!=0)
{
print_r($stmt);
mysqli_stmt_bind_param($stmt, 'sisssisssi', $Site, $MedRec, $fname,
$lname, $phone, $hgt, $sx, $hx, $bday, $age);
mysqli_execute($stmt);
mysqli_stmt_bind_result($stmt, $Site, $MedRec, $fname, $lname,
$phone, $hgt, $sx, $hx, $bday, $age);
echo $stmt;
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);
}
else
echo "Ouch";
Regrettably, all I see on the monitor is "ouch"!!
Help and advice, please.
Ethan Rosenberg
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Prepared Statements
Hi all,
I am struggerling with a prepared statement (may not even be suited)
$stmt = $db->stmt_init();
if($stmt->prepare("SELECT title,live,where FROM content"))
{
$stmt->bind_param('sii',$t,$l,$w);
## This bit here
$stmt->execute();
$stmt->bind_result($title, $live, $where);
while($stmt->fetch())
{
echo "$title\t -\t $live\t -\t $where";
}
$stmt->close();
}
How can i get the data i want to select into variables in this scenario
(title and Live and where)?
BR,
Gavin
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements
On Fri, Nov 11, 2011 at 10:41 AM, Ron Piggott
wrote:
>
> $dsh = 'mysql:host=localhost;dbname='.$database;
> $dbh = new PDO($dsh, $username, $password);
>
> #query for the authorization code
>
> $query = "SELECT `authorization_code` FROM
> `directory_listing_update_authorizations` WHERE NOW() BETWEEN `start_date`
> AND `end_date` AND `authorization_code` = :authorization_code AND
> `directory_entries_reference` = :directory_entries_reference LIMIT 1;";
>
> $stmt = $dbh->prepare($query);
>
> $stmt->bindValue(':directory_entries_reference',
> $directory_entries_reference, PDO::PARAM_STR);
> $stmt->bindValue(':authorization_code', $authorization_code, PDO::PARAM_STR);
>
> $stmt->execute() or die(print_r($stmt->errorInfo(), true));
>
> while ($row = $stmt->fetch()) {
Not entirely clear here why you need a while statement if your query
above is limit 1.
>
> if ( $row['authorization_code'] == $authorization_code ) {
This is redundant with the query statement above; given that, this
will always pass.
> #update directory_entries.last_review with today's date
>
> $query = "UPDATE `directory_entries` SET `last_review` = NOW() WHERE
> `reference` = :directory_entries_reference LIMIT 1;";
>
> $stmt = $dbh->prepare($query);
This concerns me -- the outer loop is using the previously prepared
and exectued statement in the $stmt variable, then you're resetting it
here. This probably works because your initial query was limit 1, but
it might not in another context. Regardless, it makes the outer while
loop test invalid.
>
> $stmt->bindValue(':directory_entries_reference',
> $directory_entries_reference, PDO::PARAM_STR);
>
> $stmt->execute() or die(print_r($stmt->errorInfo(), true));
>
> } else {
>
> #failure, direct user to request new authorization code or login
> manually
>
> }
>
> }
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements
On Fri, Nov 11, 2011 at 5:41 PM, Ron Piggott
wrote:
>
> I have two questions about Prepared Statements from the code below:
>
> #1:
> The purpose of the first $query is to determine if the authorization code
> supplied is both accurate and also live
> - When the authorization code is generated it is given a time frame when it
> must be used in. The columns `start_date` and `end_date` are both DATETIME.
> This is why I am using NOW() to check the “shelf life”.
>
> My question: Is there a better way to confirm the record was found than using:
>
> if ( $row['authorization_code'] == $authorization_code ) {
This check doesn't make sense, since you're SQL query already checks
this, and won't return rows where $row['authorization_code'] !=
$authorization_code
>
> In another way of executing a mySQL database query using PHP I can count the
> # rows the result brought with the command “mysql_numrows”. I don’t know how
> to do this in Prepared Statements. I wonder if comparing the # of rows found
> is a better method? or what other programmers are using?
$stmt->rowCount(); will return the number of rows. I would recommend
this way of checking.
>
> #2:
> How can I tell if the UPDATE $query executed successfully? I am wanting to
> do something like:
>
> echo “Update Successful – No changes were required during this review”;
>
> if the UPDATE is successful --- otherwise I need to direct the user to try
> again with a different message:
>
> echo “Update was unsuccessful – Follow this link to try again”;
$stmt->execute() will return either true or false, depending on if the
query was successfully executed. Currently, you're code will print an
error with some error info and terminate the script. You're probably
looking for something like this:
if($stmt->execute()) {
echo “Update Successful – No changes were required during this review”;
} else {
echo “Update was unsuccessful – Follow this link to try again”;
}
>
>
>
> I am still getting use to Prepared Statements, this is why I am asking these
> questions --- Thank you for helping me. Ron
Hope this helps you out. Feel free to ask for more help.
Matijn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Prepared Statements
I have two questions about Prepared Statements from the code below:
#1:
The purpose of the first $query is to determine if the authorization code
supplied is both accurate and also live
- When the authorization code is generated it is given a time frame when it
must be used in. The columns `start_date` and `end_date` are both DATETIME.
This is why I am using NOW() to check the “shelf life”.
My question: Is there a better way to confirm the record was found than using:
if ( $row['authorization_code'] == $authorization_code ) {
In another way of executing a mySQL database query using PHP I can count the #
rows the result brought with the command “mysql_numrows”. I don’t know how to
do this in Prepared Statements. I wonder if comparing the # of rows found is a
better method? or what other programmers are using?
#2:
How can I tell if the UPDATE $query executed successfully? I am wanting to do
something like:
echo “Update Successful – No changes were required during this review”;
if the UPDATE is successful --- otherwise I need to direct the user to try
again with a different message:
echo “Update was unsuccessful – Follow this link to try again”;
I am still getting use to Prepared Statements, this is why I am asking these
questions --- Thank you for helping me. Ron
===
prepare($query);
$stmt->bindValue(':directory_entries_reference', $directory_entries_reference,
PDO::PARAM_STR);
$stmt->bindValue(':authorization_code', $authorization_code, PDO::PARAM_STR);
$stmt->execute() or die(print_r($stmt->errorInfo(), true));
while ($row = $stmt->fetch()) {
if ( $row['authorization_code'] == $authorization_code ) {
#update directory_entries.last_review with today's date
$query = "UPDATE `directory_entries` SET `last_review` = NOW() WHERE
`reference` = :directory_entries_reference LIMIT 1;";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':directory_entries_reference',
$directory_entries_reference, PDO::PARAM_STR);
$stmt->execute() or die(print_r($stmt->errorInfo(), true));
} else {
#failure, direct user to request new authorization code or login
manually
}
}
www.TheVerseOfTheDay.info
Re: [PHP-DB] Prepared Statements With Multiple Databases
On 2011-09-30, at 8:26 PM, "Ron Piggott" wrote: > > I need my Prepared Statement database connection to be able to connect with > two different databases (which use the same username / password). > > They are assigned variables > > $database1 and $database2 > > What I have been using so far is: > > > $dsh = 'mysql:host=localhost;dbname='. $database1; > $dbh = new PDO($dsh, $username, $password); > > > Is there a way to amend this with a second database connection? > > Ron > > > > www.TheVerseOfTheDay.info A simpler option maybe to use the dbname.tablename syntax to donthe query Select db1.somefield, db1.anotherfield... Select db2.somefield, db2.someotherfield Bastien Koert 905-904-0334 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Prepared Statements With Multiple Databases
I need my Prepared Statement database connection to be able to connect with two different databases (which use the same username / password). They are assigned variables $database1 and $database2 What I have been using so far is: $dsh = 'mysql:host=localhost;dbname='. $database1; $dbh = new PDO($dsh, $username, $password); Is there a way to amend this with a second database connection? Ron www.TheVerseOfTheDay.info
Re: [PHP-DB] Prepared Statements Rows Selected
$stmt->rows() should give you the number of rows returned.
Giff
On Mon, 2011-05-23 at 18:53 -0400, Ron Piggott wrote:
> What command will tell me the # of rows the SELECT query retrieved using
> Prepared Statements.
>
>
> $dsh = 'mysql:host=localhost;dbname='.$database;
> $dbh = new PDO($dsh, $username, $password);
>
> $stmt = $dbh->prepare($query);
>
> $stmt->bindParam(':email', $email);
> $stmt->bindParam(':pass', $pass);
>
> $stmt->execute();
>
>
> I am looking for the equivalent of mysql_numrows
>
> mysql_connect('localhost',$username,$password);
> @mysql_select_db($database) or die( "Unable to select database");
> $result=mysql_query($query);
> $num=mysql_numrows($result);
>
>
> Ron
>
> The Verse of the Day
> “Encouragement from God’s Word”
> http://www.TheVerseOfTheDay.info
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Prepared Statements Rows Selected
What command will tell me the # of rows the SELECT query retrieved using
Prepared Statements.
$dsh = 'mysql:host=localhost;dbname='.$database;
$dbh = new PDO($dsh, $username, $password);
$stmt = $dbh->prepare($query);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':pass', $pass);
$stmt->execute();
I am looking for the equivalent of mysql_numrows
mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$result=mysql_query($query);
$num=mysql_numrows($result);
Ron
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
Re: [PHP-DB] Prepared Statements # of rows
Hey friend!
You can use the PDOStatement::RowCount, but there is a problem
(extracted from the PHP Manual,
http://php.net/manual/en/pdostatement.rowcount.php):
PDOStatement->rowCount - Returns the number of rows affected by the
last SQL statement. If the last SQL statement executed by the
associated PDOStatement was a SELECT statement, some databases may
return the number of rows returned by that statement. However, this
behaviour is not guaranteed for all databases and should not be relied
on for portable applications.
You should test if the expected behavior happens with this method, or
use the method below (less elegant):
$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
if ($res = $conn->query($sql)) {
/* Check the number of rows that match the SELECT statement */
if ($res->fetchColumn() > 0) {
/* Issue the real SELECT statement and work with the results
*/
$sql = "SELECT name FROM fruit WHERE calories > 100";
foreach ($conn->query($sql) as $row) {
print "Name: " . $row['NAME'] . "\n";
}
}
/* No rows matched -- do something else */
else {
print "No rows matched the query.";
}
}
Best regards,
Adriano Laranjeira.
> > > > > > > > >
On Fri, 4 Mar 2011 07:30:51 -0500
"Ron Piggott" wrote:
When I used Prepared Statements how do I check for the # of rows
found (Equal to mysql_numrows )?
IE Following the command:
$stmt->execute() or die(print_r($stmt->errorInfo(), true));
Ron
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Prepared Statements # of rows
When I used Prepared Statements how do I check for the # of rows found (Equal to mysql_numrows )? IE Following the command: $stmt->execute() or die(print_r($stmt->errorInfo(), true)); Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] Prepared Statements
I am just starting to use Prepared Statements and am in a learning curve.
I am working on the code below. It is for a directory. This is to select the
listing for updating.
How do I tell if there are more than 1 search result?
Am I correctly retrieving the results, if there is more than 1?
I want to make a WHILE loop to display the search results for the listing the
user is editing. (You can see where I have started the ... ... I
want that area in the loop)
Ron
$dbh = new PDO($dsh, $username, $password);
$stmt = $dbh->prepare("SELECT `reference`, `organization`, `city`,
`province_state`, `postal_zip_code`, `country` FROM `ministry_profiles` WHERE (
`reference` = :organization_reference ) OR ( `organization` LIKE
%:organization_name% ) OR ( `telephone` LIKE %:organization_phone% ) OR (
`toll_free` LIKE %:organization_toll_free_phone% ) ORDER BY `organization`
ASC");
$stmt->bindParam(':organization_reference', $organization_reference,
PDO::PARAM_STR);
$stmt->bindParam(':organization_name', $organization_name, PDO::PARAM_STR);
$stmt->bindParam(':organization_phone', $organization_phone, PDO::PARAM_STR);
$stmt->bindParam(':organization_toll_free_phone', $organization_phone,
PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
echo "\r\n";
$search_result_organization_reference = $result['reference'];
$search_result_organization = $result['organization'];
$search_result_city = $result['city'];
$search_result_province_state = $result['province_state'];
$search_result_postal_zip_code = $result['postal_zip_code'];
$search_result_country = $result['country'];
echo "" . $search_result_organization . " (Ref: " .
$search_result_organization_reference . ")\r\n";
echo $search_result_city . ", " . $search_result_province_state . " " .
$search_result_country . " " . $search_result_postal_zip_code . "\r\n";
echo "\r\n";
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
[PHP-DB] Prepared Statements and mySQL
I am trying to implement Prepared Statements on my web site and this is the
first time I have ever used this.
#1) I received the error “class mysql not defined”. That is a reference to the
first line of code (below) where the database connection is established.
#2) How do I assign the auto_increment value to a variable use Prepared
Statements? In the syntax I am attempting below “record” is an auto_increment
column.
#3) Do syntaxes such as “$stmt->bindParam(':account_suspended', -00-00);”
require the date -00-00 to be surrounded by ‘ ?
Thank you to the many of you who have supported me this year when I have had
questions. I am physically handicapped, although I don’t want to make a big
deal about it. I have appreciated the opportunity to continue developing my
PHP / mySQL programming skills in 2010. The Internet is a life line to me.
Ron
$dbh = new mysql('localhost', '$username', '$password', '$database2');
$stmt = $dbh->prepare("INSERT INTO `$database2`.`member` ( `record` ,
`first_name` , `last_name` , `address_1` , `address_2` , `address_3` ,
`address_4` , `address_5` , `email` , `prayer_community_alias` , `birth_month`
, `birth_day` , `pass` , `validated` , `last_login` , `last_activity` ,
`birthday_records` , `greeting_reference` , `registration_ip_address`,
`account_created` , `account_suspended` , `account_closed` , `referral_source`
, `friends_of_ministry_package` , `security_question_1` , `security_answer_1` ,
`security_question_2` , `security_answer_2` , `security_question_3` ,
`security_answer_3` ) VALUES ( NULL , ':f1', ':l1', '', '', '', '', '', ':e1',
'', ':birth_month', ':birth_day', ':validate_password', ':validated',
':last_login', ':last_activity', ':birthday_records', ':greeting_reference',
':registration_ip_address', ':account_created', ':account_suspended',
':account_closed', ':referral_source', ':friends_of_ministry_package',
':security_question_1', '', ':security_question_2', '', ':security_question_3',
'' ) ON DUPLICATE KEY UPDATE `validated` = ':validated', `pass` =
':validate_password', `account_suspended` = ':account_suspended',
`account_closed` = ':account_closed', `last_activity` = ':last_activity',
`registration_ip_address` = ':registration_ip_address';");
$stmt->bindParam(':f1', $f1);
$stmt->bindParam(':l1', $l1);
$stmt->bindParam(':e1', $e1);
$stmt->bindParam(':birth_month', 0);
$stmt->bindParam(':birth_day', 0);
$stmt->bindParam(':validate_password', $validate_password);
$stmt->bindParam(':validated', 5);
$stmt->bindParam(':last_login', $todays_date);
$stmt->bindParam(':last_activity', $todays_date);
$stmt->bindParam(':birthday_records', 15);
$stmt->bindParam(':security_question_1', 0);
$stmt->bindParam(':greeting_reference', 0);
$stmt->bindParam(':registration_ip_address', $registration_ip_address);
$stmt->bindParam(':account_created', $todays_date);
$stmt->bindParam(':account_suspended', -00-00);
$stmt->bindParam(':account_closed', -00-00);
$stmt->bindParam(':referral_source', 2);
$stmt->bindParam(':friends_of_ministry_package', 0);
$stmt->bindParam(':security_question_1', $security_question_1);
$stmt->bindParam(':security_question_2', $security_question_2);
$stmt->bindParam(':security_question_3', $security_question_3);
// insert one row
$stmt->execute();
$stmt->close();
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
[PHP-DB] Prepared statements via mssql extension
Is it possible? Just checking... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] prepared statements under load with pconnect
When I use pconnect to connect to a db2 back end, I get the expected faster connection times. However, when the site is under any sort of load (6 or more concurrent accesses), we have noted that the time spent preparing statements increases greatly (execution time remains fairly consistent however). If anybody knows anything about this, your help would be greatly appreciated. John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
