Dennis Castelijns Dennis Castelijns - 2 months ago 18
PHP Question

Problems when binding values

I am working for the first time with prepared statements but I got stuck at this error. For some reason I cant pass the bind types as a parameter?

My code:

function insert($table, $columns = array(), $bindTypes, $values = array()) {
// Store connection.
$connection = connection();
$columnValues = null;
$index = 1;

// Prepare unassigned value string.
foreach ($columns as $column) {
$columnValues .= '?';
if ($index < count($columns)) {
$columnValues .= ', ';
}
$index++;
}

// Debugg purpose: echo query example:
echo "INSERT INTO {$table} (" . implode(', ', $columns) . ") VALUES ({$columnValues})";

// Prepare statement.
$statement = $connection->prepare("INSERT INTO {$table} (" . implode(', ', $columns) . ") VALUES ({$columnValues})");
$statement->bind_param($bindTypes, implode(', ', $values));
}


insert('test', array('name'), 'i', array(1));


If I echo the query example I get "INSERT INTO test (name) VALUES (?)".

Please spare me I am still a learning noob.

Answer

PHP > 5.6 version:

function insert($connection, $table, $columns, $values) {
    $columnStr = "`".implode("`,`",$columns); 
    $valueStr =  str_repeat('?,', count($values) - 1) . '?';
    $types = str_repeat('s', count($values));
    $statement = $connection->prepare("INSERT INTO `$table` ($columnStr) VALUES ($valueStr)");
    $statement->bind_param($types, ...$values);
    $statement->execute();
}

insert($conn, 'test', ['name'], [1]);

Note that you should never ever take table or column names out of user input but always have them hard-coded in your script. Which makes a distinct function for the insert quite useless.

Comments