PHP MySQL: Update Data

 PHP MySQL: Update Data

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

We are going to use the tasks table in the sample database for practicing. If you have not yet created the tasks table, please follow the PHP MySQL create table tutorial to complete it first.

The following picture illustrates the structure of the tasks table.

To update data in a table, you use the following steps:

• First, connect to the MySQL database by creating a new PDO object.
• Second, construct an UPDATE statement to update data. If you want to pass values to the UPDATE the statement, you use the named placeholders such as :name.
• Third, call the execute()  method of the PDOStatement object with an array that contains the corresponding input values of the named placeholders specified in the UPDATE statement.

PHP MySQL: update data example

PHP MySQL – update a single row

Let’s take a look at the following UpdateDataDemo class.

<?php

/**
 * PHP MySQL Update data demo
 */
class UpdateDataDemo {

    const DB_HOST = 'localhost';
    const DB_NAME = 'classicmodels';
    const DB_USER = 'root';
    const DB_PASSWORD = '';

    /**
     * PDO instance
     * @var PDO
     */
    private $pdo = null;

    /**
     * Open the database connection
     */
    public function __construct() {
        // open database connection
        $connStr = sprintf("mysql:host=%s;dbname=%s", self::DB_HOST, self::DB_NAME);
        try {
            $this->pdo = new PDO($connStr, self::DB_USER, self::DB_PASSWORD);
        } catch (PDOException $e) {
            die($e->getMessage());
        }
    }

    /**
     * Update an existing task in the tasks table
     * @param string $subject
     * @param string $description
     * @param string $startDate
     * @param string $endDate
     * @return bool return true on success or false on failure
     */
    public function update($id, $subject, $description, $startDate, $endDate) {
        $task = [
            ':taskid' => $id,
            ':subject' => $subject,
            ':description' => $description,
            ':start_date' => $startDate,
            ':end_date' => $endDate];


        $sql = 'UPDATE tasks
                    SET subject      = :subject,
                         start_date  = :start_date,
                         end_date    = :end_date,
                         description = :description
                  WHERE task_id = :taskid';

        $q = $this->pdo->prepare($sql);

        return $q->execute($task);
    }

    /**
     * close the database connection
     */
    public function __destruct() {
        // close the database connection
        $this->pdo = null;
    }

}

$obj = new UpdateDataDemo();

if ($obj->update(2, 'MySQL PHP Update Tutorial', 
                    'MySQL PHP Update using prepared statement', 
                    '2013-01-01', 
                    '2013-01-01') !== false)
    echo 'The task has been updated successfully';
else
    echo 'Error updated the task';

How the script works.

• First, connect to the database by creating a new PDO an instance in the constructor of the UpdateDataDemo class.
• Second, in the update() a method, construct the UPDATE a statement with named placeholders.
• Third, use a prepared statement to prepare the UPDATE statement for the execution and execute it with an array argument.

you can update a row with id 2 using the following script:

$obj = new UpdateDataDemo();

if($obj->update(2,
				'MySQL PHP Update Tutorial',
				'MySQL PHP Update using prepared statement', 
				'2013-01-01',
				'2013-01-01') !== false)
	echo 'The task has been updated successfully';
else 
	echo 'Error updated the task';

You can query data from the tasks table to verify the update:

SELECT * 
FROM
   tasks;

You can download the code via the following link:

Download PHP MySQL Update Source Code

PHP MySQL – update rows in related tables

There are three ways to update rows in related tables:

• Use the MySQL UPDATE JOIN statement.
• Use multiple UPDATE statements inside a transaction.

We will examine the second way in the PHP MySQL transaction tutorial.

In this tutorial, you have learned how to update data in the MySQL table using the PHP PDO prepared statement.