PHPs mysqli prepare - generate dynamically

Question

My code to generate the SQL statement works fine - however I run into a hiccup when generating the string for $stmt->bind_param(). Code is as follows:

$stmt = $mysqli->stmt_init();
if ($stmt->prepare ($sql)) {
    $bind_types = '"';
    $bind_values = '';

    if ($action == 'insert' || $action == 'update') {
        reset ($array);
        foreach ($array as $key => $value) {
            if (is_string ($value)) { $type = 's'; } else if (is_int ($value)) { $type = 'i'; } else if (is_float ($value)) { $type = 'd'; } else { die ('Cannot determine type for ' . $key . ' => ' . $value . ''); }
            $bind_types .= $type;
            $bind_values .= $value . ', ';
            //$stmt->bind_param ($type, $value);
        }
    }
    
    if ($action == 'update' || $action == 'delete') {
        if (is_string ($id_value)) { $type = 's'; } else if (is_int ($id_value)) { $type = 'i'; } else if (is_float ($id_value)) { $type = 'd'; } else { die ('Cannot determine type for ' . $id_column . ' => ' . $id_value . ''); }
        $bind_types .= $type;
        $bind_values .= $id_value . ', ';
        //$stmt->bind_param ($type, $id_value);
    }
    
    $bind_types .= '"';
    
    $bind_values = substr ($bind_values, 0, -2);
    
    echo $bind_types  . ', ' . $bind_values;
    
    $stmt->bind_param ($bind_types, $bind_values);
    $stmt->execute();
    
}

The formatting of that got messed up. I apologize if its hard to read.

I am getting the following error:

"Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables in ... "

Any ideas?

Answer 1

I would highly advise to use PDO as you can do it easily. If you want to do it in mysqli it is more complicated since you can't easily bind them dynamically. To bind them dynamically look at this ugly hack

$bind_values= explode(',', $bind_values);
call_user_func_array(array($stmt, 'bind_param'), makeValuesReferenced($bind_values));
$stmt->execute();

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

}