Update Postgres Data using a Transaction in PHP

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’re interacting with a PostgreSQL database from a PHP application, one operation you may need to perform is an update. Fortunately, it’s easy to accomplish this task using the PHP PDO. In this article, we’ll provide sample code to show you how to update Postgres data using a transaction in PHP.

Prerequisite

Before you proceed, be sure to install and configure the following items:

Creating Sample Dataset

In this section, we’re going to create a sample dataset that we can use in this tutorial for demo purposes.

  • To create our sample database, we use the following command:
1
create database restaurant;
  • To create a table within that database, we’ll use the command shown below:
1
2
3
4
5
CREATE TABLE IF NOT EXISTS food (
id SERIAL PRIMARY KEY,
menu_name CHARACTER VARYING(100),
price INTEGER
);

We can then insert sample data using the following SQL statement:

1
2
3
4
5
6
7
INSERT INTO food (id, menu_name, price)
VALUES
(1,'Black Bean Veggie Burger', 9),
(2,'California Chicken Burger', 8),
(3,'Hickory Burger', 9),
(4,'Mushroom Swiss Burger', 8),
(5,'Grass-Fed Burger', 9);

If we do a simple SELECT statement, we should see output that looks like the following:

1
2
3
4
5
6
7
8
9
restaurant=# select * from food;
id | menu_name | price
----+---------------------------+-------
1 | Black Bean Veggie Burger | 9
2 | California Chicken Burger | 8
3 | Hickory Burger | 9
4 | Mushroom Swiss Burger | 8
5 | Grass-Fed Burger | 9
(5 rows)

Project Structure

Now that we have our sample dataset in place, we’re going to create our project files. See the below image for reference on how to structure your project directory:

alt text

How to update data in PHP via transaction

At this point, we’re ready to code the PHP file that we’ll be using to update Postgres data using a transaction in PHP.

To do this, we’lll be modifying a PHP file called ‘phpupdatetrans.php’.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<?php

namespace updatetransaction;

class phpupdatetrans {

private $pdo;

/**
* Initialize the object with a specified PDO object
* @param \PDO $pdo
*/

public function __construct($pdo) {
$this->pdo = $pdo;
}

/**
* update a food item
* @param int $id
*/

// the function accepts three arguments
public function updatedFood($id, $menu_name, $price) {
try {
// we start the transaction
$this->pdo->beginTransaction();

// sql statement to update a row in the stock table
$sql = 'UPDATE food '
. 'SET menu_name = :menu_name, '
. 'price = :price '
. 'WHERE id = :id';

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

// bind values to the statement
$stmt->bindValue(':menu_name', $menu_name);
$stmt->bindValue(':price', $price);
$stmt->bindValue(':id', $id);
// update data in the database
$stmt->execute();

// commit the changes if no error encountered
$this->pdo->commit();
return $stmt->execute();
} catch (\PDOException $e) {
// we rollback the changes should we encounter an error
$this->pdo->rollBack();
throw $e;
}
}

}

Although you can just copy and paste the above code into your own file, it’s important to pay attention to the comments in it. The code is heavily commented to help you understand what’s going on in this PHP script.

Next, we’ll look at the following script, which is contained in the file ‘index.php’.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php

require 'vendor/autoload.php';

use updatetransaction\Connection as Connection;
use updatetransaction\phpupdatetrans as phpupdatetrans;

try {
$pdo = Connection::get()->connect();


$updateTransaction = new phpupdatetrans($pdo);

$affectedRows = $updateTransaction->updatedFood(2, 'Bison Burger', 10);

echo 'Number of row affected ' . $affectedRows;

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

To test our PHP application, we can use the following URL: http://localhost/updatetransaction/index.php

The output should look something like this:

alt text

We can query our table again to see if we updated our record successfully:

1
2
3
4
5
6
7
8
9
restaurant=# SELECT * FROM food;
id | menu_name | price
----+--------------------------+-------
1 | Honey Glazed Onion Rings | 8
3 | Fried Calamari | 9
4 | Antipasto Platter | 10
5 | Grilled Artichoke | 7
2 | Bison Burger | 10
(5 ROWS)

Conclusion

You can perform many different PostgreSQL database operations from a PHP application using the PHP PDO. In this article, we focused on the process of updating data. We walked you through all the code needed to update Postgres data using a transaction in PHP. With our sample code to get you started, you’ll be able to update Postgres data from your own PHP applications.

The Code

Shown below is the code used for other files that we did not discuss in the previous sections of this tutorial:

Connection.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
<?php

namespace updatetransaction;

/**
* Represent the Connection
*/

class Connection {

/**
* Connection
* @var type
*/

private static $conn;

/**
* Connect to the database and return an instance of \PDO object
* @return \PDO
* @throws \Exception
*/

public function connect() {

// This code will read the parameters on the Db.ini file using the parse_ini_file() function

$paramDb = parse_ini_file('param.ini');
if ($paramDb === false) {
throw new \Exception("Error reading database config file, please check param.ini file");
}
// The following code will connect us to the database using the parsed parameters
$connString = sprintf("pgsql:host=%s;port=%d;dbname=%s;user=%s;password=%s",
$paramDb['host'],
$paramDb['port'],
$paramDb['database'],
$paramDb['user'],
$paramDb['password']);

$pdo = new \PDO($connString);
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

return $pdo;
}

/**
* this code will return an instance of the Connection object
* @return type
*/

public static function get() {
if (null === static::$conn) {
static::$conn = new static();
}

return static::$conn;
}

protected function __construct() {

}

}

Param.ini

1
2
3
4
5
host=localhost
port=5432
database=restaurant
user=postgres
password=1234

composer.json

1
2
3
4
5
6
7
{
"autoload": {
"psr-4": {
"updatetransaction\": "app/"
}
}
}

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.