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

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