How to Use PHP MySQL Update: A Cheat Sheet for Correct Syntax

By now, you know how to make a table and use various MySQL statements to insert, delete, and access your information. This time, we are going to present you with one more way to save time: instead of deleting a row and inserting a new one in your table, you can use PHP MySQL update to overwrite it.

In MySQL, overwriting requires knowledge on how to use a particular statement called Update. That is why we will keep this lesson rather short. You will be presented with an example to study and introduced to the correct PHP MySQL update syntax.

PHP MySQL Update: Main Tips

• SQL includes the update statement, which updates existing records by overwriting them.
• To specify which records we want to update, you can use where after select and update statements.

Syntax and Usage

For the statement to be executed properly, you should make sure you are using the correct update MySQL syntax:

UPDATE `table_name` SET `column_name` = `new_value' [WHERE condition];

Look at the example below. Here we have a basic table, called Users. Let's see the way to update table SQL suggests:

 
The examples below show multiple ways you can write a PHP update MySQL query using the select statement. Which one you should use depends on the type of connection you have chosen (MySQLi object-oriented, MySQLi procedural or PDO).

PHP MySQL update statement will take the row containing user_id with the value of 2, and update its username column value:

<?php
  $server = 'host'; 
  $user = 'user'; 
  $pass = 'pass'; 
  $db = 'db';
  // Create connection
  $conn = new mysqli($host, $user, $pass, $db);  	
  // Check connection  	
  if ($conn->connect_error) {
      die("Could not connect: " . $conn->connect_error);
  }
  $sql = "UPDATE users SET username='john.doe' WHERE user_id=2";
  if ($conn->query($sql) === TRUE) {
    echo "Update successful.";
  } else {
    echo "Could not update: " . $conn->error;
  }
  $conn->close();  
?>

<?php
  $server = 'host';
  $user = 'user'; 
  $pass = 'pass'; 
  $db = 'db';
  // Create connection  
  $conn = mysqli_connect($server, $user, $pass, $db);  	
  // Check connection  	
  if (!$conn) { 
    die("Could not connect: " . mysqli_connect_error());
  }
  $sql = "UPDATE users SET username='john.doe' WHERE user_id=2";  
  if (mysqli_query($conn, $sql)) {
    echo "Update successful.";
  } else {
    echo "Could not update: " . mysqli_error($conn);
  }
  mysqli_close($conn);  
?>

<?php
  $server = 'host';
  $user = 'user';  
  $pass = 'pass';
  $db = 'db';
  try {
    $conn = new PDO("mysql:host=$server;dbname=$db",$user, $pass); 
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
    $sql = "UPDATE users SET uesrname='john.doe' WHERE user_id=2";  	
    // Prepare statement
    $stmt = $conn->prepare($sql);
    // execute the query   
    $stmt->execute();
    // echo a message to say the  
    UPDATE succeeded
    echo $stmt->rowCount() . "Update successful";
  } catch (PDOException $e) {
    echo $sql . "</br>" . $e->getMessage();
  }
  $conn = null;  
?>

Once we're done with the PHP MySQL update query, our table will look like this:

PHP MySQL Update: Summary

• By using MySQL update statement, you can overwrite any records your data table holds.
• where statement is used to specify the exact records you wish to update.