PHP MySQL: update data

Introduction: In this tutorial, you will learn how to use PHP PDO prepared statements to update data in a MySQL table.

Recommended related video tutorials: MySQL tutorial!

We will use the tasks table in the sample database for exercises. If you haven't created the table yet, follow the PHP MySQL Create Table tutorial to do it first.

The following figure illustrates the structure of the tasks table.

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

First, connect to the MySQL database by creating a new PDO object.

Secondly, construct an UPDATE statement to update the data. If you want to pass a value to an UPDATE statement, use a named placeholder, such as :name.

Then, the object's execute() method is called with an array containing the corresponding input values ​​for the named placeholders specified in the statement. PDOStatementUPDATE

PHP MySQL: Update Data Example

PHP MySQL - Updating a Single Row

Let’s take a look at the UpdateDataDemo lesson below.

<?php
 
/**
 * PHP MySQL Update data demo
 */
class UpdateDataDemo {
 
    const DB_HOST = &#39;localhost&#39;;
    const DB_NAME = &#39;classicmodels&#39;;
    const DB_USER = &#39;root&#39;;
    const DB_PASSWORD = &#39;&#39;;
 
    /**
     * 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 = [
            &#39;:taskid&#39; => $id,
            &#39;:subject&#39; => $subject,
            &#39;:description&#39; => $description,
            &#39;:start_date&#39; => $startDate,
            &#39;:end_date&#39; => $endDate];
 
 
        $sql = &#39;UPDATE tasks
                    SET subject      = :subject,
                         start_date  = :start_date,
                         end_date    = :end_date,
                         description = :description
                  WHERE task_id = :taskid&#39;;
 
        $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, &#39;MySQL PHP Update Tutorial&#39;, 
                    &#39;MySQL PHP Update using prepared statement&#39;, 
                    &#39;2013-01-01&#39;, 
                    &#39;2013-01-01&#39;) !== false)
    echo &#39;The task has been updated successfully&#39;;
else
    echo &#39;Error updated the task&#39;;

How the script works.

First, connect to the database by creating a new instance in the constructor of the UpdateDataDemo class through PDO.

Secondly, in the update() method, UPDATE uses named placeholders to construct the statement.

Then, use a prepared UPDATE statement to prepare the statement for execution and execute it using the array parameters.

You can use the following script to update the row with ID 2:

$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 the data tasks from the table to verify the update:

SELECT * FROM tasks;

In this tutorial, you Learned how to use PHP PDO prepared statements to update data in a MySQL table.

The above is the detailed content of PHP MySQL: update data. For more information, please follow other related articles on the PHP Chinese website!