Create a File PHP in Postgres to Insert in Web App
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 |
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 |
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 |
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:
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