PHP PDO Update Query Example

#pdo #php #javascript #tutorial

Originally posted @ https://codeanddeploy.com visit and download the sample code: https://codeanddeploy.com/blog/php/php-pdo-update-query-example

In this post, I'm sharing how PHP PDO Update work with an example so that easier for you to understand. Updating record is one of the most important functionality to work in PHP Development using PDO.

Update SQL Statement Table

To work on this PHP PDO Update operation we need to set up first our database and table. In this example, we are working with a basic posts table. See below sample SQL statement of our posts table.

CREATE TABLE `posts` (
  `id` int(11) NOT NULL,
  `title` varchar(150) NOT NULL,
  `content` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `posts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;

PDO Update Query with Positional Placeholders Example

Positional placeholder brief and easier to use. See below example code:

<?php

$host     = 'localhost';
$db       = 'demos';
$user     = 'root';
$password = '';

$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

try {
     $conn = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

} catch (PDOException $e) {
     echo $e->getMessage();
}

$data = [
     'test title 2 - update', 'content 2 - update', 2
];

$sql = "UPDATE posts SET title=?, content=? WHERE id=?";

$statement = $conn->prepare($sql);

if($statement->execute($data)) {
  echo "Post updated successfully!";
}

?>

PDO Update Query with Named Placeholders

If you want a clearer predefined array with values this example is for you. See below code:

<?php

$host     = 'localhost';
$db       = 'demos';
$user     = 'root';
$password = '';

$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

try {
     $conn = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

} catch (PDOException $e) {
     echo $e->getMessage();
}

$data = [
     'title'=>'test title 2.1 - update', 
     'content' => 'content 2.1 - update', 
     'id' => 2
];

$sql = "UPDATE posts SET title=:title, content=:content WHERE id=:id";

$statement = $conn->prepare($sql);

if($statement->execute($data)) {
  echo "Post updated successfully!";
}

?>

PDO Update Query with Named Placeholders using bindParam() Method

Using bindParam() is used to bind a parameter to the specified variable in a SQL Statement.

<?php

$host     = 'localhost';
$db       = 'demos';
$user     = 'root';
$password = '';

$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

try {
     $conn = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

} catch (PDOException $e) {
     echo $e->getMessage();
}

$data = [
     'title'=>'test title 2.2 - update', 
     'content' => 'content 2.2 - update', 
     'id' => 2
];

$sql = "UPDATE posts SET title=:title, content=:content WHERE id=:id";

$statement = $conn->prepare($sql);

$statement->bindParam(':id', $data['id'], PDO::PARAM_INT);
$statement->bindParam(':title', $data['title']);
$statement->bindParam(':content', $data['content']);

if($statement->execute()) {
     echo "Post updated successfully!";
}