How to Use the pg_query in PHP

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

Introduction

When you want to store, modify, or retrieve data in your PostgreSQL database, pg_query in PHP can be quite useful. It’s fast because after you connect to the database, you’ll perform a few more simple actions. You can then instantly run your query. Test it out by following the steps in this tutorial about how to use the PHP pgquery command.

Prerequisites

Get the downloads for the following:

>NOTE: If you would rather install PostgreSQL and Apache PHP at the same time, get the Bitnami WAPP Stack on your OS.

  • You should have some experience using commands and statements in PHP and PostgreSQL.

The pg_query syntax

  • Here’s a short example of what the pgquery in PHP looks like:
1
2
3
<?php
pg_query( [connection], [query] );
?>

We mentioned in the introduction of this tutorial that the pgquery makes use of just two parameters.

  • The PostgreSQL database that you specify to connect to is the connection parameter.

  • The SQL statement is the query parameter.

The ‘pg_query’ example

  • First, create a database connection.

>NOTE: If you don’t specify a database for which to connect in the parameters of the command, it defaults to the most recent previous connection. It will use the one completed by pg_connect or the PostgreSQL persistent connection, pg_pconnect. Therefore, specify your database to eliminate the chance of connecting to the wrong one.

1
2
3
4
5
6
7
8
// WRITE the credentials OF the Postgres DATABASE.
$hostname = "localhost";
$dbname = "db";
$username = "postgres";
$pass = "password";

// CREATE the connection TO the PostgreSQL
$db_conn = pg_connect(" host = $hostname dbname = $dbname user = $username password = $pass ");
  • Next, make a PostgreSQL database table and for the column data values. Name the table student.
1
2
3
4
5
6
7
8
9
10
<?php
echo "<html>
    <body>
    <table>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Gender</th>
            <th>Grade</th>
        </tr>"
;
  • Finish the pgquery in PHP command by using the SELECT statement inside the pgquery parameters. Have it to indicate the table named student. Fetch it as shown here to make the connection:
1
2
3
4
5
6
7
8
9
10
11
$result = pg_query($db_conn, " SELECT  *  FROM student;");
while ($row = pg_fetch_assoc($result) ){
echo   "<tr>
            <td>"
.$row['id']."</td>
            <td>"
.$row['name']."</td>
            <td>"
.$row['gender']."</td>
            <td>"
.$row['grade']."</td>
        </tr>"
;
}
echo  "</table></body></htmml>";
?>
  • When you’re done, save the PHP file.

Screenshot of a web app using pg_query in PHP to select records

You can use pgquery to update data stored in a database too.

  • To begin, duplicate this sample script:
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>
  • Construct a PHP file like this:
1
2
3
4
5
6
7
8
9
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!";
    }
}
  • Go to your browser and add data to the form fields as shown in the image below.

  • Click the button “Submit” when you’re done. “Record Successfully Updated!” should display.

Screenshot of a web app using pg_query in PHP to update records

  • In the database table, confirm that the changes took effect.
1
2
3
4
SELECT * FROM student WHERE id = '2';
 id |      name       | gender | grade
----+-----------------+--------+-------
  2 | Eliza Herring   | Female |    94

As you can see, everything turned out as it should have. The id key of “2” and its corresponding grade column shows a value of 94. It was successfully updated from a previous grade of 96.

You can do much more with pgquery. For example, if you want to insert new data using PHP, it’s a breeze to accomplish.

  • Mimic this sample script below:
1
2
3
4
5
6
<?php
$query = pg_query($db_conn, "INSERT  INTO student VALUES ('11','Tania Schwartz','Female','91');");
if ( $query ) {
    echo  "Record Successfully Added!";
}
?>

>NOTE: As stated earlier, it’s important to remember to connect to a specified database where your table is located before you run pgquery. It’s worth repeating because the updating process will fail to produce the results you want if you skip the database connection step.

  • Next, go to your browser and execute the query.

  • This example of the successful pgquery result is what you should see:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT * FROM student;
 id |      name       | gender | grade
----+-----------------+--------+-------
  7 | George Welch    | Male   |    90
  6 | Joe Green       | Male   |    90
  3 | Darren Ferguson | Male   |    87
  4 | Artur Cisneros  | Male   |    89
  5 | Brittany Hodges | Female |    95
  8 | Anthony Mann    | Male   |    89
  9 | Michele Morgan  | Female |    88
 10 | Alycia Seaton   | Female |    91
  1 | Paulina Bright  | Female |    94
  2 | Eliza Herring   | Female |    94
 11 | Tania Schwartz  | Female |    91
(11 ROWS)

Shown above, instead of 10 rows, you now have 11 because you added a new record to the database table, student.

Managing database table data involves removing records that you no longer need.

  • Use this script to try out using pgquery in PHP to deleting a record in the student table in your sample database. For this example, we’re pretending there were 12 records instead of 11. Use the code below or modify your sample and select another id key value such as 11 (since there are actually 11 records in the sample), for the WHERE id.
1
2
3
4
5
6
<?php
$query = pg_query($db_conn, "DELETE FROM student WHERE id = '12';");
if ( $query ) {
    echo  "Record Successfully Deleted!";
}
?>
  • Run the PHP file from your browser.

  • If you see the “Record Successfully Deleted!” result, that’s a good sign. The record should not be there anymore.

  • Verify it by checking your database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT * FROM student ORDER BY id;
 id |      name       | gender | grade
----+-----------------+--------+-------
  1 | Paulina Bright  | Female |   100
  2 | Eliza Herring   | Female |   100
  3 | Darren Ferguson | Male   |
  4 | Artur Cisneros  | Male   |    89
  5 | Brittany Hodges | Female |   100
  6 | Joe Green       | Male   |    90
  7 | George Welch    | Male   |    90
  8 | Anthony Mann    | Male   |    89
  9 | Michele Morgan  | Female |    88
 10 | Alycia Seaton   | Female |   100
 11 | Tania Schwartz  | Female |   100
(11 ROWS)

The corresponding id key “12” was removed. The update was a success.

Conclusion

Whether you need to add, delete, or modify data, pgquery in PHP is how to do it most expeditiously. With the simplicity of the two parameters–connection, and query–pgquery is void of the unnecessary coding complexities. You can get more updating tasks done. Start streamlining your PostgreSQL database table updating tasks today.

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.