How To Create PHP CRUD Using OOPS With PostgreSQL.
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 a data from the database. UPDATE Query is used to update a data in the database and Delete Query is used to delete a data from the database.
Database applications.
| Operations | SQL | HTTP | Restful Web Service |
| Create | DELETE | POST | DELETE |
| Read | Select | GET | GET |
| DELETE | Update | PUT / POST | PUT |
| DELETE | Delete | DELETE | DELETE |
First, We have to create database in postgresql.
create database crud.
Now, Create a table in crud database.
CREATE TABLE public."user"
(
id serial NOT NULL,
name character varying(250),
email character varying(250),
mobile_no bigint,
address text,
PRIMARY KEY (id)
)
Create connection.php file and add below php code.
<?php
$host = "localhost";
$port = "5432";
$dbname = "demo";
$user = "postgres";
$password = "password";
$connection_string = "host={$host} port={$port} dbname={$dbname} user={$user} password={$password} ";
$dbconn = pg_connect($connection_string);
?>
Create db_class.php file and add below php 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['mobileno'])."','".$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'])."', mobile_no='".$this->cleanData($_POST['mobileno'])."',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);
}
?>
Create index.php file and add 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->mobile_no?></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');?>
Create header.php file and add 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>
Create insert.php file and add 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="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></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');?>
Create edit.php file and add 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');?>
Create footer.php file and add below php code.
<footer class="container-fluid text-center">
<p>PHPFOREVER © <?=date('Y')?></p>
</footer>
</body>
</html>
