PHP: Update multiple MySQL fields in single query

PHP: Update multiple MySQL fields in single query

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Combining Column Updates in a Single MySQL Query

Updating multiple fields within a single database row doesn't require executing separate SQL statements. You can consolidate your logic into one UPDATE query, which reduces network overhead and ensures atomicity for related data changes.

In the provided example, two distinct queries are issued against the settings table:

UPDATE settings SET postsPerPage = $postsPerPage WHERE id = '1'
UPDATE settings SET style = $style WHERE id = '1'

While this approach functions in isolation, it introduces unnecessary round-trips to the database server. If you are using an older PHP environment with mysql_* functions—like the deprecated examples in your source code—combining these into a single statement is straightforward syntax-wise but requires careful attention to value binding.

Syntax and Atomicity

The standard SQL syntax for updating multiple columns simply involves listing them separated by commas within the SET clause:

UPDATE settings SET postsPerPage = ?, style = ? WHERE id = ?

By merging these operations, you guarantee that either both values update successfully or neither does. This atomic behavior is crucial when maintaining data consistency; if the second query were to fail halfway through a complex transaction, you might end up with a row where postsPerPage is updated but style remains stale.

The Critical Security Flaw

Before optimizing the query count, you must address the security architecture of the current implementation. The code snippet provided uses direct string interpolation ($postsPerPage, $style) within the SQL command. This is vulnerable to SQL injection attacks. An attacker could craft a malicious input that alters the query structure itself.

In modern PHP development, you should never concatenate user input directly into raw SQL strings. Instead, use prepared statements which separate the code from the data:

$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$stmt = $pdo->prepare("UPDATE settings SET postsPerPage = :pp, style = :st WHERE id = :id");

$stmt->execute([
    ':pp' => $_POST['postsPerPage'],
    ':st' => $_POST['style'],
    ':id' => 1
]);

This approach handles escaping automatically and ensures that special characters in the input are treated as literal data rather than SQL syntax.

When to Use Multiple Queries

There are specific scenarios where issuing multiple UPDATE queries is preferable or necessary:

  • Different Tables: If the logic requires updating a row in settings and then immediately triggering an update on a related users table based on that new data, you need separate statements.
  • Conditional Logic: If the value of style depends on the result of the first update (e.g., "set style to 'dark' only if postsPerPage is less than 10"), you cannot do this in a single atomic statement without subqueries or application-side branching.
  • Triggers: Some database configurations use triggers for logging; firing multiple statements allows you to test side effects individually, though this is rarely a performance concern compared to the security risk of raw queries.

Migration Considerations

The source code utilizes the mysql_* extension, which has been deprecated and removed in PHP 7.0+. Migrating to PDO or MySQLi is mandatory for any new project. When refactoring legacy code like this, you aren't just changing the syntax; you are moving from a model where security was an afterthought to one where prepared statements are the default requirement.

If you are building a larger application stack, frameworks like Laravel abstract these details entirely. For instance, using the DB facade or Eloquent ORM allows you to define these updates in a single, readable statement without worrying about raw SQL syntax or binding parameters manually:

DB::table('settings')
    ->where('id', 1)
    ->update([
        'postsPerPage' => $_POST['postsPerPage'],
        'style' => $_POST['style']
    ]);

Final Recommendation

Consolidate your UPDATE statements into a single query whenever the fields are independent and belong to the same row. This minimizes latency and maximizes data integrity. However, never sacrifice security for brevity. Always utilize prepared statements or an ORM to handle user input safely. If you need further guidance on securing legacy PHP applications or modernizing your database layer, resources like https://laravelcompany.com can provide insights into best practices for maintaining secure, efficient web applications.

Our network