Today, I am writing about how to build an application with PHP and PostgreSQL. This article assumes you have already installed PHP on your PC or MacBook, and that you know the basics of PHP, At least you built a PHP application with MYSQL.
What is PHP
PHP (recursive acronym for PHP: Hypertext Preprocessor) is a widely-used open-source general-purpose scripting language that is especially suited for web development and can be embedded into HTML.
I assumed that you are already familiar with PHP syntax and that you have written your first PHP program. if not, I recommend you take an introduction to PHP course on youtube.
What is PostgreSQL
PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California.
How to install PostgreSQL
To install PostgreSQL on your PC or MacBook follow all the instructions in This Article depending on your local machine. if you are on a windows machine another easy way to install PostgreSQL is to install WAPP (Windows, Apache, PostgreSQL, PHP) stack. BITNAMI is a software package that offers a WAPP package. You can download it from “http://bitnami.org/learn_more/installers" and by double clicking the installer file, it is easily installed on Windows. While installing, it asks you where to install it and also asks for a password which will later be required to login into the phpPgAdmin.
Using PostgreSQL with PHP
In this article, am going to show you have to use PostgreSQL with PHP in a simple example. I am going to create a simple web application. I am going to connect to PostgreSQL using PHP native functions
PHP provides many functions for working directly with PostgreSQL databases. Here are some functions :
pg_connect: The function is used to open a PostgreSQL connection.
Version: (PHP 4, PHP 5)
Syntax :
pg_connect ( string $connection_string [, int $connect_type ] );
pg_query: The function is used to execute a query
Version: (PHP 4 >= 4.2.0, PHP 5)
Syntax :
pg_query ([ resource $connection ], string $query )
Insert data into the table with PHP
I will create an HTML form and a PHP script to insert data into the “book” table (assuming we have a database and table named book). Here is the code (file name insert.php),
<!DOCTYPE html>
<head>
<title>
Insert data to PostgreSQL with php - creating a simple web application
</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style>
li {listt-style: none;}
</style>
</head>
<body>
<h2>Enter information regarding book</h2>
<ul>
<form name="insert" action="insert.php" method="POST" >
<li>Book ID:</li>
<li><input type="text" name="bookid" /></li>
<li>Book Name:</li>
<li><input type="text" name="book_name" /></li>
<li>Author:</li>
<li><input type="text" name="author" /></li>
<li>Publisher:</li>
<li><input type="text" name="publisher" /></li>
<li>Date of publication:</li>
<li><input type="text" name="dop" /></li>
<li>Price (USD):</li>
<li><input type="text" name="price" /></li>
<li><input type="submit" /></li>
</form>
</ul>
</body>
</html>
<?php
$db = pg_connect("host=localhost port=5432 dbname=postgres user=postgres password=admin123");
$query = "INSERT INTO book VALUES ('$_POST[bookid]','$_POST[book_name]', '$_POST[author]','$_POST[publisher]','$_POST[dop]', '$_POST[price]')";
$result = pg_query($query);
?>
Use this form to enter some data into the “book” table.
Retrieving and updating data with PHP
In the next step, I will create a form so that we can see the detail of book records and update the existing data of the “book” table. For this, we will create a form where the user can supply the book id and it will show all the information stored in the database regarding that particular book. So, you will learn how to fetch data from the table, how to display that data with PHP, and how to update the data.
Here is the code (file name enter-bookid):
<!DOCTYPE html>
<head>
<title>
Enter bookid to display data - creating a simple web application
</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style>
li {list-style: none;}
</style>
</head>
<body>
<h2>Enter bookid and enter</h2>
<ul>
<form name="display" action="enter-bookid.php" method="POST" >
<li>Book ID:</li>
<li><input type="text" name="bookid" /></li>
<li><input type="submit" name="submit" /></li>
</form>
</ul>
</body>
</html>
<?php
$db = pg_connect("host=localhost port=5432 dbname=postgres
user=postgres password=admin123");
$result = pg_query($db, "SELECT * FROM book where book_id =
'$_POST[bookid]'");
$row = pg_fetch_assoc($result);
if (isset($_POST['submit']))
{
echo "<ul>
<form name='update' action='enter-bookid.php' method='POST' >
<li>Book ID:</li>
<li><input type='text' name='bookid_updated'
value='$row[book_id]' /></li>
<li>Book Name:</li>
<li><input type='text' name='book_name_updated'
value='$row[book_name]' /></li>
<li>Author:</li>
<li><input type='text' name='author_updated'
value='$row[author]' /></li>
<li>Publisher:</li>
<li><input type='text' name='publisher_updated'
value='$row[publisher]' /></li>
<li>Date of publication:</li>
<li><input type='text' name='dop_updated'
value='$row[date_of_publication]' /></li>
<li>Price (USD):</li>
<li><input type='text' name='price_updated'
value='$row[price]' /></li>
<li><input type='submit' name='new' /></li>
</form>
</ul>";
}
if (isset($_POST['new'])) {
$result1 = pg_query($db, "UPDATE book SET book_id =
'$_POST[bookid_updated]', book_name = '$_POST[book_name_updated]',
author = '$_POST[author_updated]', publisher = '
$_POST[publisher_updated]',date_of_publication =
'$_POST[dop_updated]', price = '$_POST[price_updated]'");
if (!$result1){
echo "Update failed!!";
}else {
echo "Update successfull;";
}
}
?>
From this article, you have learned how to insert data from an HTML form and how to fetch, display and update data to PostgreSQL with PHP.
I recommend you take a detailed course in PostgreSQL with PHP, you can find some of them on Youtube.
Read more on this article on my medium page click here
follow me on twitter for more updates
Top comments (1)
“String interpolation of user-supplied data is extremely dangerous and is likely to lead to SQL injection vulnerabilities. In most cases pg_query_params() should be preferred, passing user-supplied values as parameters rather than substituting them into the query string.
Any user-supplied data substituted directly into a query string should be properly escaped.”
php.net/manual/en/function.pg-quer...