How to run an UPDATE query using Mysqli

1. Running UPDATE query with raw Mysqli
2. UPDATE query with a helper function
3. UPDATE query from an array
4. Comments (4)

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

• create a correct SQL UPDATE 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

Running UPDATE query with raw Mysqli

Here is a simple example. First of all, make sure that there is a proper connection to mysqli. Then write a code like this (adding your own table and field names and variables):

$sql = "UPDATE users SET name=?, email=?, password=? WHERE id=?";
$stmt= $conn->prepare($sql);
$stmt->bind_param("sssi", $name, $email, $password, $id);
$stmt->execute();

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

What is going on here?

$sql = "UPDATE users SET name=?, email=?, password=? WHERE id=?";

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= $conn->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 contains a mysqli statement is created as a result. We would use this variable from now on.

$stmt->bind_param("sssi", $name, $email, $password, $id);

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 "sssi" means "there would be 3 variables of string type and the last one of integer type". And then, naturally, four 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.

UPDATE query with a helper function

As you may noted, the code above is quite verbose. If you like to build a code like a Lego figure, with shining ranks of operators, you may keep it as is. If you, like me, hate useless repetitions and like to write concise and meaningful code, then there is a simple helper function. With it, the code will become two times shorter:

$sql = "UPDATE users SET name=?, email=?, password=? WHERE id=?";
prepared_query($conn, $sql, [$name, $email, $password, $id]);

Here we are calling the helper function using a connection variable, an sql query and an array with variables. Simple, clean and safe!

UPDATE query from an array

An UPDATE function is a tricky business, as it requires a WHERE clause which at best requires a full featured Query Builder. And trust me, you don't want to go down that rabbit hole for a simple helper function.

What can be suggested for the matter is a quick palliative, a function where a WHERE clause limited to the primary key lookup with the primary key field name hardcoded, in this case as id:

function prepared_update_by_id($conn, $table, $data, $id) {
    $table = escape_mysql_identifier($table);
    $sql = "UPDATE $table SET ";

    foreach (array_keys($data) as $i => $field) {
        $field = escape_mysql_identifier($field);
        $sql .= ($i) ? ", " : "";
        $sql .= "$field = ?";
    }       
    $sql .= " WHERE id = ?";
    $data[] = $id;
    prepared_query($conn, $sql, array_values($data));
}

In order for this function to work we will need aforementioned helper function as well as 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)."`";
}