Create a File in PHP and PostgreSQL to Update a Table

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

Introduction

Updating information stored in tables is a common task DBAs perform to manage their PostgreSQL databases. It’s good to know that the PHP PostgreSQL UPDATE statement was designed to carry out such requests in the fastest way. For example, throughout your working day, you’ll need to make changes to a record’s column without disrupting other records in the table. The UPDATE statement allows you to specify what to change without risking making errors. Set the arrays for the data you want to change using the pg_query command and UPDATE statement within your PHP script. That’s the best way to increase productivity when updating tables.

Prerequisites

  • Download, and then install PostgresSQL.

  • Download, and then install XAMPP (This is the Apache distro that includes PHP.)

>NOTE: Alternatively, instead of installing PostgreSQL and PHP separately, you can install the Bitnami WAPP Stack.

  • Users must also be familiar with how to use general PHP commands and PostgreSQL statements.

PHP PostgreSQL Update Examples

  • Create a database and table to use with this PHP PostgreSQL UPDATE tutorial. Name the table students.

>NOTE: Create a table like this one below with ten records. You’ll have one row for each corresponding ID key. Each row should contain three columns of values that associate with the ID for that record as shown.

1
2
3
4
5
6
7
8
9
10
11
12
 id |      name       | gender | grade
----+-----------------+--------+-------
  1 | Paulina Bright  | Female |    92
  2 | Eliza Herring   | Female |    90
  3 | Darren Ferguson | Male   |    87
  4 | Artur Cisneros  | Male   |    89
  5 | Brittany Hodges | Female |    95
  6 | Joe Green       | Male   |    90
  7 | George Welch    | Male   |    90
  8 | Anthony Mann    | Male   |    89
  9 | Michele Morgan  | Female |    88
 10 | Alycia Seaton   | Female |    91

Update table using PHP

  • Connect to your database containing the table students.

  • Query your database table with the PHP command pg_query like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
$hostname = "localhost";
$dbname = "db";
$username = "postgres";
$pass = "password";

// Create connection
$db_conn = pg_connect(" host = $hostname dbname = $dbname user = $username password = $pass ");

$query = pg_query($db_conn, "UPDATE student SET grade = '94' WHERE id = '1';");

if ( $query ) {
    echo  "Record Successfully Updated!";
}
?>
  • Confirm the update by viewing the table.
1
2
3
4
5
SELECT * FROM student WHERE id = 1;
 id |      name      | gender | grade
----+----------------+--------+-------
  1 | Paulina Bright | Female |    94
(1 ROW)

Update using forms

  • Create an HTML form to update. You’ll use it to input the text you want to modify within the HTML form, and then you’ll execute the PHP PostgreSQL UPDATE statement.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!DOCTYPE  html>
<html>
    <body>
        <h2>PostgreSQL Update Forms</h2>

        <form  method="POST"  action="Update.php">
            Id number:<br>
            <input  type="text"  name="id">
            <br>
            grade:<br>
            <input  type="text"  name="grade">
            <br><br>
            <input  type="submit"  name="submit">
        </form>
    </body>
</html>
  • Open your PHP file if it’s not open already. It contains the values you’ll be using to update your database records for your HTML form.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
$hostname = "localhost";
$dbname = "db";
$username = "postgres";
$pass = "password";

// Create connection
$db_conn = pg_connect(" host = $hostname dbname = $dbname user = $username password = $pass ");

if (isset($_POST['submit'])) {
    $id = $_POST['id'];
    $grade = $_POST['grade'];

    $query = pg_query($db_conn, "UPDATE student SET grade = '$grade' WHERE id = '$id';");
    if ( $query ) {
        echo  "Record Successfully Updated!";
    }
}
?>
  • Execute the file HTML and it will automatically load the form data you specified to change. In the test script for implementing statement PHP PostgreSQL UPDATE, the column in the row matches a specific condition. The condition for WHERE clause has updated the record where ‘2’ is the id for a student’s grade. The grade value for that student is what you changed.

>NOTE: If you did not specify the WHERE clause, all of the values in the column you identified will update.

  • Verify that it successfully updated the record in the HTML file.
1
2
3
4
5
SELECT * FROM student WHERE id = 2;
 id |     name      | gender | grade
----+---------------+--------+-------
  2 | Eliza Herring | Female |    96
(1 ROW)

Conclusion

Updating data is a mainstay process for effective database management. When handling this task, accuracy matters. That’s why PHP PostgreSQL UPDATE is a handy statement you’ll want to use on a regular basis. To summarize, an overview of what the code does, and what you are to do to execute it, can be broken down into these three steps: (1) connect to your database using pg_connect, (2) use pg_query command and UPDATE statement to specify the conditions, namely the values and corresponding keys, and finally (3), confirm the results. Simplicity is golden.

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.