Create a File PHP in Postgres to Insert in Web App

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

Introduction

This PostgreSQL tutorial will explain how to create a HTML file with PHP in Postgres to insert in a web app. The INSERT INTO statement or a query is used to insert data into a table in Postgres, however, an additional function is required to run the query on a PHP file that will run the query on the web application. Here the PG_Query command is used to execute the query on the specified database connection using a HTML file containing HTML forms. This will result in data going straight into the database from the web application when it is submitted.

Prerequisites

  • PostgreSQL and PHP Apache must both be properly installed on the local system. This is required in order to execute the examples provided in this tutorial for creating a file with PHP in Postgres to insert in a web app. However, the Bitnami WAPP Stack can also be used as it includes both PHP and PostgreSQL.

  • A good knowledge of PHP and PostgreSQL is needed to understand the instruction in this tutorial.

PHP PostgreSQL insert example

First, establish a connection from the PostgreSQL database to the PHP file. Now specify the host, username, password and the specific database the records will be retrieved from in the PHP file.

This is demonstrated in the following example:

1
2
3
4
5
<?php
$hostname = "localhost";
$dbname = "db";
$username = "postgres";
$pass = "password";

Now create the connection using the following variable:

1
2
$db_conn = pg_connect(" host = $hostname dbname = $dbname user = $username password = $pass ");
?>

Once a connection has been established, create an HTML file containing HTML forms. This form will contain all of the text that will be inserted to the database. Following is an example of the HTML file and forms:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!DOCTYPE  html>
<html>
    <body>
        <h2>PostgreSQL Insert Forms</h2>

        <form  method="POST"  action="Insert.php">
            First name:<br>
            <input  type="text"  name="firstname">
            <br>
            Last name:<br>
            <input  type="text"  name="lastname">
            <br><br>
            <input  type="submit"  name="submit">
        </form>
    </body>
</html>

Notice there are two attributes in the above form tag. The first uses the “POST” attribute and sends the form data as an HTTP post transaction. The second is the “action” attribute that specifies where the data is sent.

PG_Query command

Now, in the PHP file, set up the following action so when data is submitted from the HTML file the data will go straight into the database:

1
2
3
4
5
6
7
8
9
10
if (isset($_POST['submit'])) {
    $fname = $_POST['firstname'];
    $lname = $_POST['lastname'];

    $query = pg_query($db_conn, "INSERT  INTO users(first_name, last_name) VALUES ('$fname','$lname');");
    if ( $query ) {
        echo  "Record Successfully Added!";
    }
}
?>

Insert Data

Now run the previously created HTML file, insert the input text and click the submit button. The results should resemble the following screenshot:

Screenshot of a web app using PHP and Postgres to insert a record

Navigating to the PHP file, where the query is stored, should result in a “Record Successfully Added!” message.

Select the table on the Database

Type the following code into the PostgreSQL terminal to confirm the record was successfully inserted into database table:

1
2
3
4
5
SELECT * FROM users;
 id | first_name | last_name
----+------------+-----------
  5 | Samuel     | Hardy
(1 ROW)

Conclusion

This tutorial explained how to create a file with PHP in Postgres to insert in a web app. The article first covered establishing a connection from the PostgreSQL database and provided a working example. The tutorial then explained the PHP PostgreSQL insert example, including creating an HTML file containing HTML forms and how to insert it into the database. The tutorial then covered the PG Query command, explained how to select the table in the database and how to confirm the record was successfully added. Remember that both PostgreSQL and PHP Apache are required to execute the functions covered in this tutorial. However, in lieu if these two programs, the Bitnami WAPP Stack includes both PHP and PostgreSQL.

Just the Code

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'])) {
    $fname = $_POST['firstname'];
    $lname = $_POST['lastname'];

    $query = pg_query($db_conn, "INSERT  INTO users(first_name, last_name) VALUES ('$fname','$lname');");
    if ( $query ) {
        echo  "Record Successfully Added!";
    }
}
?>

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.