[PHP-DB] Prepared Statements - Search

2012-12-03 Thread Ethan Rosenberg, PhD

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 #1br /;
echo 'Query: ' . $sql12 . PHP_EOL;
echo 'Bind types: ' . $types . PHP_EOL;
echo argumentsbr /;
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)
{
?   

centerbSearch 
Results/b/centerbr /
center
table border=4 cellpadding=5 cellspacing=55  
rules=all frame=box style=table-layout: fixed;
tr class=heading
thCust_Num/th
thFirst Name/th
thLast Name/th
thStreet/th
thCity/th
thState/th
thZip/th
thPhone/th
thNotes/th




?php$i = 0;
do
{
{

$vara2 = 
array(array($Cust_Num, $Fname, $Lname, $Street, $City, $State, $Zip, $Phone, 
$Notes));
$vara2[$i][0]   = 
$Cust_Num;

Re: [PHP-DB] Prepared Statements - Select - Bind Parameters w/ correction

2012-09-28 Thread Matt Pelmear

$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




[PHP-DB] Prepared Statements - Select - Bind Parameters

2012-09-27 Thread Ethan Rosenberg, PhD

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



[PHP-DB] Prepared Statements - Select - Bind Parameters w/ correction

2012-09-27 Thread Ethan Rosenberg, PhD

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 - Bind Parameters w/ correction

2012-09-27 Thread Matt Pelmear

Ethan,

Please accept my apologies for the minor errors in the untested code I 
previously provided for your edification.

Consider the following tested code:

?php
/*
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

2012-09-21 Thread Matt Pelmear

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 br /$typ3;'sis'

echo br /  $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

2012-09-19 Thread Ethan Rosenberg, PhD

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 br /$typ3;'sis'

echo br /  $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 - Search

2012-09-13 Thread Ethan Rosenberg, PhD

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

2012-09-13 Thread Fjalar Sigurðarson
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 -- SOLVED!!!

2012-09-13 Thread Ethan Rosenberg, PhD

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

2012-09-13 Thread Karl DeSaulniers

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

2012-09-02 Thread Matijn Woudt
On Sun, Sep 2, 2012 at 6:45 AM, Ethan Rosenberg, PhD
erosenb...@hygeiabiomedical.com 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 Insert Problem

2012-09-02 Thread Ethan Rosenberg, PhD


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
erosenb...@hygeiabiomedical.com  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 - Any more ideas?

2012-09-02 Thread Ethan Rosenberg, PhD

On Sun, Sep 2, 2012 at 6:45 AM, Ethan Rosenberg, PhD
erosenb...@hygeiabiomedical.com 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 - Any more ideas?

2012-09-02 Thread Matijn Woudt
On Sun, Sep 2, 2012 at 10:41 PM, Ethan Rosenberg, PhD
erosenb...@hygeiabiomedical.com wrote:
 On Sun, Sep 2, 2012 at 6:45 AM, Ethan Rosenberg, PhD
 erosenb...@hygeiabiomedical.com 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



[PHP-DB] Prepared Statements Insert Problem

2012-09-01 Thread Ethan Rosenberg, PhD

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

2011-11-15 Thread Gavin Chalkley

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

2011-11-12 Thread tamouse mailing lists
On Fri, Nov 11, 2011 at 10:41 AM, Ron Piggott
ron.pigg...@actsministries.org wrote:
 ?php

 $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



[PHP-DB] Prepared Statements

2011-11-11 Thread Ron Piggott

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

===

?php

$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()) {

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

2011-11-11 Thread Matijn Woudt
On Fri, Nov 11, 2011 at 5:41 PM, Ron Piggott
ron.pigg...@actsministries.org 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 With Multiple Databases

2011-09-30 Thread Ron Piggott

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 With Multiple Databases

2011-09-30 Thread Bastien


On 2011-09-30, at 8:26 PM, Ron Piggott ron.pigg...@actsministries.org 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 Rows Selected

2011-05-23 Thread Ron Piggott

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

2011-05-23 Thread Giff Hammar
$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

2011-03-04 Thread Adriano Rodrigo Guerreiro Laranjeira

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 ron.pigg...@actsministries.org 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

2011-02-11 Thread Ron Piggott

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 ul ... /ul ... 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 ul\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 listrong . $search_result_organization . /strong (Ref:  . 
$search_result_organization_reference . )br /\r\n;
echo $search_result_city . ,  . $search_result_province_state .   . 
$search_result_country .   . $search_result_postal_zip_code . /li\r\n;

echo /ul\r\n;

The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info 


[PHP-DB] Prepared Statements and mySQL

2010-12-27 Thread Ron Piggott

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

2004-06-28 Thread Gerard Samuel
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

2001-09-13 Thread John Espey

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]