Bind Variable Amount of Parameters in Prepared Statement

Question

I'm currently trying to build a little generic function to make inserts. My goal is to pass the table, the columns, values and types to fullfill an insert.

My only problem is the statement:

$stmt -> bind_param($types, $var1, $var2 ...);

What i would basically need is something like this:

$stmt -> bind_param($types, $array);

This is what i got till now:

function insert($into, $columns, $values, $types) {
    global $connection;

    // Check Correct Length
    if(count($columns) != count($values) ||
       count($columns) != count($types)) {
           return false;
       }

    $count = count($columns);

    $column_string = "";
    $value_string = "";
    $value_types = "";

    for($i = 0; $i < $count; $i++) {
        $column_string .= $columns[$i];
        $value_types .= $types[$i];

        $value_string .= '?';

        if($i + 1 < $count) {
            $column_string .= ',';
            $value_string .= ',';
        }
    }

    $sql = "INSERT INTO $into ($column_string) VALUES ($value_string)";

    // Execute Statement
    if($stmt = $connection -> prepare($sql)) {

        // $stmt -> bind_param("sss", $transaction, $email, $status);
        // What to do here?

        $stmt -> execute();

        $stmt -> close();
    }

The SQL statement already looks fine. Also the types are ready - i just need a way to dynamically bind the parameters...

Answer 1

Well I assume, that in $columns is defined as $columns = array('col1', 'col2'/*...*/); and $values as $values = array($val1, $val2/*...*/);

I would create function for column names escaping.

$escapeCols = function($column) { return sprintf('%s', $column); };

Another function for creating ? placeholders

$placeholders = function ($values) { return array_fill(0, count($values), '?'); }

You can than prepare query

$sql = sprintf( 'INSERT INTO %s (%s) VALUES (%s)', $table, implode(', ', array_map($escapeCols, $columns), implode(', ', $placeholders($values)) );

And then you can call execute

$stmt = $connection->prepare($sql); $stmt->execute($values);

This way can be easily transformed if you would have $values defined as

$values = array('col1' => $va1, 'col2' => $val2);