PHP MySQL: Update Data

Summary: in this tutorial, you will learn how to update data in a MySQL table using PHP.

To update data in MySQL from PHP, you follow these steps:

• First, connect to the MySQL server.
• Second, prepare an UPDATE statement.
• Third, execute the UPDATE statement.

Updating data

The following update.php script sets the value in the completed column of row id 1 in the tasks table to true:

<?php
require_once 'config.php';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    
    $sql = 'update tasks 
            set completed=:completed 
            where id=:id';
    
    $stmt = $conn->prepare($sql);
    
    $stmt->execute([':completed' => true, ':id' => 1]);
} catch (PDOException $e) {
    die($e);
}Code language: PHP (php)

How it works.

First, include the config.php that contains the database configuration.

require_once 'config.php';Code language: PHP (php)

Second, establish a new connection to the todo database on the MySQL server:

$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);Code language: PHP (php)

Third, construct an UPDATE statement with two parameterized placeholders:

$sql = 'update tasks 
        set completed=:completed 
        where id=:id';Code language: PHP (php)

Fourth, prepare the statement for the execution:

 $stmt = $conn->prepare($sql);Code language: PHP (php)

Finally, execute the UPDATE statement with data:

 $stmt->execute([':completed' => true, ':id' => 1]);Code language: PHP (php)

If an error occurs, the script displays it and is terminated:

die($e);Code language: PHP (php)

Summary

• Use a prepared statement to update data in MySQL from PHP.