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