How to run an INSERT query using Mysqli

1. Running INSERT query with raw Mysqli
2. Explanation
3. INSERT query with a helper function
4. Insert multiple rows in one query
5. INSERT query from associative array
6. Comments (5)

It goes without saying that you must use prepared statements for any SQL query that would contain a PHP variable. Therefore, as usually the INSERT query makes little sense without variables, it should always run through a prepared statement. To do so:

• create a correct SQL INSERT statement
• replace all variables in the query with with question marks (called placeholders or parameters)
• prepare the resulting query
• bind variables to placeholders
• execute the query

Just make sure you've got a properly configured mysqli connection variable that is required in order to run SQL queries and to inform you of the possible errors.

Running INSERT query with raw Mysqli

Just write a code like in this example

$sql = "INSERT INTO users (name, email, password) VALUES (?,?,?)";
$stmt= $db->prepare($sql);
$stmt->bind_param("sss", $name, $email, $password);
$stmt->execute();

and have your query executed without a single syntax error or SQL injection!

Explanation

What is going on here?

$sql = "INSERT INTO users (name, email, password) VALUES (?,?,?)";

Like it was said above, first we are writing an SQL query where all variables are substituted with question marks.

IMPORTANT! there should be no quotes around question marks, you are adding placeholders, not strings.

$stmt= $db->prepare($sql);

Then, the query is prepared. The idea is very smart. To avoid even a possibility of the SQL injection or a syntax error caused by the input data, the query and the data are sent to database server separately. So it goes on here: with prepare() we are sending the query to database server ahead. A special variable, a statement is created as a result. We would use this variable from now on.

$stmt->bind_param("sss", $name, $email, $passwor);

Then variables must be bound to the statement. The call consists of two parts - the string with types and the list of variables. With mysqli, you have to designate the type for each bound variable. It is represented by a single letter in the first parameter. The number of letters should be always equal to the number of variables. The possible types are

• i for integer
• d fof double (float)
• s for string
• b for blobs

NOTE that you can almost always safely use "s" for any variable.

So you can tell now that "sss" means "there would be 3 variables, all of string type". And then, naturally, three variables obediently follow.

$stmt->execute();

Then the query finally gets executed. Means variables get sent to database server and the query is actually executed.

NOTE that you don't have to check the execution result. Given you have the proper connection code mentioned above, in case of error mysqli will raise an error automatically.

INSERT query with a helper function

As you may noted, the code is quite verbose. Luckily, in the recent PHP version (8.2) another function has been added to ease the task

$sql = "INSERT INTO users (name, email, password) VALUES (?,?,?)";
$db->execute_query($sql, [$name, $email, $password]);

Simple, clean and safe!

In case your PHP version is not that recent, I encourage you to upgrade, but if it's impossible at the moment, then there is a simple temporary substitution that you can add to your code.

Insert multiple rows in one query

Assuming you have array of arrays, something like this

$data = [
    4, 'foo', 10,
    2, 'bar', 15,
    5, 'baz', 20,
];

You can write a code that would create a query with multiple VALUES clauses, like this

INSERT INTO t (col1,col2,col3) VALUES (?,?,?),(?,?,?),(?,?,?)

And then execute it with all data in one go:

$sql = "INSERT INTO t (col1,col2,col3) VALUES ";
$values = "(?".str_repeat(",?",count($data[0])-1).")"; // (?,?,?)
$sql .= $values.str_repeat(",$values",count($data)-1); // (?,?,?),(?,?,?)

$stmt = $mysqli->prepare($sql);

$types = str_repeat("s", count($data) * count($data[0])); // sss
$params = array_merge(...$data);
$stmt->bind_param($types, ...$params);

$stmt->execute();

Or the same with execute_query() (instead of prepare and below):

$mysqli->execute_query($sql, array_merge(...$data));

INSERT query from associative array

It is often happens that we have an array consists of fields and their values that represents a row to be inserted into a database. And naturally it would be a good idea to have a function to convert such an array into a correct SQL INSERT statement and execute it. So here it goes (utilizing a helper function mentioned above but you can easily rewrite it to raw mysqli if you'd like to):

First of all this function will need a helper function of its own. We will need a function to escape MySQL identifiers. Yes, all identifiers must be quoted and escaped, according to MySQL standards, in order to avoid various syntax issues.

function escape_mysql_identifier($field){
    return "`".str_replace("`", "``", $field)."`";
}

And now we can finally have a function that accepts a table name and an array with data and runs a prepared INSERT query against a database:

function prepared_insert($db, $table, $data) {
    $keys = array_keys($data);
    $keys = array_map('escape_mysql_identifier', $keys);
    $fields = implode(",", $keys);
    $table = escape_mysql_identifier($table);
    $placeholders = str_repeat('?,', count($keys) - 1) . '?';
    $sql = "INSERT INTO $table ($fields) VALUES ($placeholders)";
    prepared_query($db, $sql, array_values($data));
}