Sander Koedood Sander Koedood - 5 days ago 4
PHP Question

Insert array of unspecified length in MYSQLI

It's far from perfect, but I'm trying to create a function to insert data into a SQL table, using MySQLI. I want to create a generic function, to insert different types of data in different databases. I have the following so far:

/**
* Add data to specified table. Data consist of column name as key, and value.
* Table is a string of the table to insert into.
* @param array $data
* @param string $table
* @return string
*/
private function insert( $data = array(), $table = null ){

foreach( $data as $key => $value ){

// Create arrays of separate keys and values
$keys[] = $key;
$values[] = $value;

// Get type of data
switch( gettype( $value ) ){
case "integer":
$types[] = "i";
break;
case "string":
$types[] = "s";
break;
default:
$types[] = "i";
break;
};

// for each variable, add a questionmark
$vars[] = "?";

}

// Create strings out of the data
$key = implode( ",", $keys );
$var = implode( ",", $vars );
$type = implode( "", $types );
$value = '"' . implode( '\", \"', $values ) . '"';

// prepare SQL statement
// var_dump( $sql ) = 'INSERT INTO table (var1,var2,var3) VALUES (?,?,?)'
$sql = "INSERT INTO " . $table . " (" . $key . ") VALUES (" . $var . ")";

// Prepare SQL insert
// $this->conn = new mysqli($this->server, $this->user, $this->pass, $this->name);
if( ! ( $stmt = $this->conn->prepare( $sql ) ) ) {
return "Preparing failed!: (" . $this->conn->errno . ") " . $this->conn->error;
}

// Bind parameters. THIS IS WHERE THE ISSUE IS!
if( ! $stmt->bind_param( $type, $values ) ) {
return "Binding failed! (" . $stmt->errno . ") " . $stmt->error;;
}

// Execute the statement
if( ! $stmt->execute() ){
return "Executing failed! (" . $stmt->errno . ") " . $stmt->error;;
}

}


The issue is at binding the parameters. I can't find a good way to bind them, as I've got multiple variables with values and keys, but they're all in array format, and
bind_param
requires a new variable for each

In short, I'm looking for a way to add an array of unspecified length into my SQL (in a secure way, ofcourse).

Answer

If you are using php 5.6+, you can use the ... operator to unpack an array.

In your example:

$stmt->bind_param( $type, ...$values )

See example #14 in the manual.

Comments