Bind parameters dynamically to a mysqli statement

Question

I am trying to build a dynamic prepared statement but I am stuck at the bind_param part. I tried to read other answers referring to call_user_func_array but I couldn't figure out how to adapt it here:

//connecting
$connection = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);

//info submitted through a form
$values_columns = array(
                        "column_one" => "value_one",    //value_one will be replaced by $_POST['value_one']
                        "column_two" => "value_two",    //value_two will be replaced by $_POST['value_two']
                        "column_three" => "value_three" //value_three will be replaced by $_POST['value_three']
                       );

$value_type = implode('', array('s', 's', 's'));

//preparing and binding my query dinamically
function prepare_bind($table_name, $values_columns, $value_type) {

    global $connection;

    $columns_string = "";
    $question_marks = "";

    $flag = 0;
    $count = count($values_columns);

    foreach ($values_columns as $column => $value) {

        $flag++;

        // building the prepare
        if ($flag == $count) {
            $columns_string .= $column; 
            $question_marks .= "?";
        } else {
            $columns_string .= $column . ", ";  
            $question_marks .= "?, ";
        }
    }

    $sql = $connection->prepare('INSERT INTO ' . $table_name . ' (' . $columns_string . ') VALUES (' . $question_marks . ')');
    $sql->bind_param($value_type, /*I am stuck here while trying to add the values*/);
}

Is there any way to get out of this situation. It is the first time I am using this approach and I don't know if I got it right. Thank you very much.

You're better off using an array of values, then `join(', ', $arr)` to avoid having the trailing comma. You can use `array_fill` to generate an array of question marks. — Ian, Dec 16 '15 at 20:15
maybe interesting so that you can see the logic? https://github.com/aaronlord/mysqli. (i have this working here). Also, internet search: `'github mysqli class'` for other classes. — Ryan Vincent, Dec 16 '15 at 21:06

score 1 · Accepted Answer · answered Dec 16 '15 at 20:17

1

Easiest solution to generating the placeholders:

function placeholderMarks($count) {
    return join(', ', array_fill(0, $count, '?'));
}

Insert the result of this function into the query with placeholderMarks(count($values_columns))

answered Dec 16 '15 at 20:17

Ian

24,116
22
58
96

Bind parameters dynamically to a mysqli statement

1 Answers1