PHP MySQL UPDATE Query

UPDATE statement is used to update the records in a MySQL database table.

Updating Database Table Data

The UPDATE statement is used to change or modify the existing records in a database table. It is typically used in conjugation with the WHERE clause to apply the changes to only those records that matches specific criteria.

The basic syntax of the UPDATE statement can be given with:

UPDATE table_name SET column1=value, column2=value2,... WHERE column_name=some_value

Let's make a SQL query using the UPDATE statement and WHERE clause, after that we will execute this SQL query through passing it to the mysqli_query() function to update the tables records. Consider the following "persons" table inside the "demo" database:

+-----------+------------+-----------+----------------------+
| person_id | first_name | last_name | email_address        |
+-----------+------------+-----------+----------------------+
|         1 | Peter      | Parker    | peterparker@mail.com |
|         2 | John       | Rambo     | johnrambo@mail.com   |
|         3 | Clark      | Kent      | clarkkent@mail.com   |
|         4 | John       | Carter    | johncarter@mail.com  |
|         5 | Harry      | Potter    | harrypotter@mail.com |
+-----------+------------+-----------+----------------------+

The PHP code in the following example update the email address of a person in the persons table whose first_name='Peter' and last_name='Parker'.

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $link = mysqli_connect("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($link === false){
  •     die("ERROR: Could not connect. " . mysqli_connect_error());
  • }
  •  
  • // Attempt update query execution
  • $sql = "UPDATE persons SET email_address='peterparker_new@mail.com' WHERE first_name='Peter' AND last_name='Parker'";
  • if(mysqli_query($link, $sql)){
  •     echo "Records were updated successfully.";
  • } else {
  •     echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  • }
  •  
  • // Close connection
  • mysqli_close($link);
  • ?>

After update the persons table will look something like this:

+-----------+------------+-----------+--------------------------+
| person_id | first_name | last_name | email_address            |
+-----------+------------+-----------+--------------------------+
|         1 | Peter      | Parker    | peterparker_new@mail.com |
|         2 | John       | Rambo     | johnrambo@mail.com       |
|         3 | Clark      | Kent      | clarkkent@mail.com       |
|         4 | John       | Carter    | johncarter@mail.com      |
|         5 | Harry      | Potter    | harrypotter@mail.com     |
+-----------+------------+-----------+--------------------------+
 

Warning:The WHERE clause in the UPDATE statement specifies which record or records should be updated. If you omit the WHERE clause, all records will be updated.

 
Close

Your Feedback:

 

We would love to hear from you! Please say something.