In this article, we are going to build a CRUD PHP application using PostgreSQL as the Database. CRUD stands for Create, Read, Update, and Delete. CRUD operations are basic data manipulation for the database. INSERT query is used to insert data in the database. SELECT Query is used to select data from the database. UPDATE Query is used to update data in the database and Delete Query is used to delete data from the database.
Pre-Requisites
This article assumes you have already installed PHP on your PC or MacBook, you have also installed PostgreSQL, and that you know the basics of PHP, At least you built a PHP application, you know the basics of SQL, and you should have built a PHP application that uses an SQL database (eg Mysql).
To follow along and understand this article, a basic knowledge of PHP, working with SQL and PostgreSQL is required, you should have also setup PHP and PostgreSQL on your PC or MacBook, and finally, you should know how to interact with PostgreSQL using the CMD or terminal depending on your operating system.
Create a Database
To start building our CRUD application with PHP and PostgreSQL First, We have to create a database in PostgreSQL. To do that login to your PostgreSQL from the terminal or CMD and run this
create database users_dbhere this command creates a database called users_db in Postgres. you can also create the database from the UI, this depends on how you installed Postgres on your PC or MacBook.
Create a Table
Now that we have created our Database we can now create tables inside the database. so here we are going to create a users table. to create the table we going to run the following command in our terminal or CMD.
CREATE TABLE public."user"
(
id serial NOT NULL,
name character varying(250),
email character varying(250),
phone bigint,
address text,
PRIMARY KEY (id)
)here our table is called user, it has the following fields id, name, email, phone, and address. for this article, we are going to have only one table in our database.
Connecting to the database
To use PHP with PostgreSQL we need to connect to the database and we will do so, by creating a PHP file and calling it connection.php in our application root directory. inside the connection.php file, we will then have the following PHP code.
<?php
$host = "localhost";
$port = "5432";
$dbname = "users_db";
$user = "postgres";
$password = "password";
$connection_string = "host={$host} port={$port} dbname={$dbname}
user={$user} password={$password} "; $dbconn = pg_connect($connection_string);
?>Here we are connected to PostgreSQL using the PHP function pg_connect()
Please make sure you update the $dbname, $user, and $password with your own details.
Create the DB_class
Now that we have connected to our database, we have to create another PHP file to handle all the database queries. the db_class will have the following code.
<?php
include_once('connection.php'); class Db_Class
{
private $table_name = 'user'; function createUser()
{
$sql = "INSERT INTO PUBLIC.".$this->table_name."
(name,email,mobile_no,address) "."VALUES('".
$this->cleanData($_POST['name'])."','".
$this->cleanData($_POST['email'])."','".
$this->cleanData($_POST['phone'])."','".
$this->cleanData($_POST['address'])."')"; return pg_affected_rows(pg_query($sql));
}
function getUsers()
{
$sql ="select *from public." . $this->table_name . "
ORDER BY id DESC"; return pg_query($sql);
}
function getUserById(){
$sql ="select *from public." . $this->table_name . "
where id='".$this->cleanData($_POST['id'])."'"; return pg_query($sql);
}
function deleteuser()
{
$sql ="delete from public." . $this->table_name . "
where id='".$this->cleanData($_POST['id'])."'"; return pg_query($sql);
}
function updateUser($data=array())
{
$sql = "update public.user set name='".
$this->cleanData($_POST['name'])."',email='".
$this->cleanData($_POST['email'])."', phone='".
$this->cleanData($_POST['phone'])."',address='".
$this->cleanData($_POST['address'])."' where id = '".
$this->cleanData($_POST['id'])."' "; return pg_affected_rows(pg_query($sql));
}
function cleanData($val)
{
return pg_escape_string($val);
}
?>This file contains reusable PHP codes that will help us to interact with PostgreSQL.
Create Index.php file
The index page in PHP is the first page that we visit when we open a website on the browser. for this article, we create a simple index page. Create an index.php file and add the below PHP code.
<?php
include('header.php');
$users = $obj->getUsers();
$sn=1; if(isset($_POST['update']))
{
$user = $obj->getUserById();
$_SESSION['user'] = pg_fetch_object($user);
header('location:edit.php');
} if(isset($_POST['delete']))
{
$ret_val = $obj->deleteuser(); if($ret_val==1)
{
echo "<script language='javascript'>";
echo "alert('Record Deleted Successfully')
{
window.location.reload();
}"; echo "</script>";
}
}
?> <div class="container-fluid bg-3 text-center">
<h3>CRUD Example Using PHP OOPS And PostgreSQL</h3> <a href="insert.php" class="btn btn-primary pull-right"
style='margin-top:-30px'> <span class="glyphicon glyphicon-
plus-sign"></span>Add Record</a> <br>
<div class="panel panel-primary">
<div class="panel-heading">CRUD Example Using PHP OOPS And PostgreSQL</div>
<div class="panel-body">
<table class="table table-bordered table-striped">
<thead>
<tr class="active">
<th>Sr. No.</th>
<th >Name</th>
<th>Email</th>
<th>Mobile Number</th>
<th>Address</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php while($user = pg_fetch_object($users)): ?>
<tr align="left">
<td ><?=$sn++?></td>
<td><?= $user->name ?></td>
<td><?= $user->email ?></td>
<td><?= $user->phone ?></td>
<td><?= $user->address?></td>
<td>
<form method="post">
<input type="submit" class="btn btn-success" name= "update" value="Update">
<input type="submit" onClick="return confirm('Please confirm deletion');" class="btn btn-danger" name= "delete" value="Delete">
<input type="hidden" value="<?=$user->id?>" name="id">
</form>
</td>
</tr>
<?php endwhile; ?>
</tbody>
</table>
</div>
</div>
</div>
<?php include('footer.php');?>This index.php file contains the HTML code, we can also delete and update a user record from this index.php file. Next, we need to create the header.php, this is where all the headers of the application will load. Create a header.php file and add the below PHP code.
<?php
session_start();
require('db_class.php');
$obj = new Db_Class();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>PHP PostgreSQL OOPS CRUD Example</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<style>
/* Remove the navbar's default margin-bottom and rounded borders */
.navbar {
margin-bottom: 0;
border-radius: 0;
}
/* Add a gray background color and some padding to the footer */
footer {
background-color: #f2f2f2;
padding: 25px;
}
</style>
</head>
<body>
<nav class="navbar navbar-inverse">
<div class="container-fluid">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#myNavbar">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="#">PHP4EVER</a>
</div>
<div class="collapse navbar-collapse" id="myNavbar">
<ul class="nav navbar-nav">
<li class="active"><a href="#">Home</a></li>
</ul>
</div>
</div>
</nav>we are also going to create a footer.php which will contain the code for the footer on the application. Create a footer.php file and add the below PHP code.
<footer class="container-fluid text-center">
<p>PHPFOREVER © <?=date('Y')?></p>
</footer>
</body>
</html>Create an Insert.php file
Create an insert.php file and add the below PHP code.
<?php
include('header.php');
if(isset($_POST['submit']) and !empty($_POST['submit'])){
$ret_val = $obj->createUser();
if($ret_val==1){
echo '<script type="text/javascript">';
echo 'alert("Record Saved Successfully");';
echo 'window.location.href = "index.php";';
echo '</script>';
}
}
?>
<div class="container-fluid bg-3 text-center">
<h3>CRUD Example Using PHP OOPS And PostgreSQL</h3>
<a href="index.php" class="btn btn-primary pull-right" style='margin-top:-30px'><span class="glyphicon glyphicon-eye-open"></span> View Records</a>
<br>
<div class="panel panel-primary">
<div class="panel-heading">CRUD Example Using PHP OOPS And PostgreSQL</div>
<form class="form-horizontal" method="post">
<div class="panel-body">
<div class="form-group">
<label class="control-label col-sm-2">Name:<span style='color:red'>*</span></label>
<div class="col-sm-5">
<input class="form-control" type="text" name="name" required>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2">Email:<span style='color:red'>*</span></label>
<div class="col-sm-5">
<input class="form-control" type="email" name="email" required>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2">Mobile Number:<span style='color:red'>*</span></label>
<div class="col-sm-5">
<input class="form-control" type="number" name="phone" required>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2">Address:<span style='color:red'>*</span></label>
<div class="col-sm-5">
<textarea rows="5" cols="5" class="form-control" name="address" required></textarea>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2"> </label>
<div class="col-sm-5">
<input type="submit" class="btn btn-primary" name="submit" value="Submit">
</div>
</div>
</div>
</form>
</div>
</div>
<?php include('footer.php');?>The insert.php file is used to insert data into the PostgreSQL database, it contains HTML code and PHP code, used to submit the data into the PostgreSQL database.
Create Edit PHP class
Create an edit.php file and add the below PHP code.
<?php
include('header.php');
$user = $_SESSION['user'];
if(isset($_POST['update']) and !empty($_POST['update'])){
$ret_val = $obj->updateUser();
if($ret_val==1){
echo '<script type="text/javascript">';
echo 'alert("Record Updated Successfully");';
echo 'window.location.href = "index.php";';
echo '</script>';
}
}
?>
<div class="container-fluid bg-3 text-center">
<h3>CRUD Example Using PHP OOPS And PostgreSQL</h3>
<a href="index.php" class="btn btn-primary pull-right" style='margin-top:-30px'><span class="glyphicon glyphicon-step-backward"></span>Back</a>
<br>
<div class="panel panel-primary">
<div class="panel-heading">CRUD Example Using PHP OOPS And PostgreSQL</div>
<form class="form-horizontal" method="post">
<div class="panel-body">
<div class="form-group">
<label class="control-label col-sm-2">Name:<span style='color:red'>*</span></label>
<div class="col-sm-5">
<input class="form-control" value= "<?=$user->name?>"type="text" name="name" required>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2">Email:<span style='color:red'>*</span></label>
<div class="col-sm-5">
<input class="form-control" value= "<?=$user->email?>"type="email" name="email" required>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2">Mobile Number:<span style='color:red'>*</span></label>
<div class="col-sm-5">
<input class="form-control" value= "<?=$user->mobile_no?>"type="number" name="mobileno" required>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2">Address:<span style='color:red'>*</span></label>
<div class="col-sm-5">
<textarea rows="5" cols="5" class="form-control" name="address" required><?=$user->address?></textarea>
</div>
<input type="hidden" value="<?=$user->id?>" name="id">
</div>
<div class="form-group">
<label class="control-label col-sm-2"> </label>
<div class="col-sm-5">
<input type="submit" class="btn btn-success" name="update" value="Update">
</div>
</div>
</div>
</form>
</div>
</div>
<?php include('footer.php');?>The edit.php file contains HTML code and PHP code we are going to use the HTML form to update the user record on the database.
We have come to the end of this article, I hope you understood the basics of how to build an Application with PHP and PostgreSQL. To understand more about how to build an Application with PHP and PostgreSQL, recommend you search for more tutorials on PHP and PostgreSQL, some of them are available on Youtube