How to build an Application with PHP and PostgreSQL
Tags: php, postgres, sql, beginners
Published: 2022-10-24 19:13:03

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](https://medium.com/@dan.chiniara/installing-postgresql-for-windows-7ec8145698e3) depending on your local machine. if you are on a windows machine another easy way to install PostgreSQL is to install [WAPP](https://bitnami.com/stack/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](https://medium.com/@officialozioma/how-to-build-an-application-with-php-and-postgresql-1a5d2b590ea7)

follow me on [twitter](https://twitter.com/OfficialOzioma) for more updates