PHP PDO Update

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

Updating data from PHP using PDO

To update data in a table from PHP using PDO, you follow these steps:

  • First, connect to the database by creating a new instance of the PDO class.
  • Next, construct an SQL UPDATE statement to update data in a table.
  • Then, call the prepare() method of the PDO object. The prepare() method returns a new instance of the PDOStatement class.
  • After that, bind the values to the UPDATE statement by calling the bindParam() method of the PDOStatement object.
  • Finally, execute the statement by calling the execute() method of the PDOStatement.

PHP PDO update example

The following example shows how to update the publisher with id 1 in the publishers table:

<?php

// connect to the bookdb database
$pdo = require_once 'connect.php';

$publisher = [
	'publisher_id' => 1,
	'name' => 'McGraw-Hill Education'
];

$sql = 'UPDATE publishers
        SET name = :name
        WHERE publisher_id = :publisher_id';

// prepare statement
$statement = $pdo->prepare($sql);

// bind params
$statement->bindParam(':publisher_id', $publisher['publisher_id'], PDO::PARAM_INT);
$statement->bindParam(':name', $publisher['name']);

// execute the UPDATE statment
if ($statement->execute()) {
	echo 'The publisher has been updated successfully!';
}
Code language: HTML, XML (xml)

How it works.

First, connect to the bookdb database and get an instance of the PDO object. Check the connect.php script for the detail.

$pdo = require_once 'connect.php';Code language: PHP (php)

Second, define data for updating and construct an UPDATE statement:

$publisher = [
	'publisher_id' => 1,
	'name' => 'McGraw-Hill Education'
];

$sql = 'UPDATE publishers
        SET name = :name
        WHERE publisher_id = :publisher_id';Code language: PHP (php)

The UPDATE statement uses the named placeholders :publisher_id.

Third, prepare the UPDATE statement for execution by calling the prepare() method:

$statement = $pdo->prepare($sql);Code language: PHP (php)

Fourth, bind the values to the UPDATE statement:

// bind values
$statement->bindParam(':publisher_id', $publisher['publisher_id'], PDO::PARAM_INT);
$statement->bindParam(':name', $publisher['name']);Code language: PHP (php)

Finally, execute the UPDATE statement by calling the execute() method of the PDOStatement object:

if ($statement->execute()) {
	echo 'The publisher has been updated successfully!';
}Code language: PHP (php)

To get the number of updated rows, you call the rowCount() method of the PDOStatement object.

Summary

  • Use a prepared statement to update data in a table from PHP using PDO.
Did you find this tutorial useful?