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