MySQLi : Dynamic bind_param() parameters

[undertaker]

I would like to dynamically pass data for bind parameters, depending on user selection.
This code gives me two errors:

Warning: Invalid argument supplied for foreach()
Warning: Wrong parameter count for mysqli_stmt::bind_param()

$query="SELECT COUNT(*) FROM database WHERE parameter_zero ='approved'";

if(!empty($_GET['first_parameter'])) {

$queryPart[] = 'first_parameter=?';
$type[]='s';
$value[]=$_GET['first_parameter'];

}


if(!empty($_GET['second_parameter'])) {

$queryPart[] = 'second_parameter=?';
$type[]='s';
$value[]=$_GET['second_parameter'];

}


if(!empty($_GET['third_parameter'])) {

$queryPart[] = 'third_parameter=?';
$type[]='s';
$value[]=$_GET['third_parameter'];

}

$query.= ' AND '.implode(' AND ',$queryPart);
$bind=array_merge($type,$value);

if ($res = $con->prepare($query)) {


call_user_func_array(array($count,'bind_param'),ref($bind));
$res->execute();
$cRes = $res-> $fetch_row();
$count -> close();

} else error($con->error);

echo $cRes;

function ref($arr) {
    
    $refs=array();
    foreach($arr as $key => $value) {
        $refs[$key] = &$arr[$key];
    }
    return $refs;
}

[mac_gyver]

$query="SELECT COUNT(*) FROM `database` WHERE parameter_zero ='approved'";

if(!empty($_GET['first_parameter'])) {
    $queryPart[] = 'first_parameter=?';
    $type[]='s';
    $value[]=$_GET['first_parameter'];
}

if(!empty($_GET['second_parameter'])) {
    $queryPart[] = 'second_parameter=?';
    $type[]='s';
    $value[]=$_GET['second_parameter'];
}

if(!empty($_GET['third_parameter'])) {
    $queryPart[] = 'third_parameter=?';
    $type[]='s';
    $value[]=$_GET['third_parameter'];
}

$query.= ' AND '.implode(' AND ',$queryPart);

if ($res = $con->prepare($query)) {

    $types = array(implode('',$type));
    $refs = ref($value);
    $bind=array_merge($types,$refs);
    
    call_user_func_array(array($res,'bind_param'),$bind);
    $res->execute();
    $Res = $res->get_result(); // if using mysqlnd driver, else you must use bind_result()
    list($cRes) = $Res->fetch_row();
    $res->close();

} else error($con->error);

echo $cRes;

function ref(&$arr){ // added & in call time parameter definition, must be a reference to the actual data
    
    $refs=array();
    foreach($arr as $key => $value) {
        $refs[$key] = &$arr[$key];
    }
    return $refs;
}

it looks like you just copy/pasted together something. at the level of trying to dynamically produce a prepared query, you really need to know what each statement does.

 

you also need to test your code when there are no $_GET parameters to make sure your query statement is correctly being built.

[undertaker]

I implemented your solution and it works, like a charm.

Thank you very much