PHP PDO Update Query Example
Tags: pdo, php, javascript, tutorial
Published: 2022-04-07 07:24:01

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.

```php
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
<?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
<?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
<?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!";
}

```

I hope this tutorial can help you. Kindly visit here https://codeanddeploy.com/blog/php/php-pdo-update-query-example if you want to download this code.

Happy coding :)