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