close
CRUD (Create, Read, Update, and Delete) operations are used in the web application for the data manipulation in the database. There are four basic operations involved in the CRUD functionality that help to manage data with the database. We have already shared the tutorial to perform create (insert), read (select), update, and delete operations in PHP CRUD Operations with MySQL. In this tutorial, we will build PHP CRUD application with PostgreSQL server.
PostgreSQL also known as Postgres is a relational database management system (RDBMS). The PostgreSQL database is open-source and free to use. We will connect with the PostgreSQL Server from the PHP script to execute select, insert, update, and delete operations in the database. You can manipulate data in the PostgreSQL server from a PHP application with CRUD operations. We will use the PDO_PGSQL driver to access the PostgreSQL Server from the PHP script.
The following functionality will be implemented in this PHP CRUD with PostgreSQL script.
Before getting started to create a CRUD application with PostgreSQL and PHP, take a look at the file structure.
php_crud_with_postgresql/ ├── index.php ├── addEdit.php ├── userAction.php ├── dbConfig.php ├── bootstrap/ │ └── bootstrap.min.css └── css/ └── style.css
Before start building the PHP CRUD application with PostgreSQL database, you must have access to the PostgreSQL server or you must install the PostgreSQL server on your system.
The PDO_PGSQL driver is required to enable access from PHP to PostgreSQL databases. If the PDO_PGSQL extension has not already been enabled in your PHP server, you can enable it in the PHP configuration file (php.ini).
In the php.ini file, uncomment the following line in php.ini by removing the ;.
;extension=pdo_pgsql
Once done, restart the server to make effect the changes. Now pdo_pgsql extension will be available in the PHP server.
Before getting started to build a CRUD application with PHP and PostgreSQL, a table is required in the database.
You can create databases and tables using pgAdmin UI or SQL Shell (psql):
If you don’t have any database in the PostgreSQL server, create a new database with the following command. It will create a database named codexworld_db in the PostgreSQL server.
CREATE DATABASE codexworld_db;
Now, create a table inside the database to store data in PostgreSQL. The following command creates a members table with some basic fields in the PostgreSQL database server.
CREATE TABLE members (
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
first_name varchar(50) DEFAULT NULL,
last_name varchar(50) DEFAULT NULL,
email varchar(255) NOT NULL,
country varchar(255) DEFAULT NULL,
created timestamp DEFAULT current_timestamp,
status smallint NULL DEFAULT '1'
);
The dbConfig.php file is used to create a connection with the PostgreSQL database using the PDO class. Specify the host ($serverName), dbname ($dbName), username ($dbUsername), and password ($dbPassword) as per the PostgreSQL server credentials.
<?php
// PostgreSQL server configuration
$serverName = "localhost";
$dbUsername = "postgres";
$dbPassword = "root";
$dbName = "codexworld_db";
// Create database connection
try {
$conn = new PDO("pgsql:host=$serverName;dbname=$dbName", $dbUsername, $dbPassword);
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch( PDOException $e ) {
die( "Error connecting to PostgreSQL Server: ".$e->getMessage() );
}
?>
The userAction.php file performs the CRUD operations using PHP and PostgreSQL database server. The code blocks are executed based on the requested action.
Add/Edit Record:
When the add/edit form is submitted and userSubmit parameter exists in the $_POST method, the pointer is entered in this code block.
Delete Records:
If delete is requested in action_type, perform DELETE Query to remove data from the PostgreSQL server based on the id passed in the query string.
<?php
// Start session
if(!session_id()){
session_start();
}
// Include database configuration file
require_once 'dbConfig.php';
// Set default redirect url
$redirectURL = 'index.php';
if(isset($_POST['userSubmit'])){
// Get form fields value
$id = $_POST['id'];
$first_name = trim(strip_tags($_POST['first_name']));
$last_name = trim(strip_tags($_POST['last_name']));
$email = trim(strip_tags($_POST['email']));
$country = trim(strip_tags($_POST['country']));
$status = $_POST['status'];
// Store the submitted field values in the session
$sessData['postData'] = $_POST;
$id_str = '';
if(!empty($id)){
$id_str = '?id='.$id;
}
// Fields validation
$errorMsg = '';
if(empty($first_name)){
$errorMsg .= 'Please enter your first name.<br>';
}
if(empty($last_name)){
$errorMsg .= 'Please enter your last name.<br>';
}
if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){
$errorMsg .= 'Please enter a valid email.<br>';
}
if(empty($country)){
$errorMsg .= 'Please enter country name.<br>';
}
// Process the form data
if(empty($errorMsg)){
if(!empty($id)){
// Update data in PostgreSQL server
$sql = "UPDATE members SET first_name = ?, last_name = ?, email = ?, country = ?, status = ? WHERE id = ?";
$query = $conn->prepare($sql);
$update = $query->execute(array($first_name, $last_name, $email, $country, $status, $id));
if($update){
$sessData['status']['type'] = 'success';
$sessData['status']['msg'] = 'Member data has been updated successfully.';
// Remove submitted field values from session
unset($sessData['postData']);
}else{
$sessData['status']['type'] = 'error';
$sessData['status']['msg'] = 'Something went wrong, please try again!';
// Set redirect url
$redirectURL = 'addEdit.php'.$id_str;
}
}else{
// Insert data in PostgreSQL server
$sql = "INSERT INTO members (first_name, last_name, email, country, status, created) VALUES (?,?,?,?,?,?)";
$params = array(
&$first_name,
&$last_name,
&$email,
&$country,
&$status,
date("Y-m-d H:i:s")
);
$query = $conn->prepare($sql);
$insert = $query->execute($params);
if($insert){
//$MemberID = $conn->lastInsertId();
$sessData['status']['type'] = 'success';
$sessData['status']['msg'] = 'Member data has been added successfully.';
// Remove submitted field values from session
unset($sessData['postData']);
}else{
$sessData['status']['type'] = 'error';
$sessData['status']['msg'] = 'Something went wrong, please try again!';
// Set redirect url
$redirectURL = 'addEdit.php'.$id_str;
}
}
}else{
$sessData['status']['type'] = 'error';
$sessData['status']['msg'] = 'Please fill all the mandatory fields:<br>'.trim($errorMsg, '<br>');
// Set redirect url
$redirectURL = 'addEdit.php'.$id_str;
}
// Store status into the session
$_SESSION['sessData'] = $sessData;
}elseif(($_REQUEST['action_type'] == 'delete') && !empty($_GET['id'])){
$id = $_GET['id'];
// Delete data from PostgreSQL server
$sql = "DELETE FROM members WHERE id = ?";
$query = $conn->prepare($sql);
$delete = $query->execute(array($id));
if($delete){
$sessData['status']['type'] = 'success';
$sessData['status']['msg'] = 'Member data has been deleted successfully.';
}else{
$sessData['status']['type'] = 'error';
$sessData['status']['msg'] = 'Something went wrong, please try again!';
}
// Store status into the session
$_SESSION['sessData'] = $sessData;
}
// Redirect to the respective page
header("Location:".$redirectURL);
exit();
?>
We will use the Bootstrap library to style the UI elements – data list table, form, buttons, and links. If you want to use a custom style for these UI elements, you can omit to include the Bootstrap library.
Include the CSS file of the Bootstrap library.
<link rel="stylesheet" href="css/bootstrap.min.css">
In the index.php file, the records are retrieved from the PostgreSQL server using PHP and listed in a tabular format with Add, Edit, and Delete options.
members table using prepare(), execute(), and fetchAll() methods of the PDO class.addEdit.php page to perform the Create operation.addEdit.php page to perform the Update operation.userAction.php file with action_type=delete and id params. In the userAction.php file, the record is deleted from the PostgreSQL server based on the unique identifier (ID).<?php
// Start session
if(!session_id()){
session_start();
}
// Retrieve session data
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:'';
// Get status message from session
if(!empty($sessData['status']['msg'])){
$statusMsg = $sessData['status']['msg'];
$statusMsgType = $sessData['status']['type'];
$statusMsgType = ($statusMsgType == 'error')?'danger':$statusMsgType;
unset($_SESSION['sessData']['status']);
}
// Include database configuration file
require_once 'dbConfig.php';
// Fetch the data from PostgreSQL server
$sql = "SELECT * FROM members ORDER BY id DESC";
$query = $conn->prepare($sql);
$query->execute();
$members = $query->fetchAll(PDO::FETCH_ASSOC);
?>
<!-- Display status message -->
<?php if(!empty($statusMsg)){ ?>
<div class="col-xs-12">
<div class="alert alert-<?php echo $statusMsgType; ?>"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>
<div class="row">
<div class="col-md-12 head">
<h5>Members</h5>
<!-- Add link -->
<div class="float-end">
<a href="addEdit.php" class="btn btn-primary"><i class="plus"></i> New Member</a>
</div>
</div>
<!-- List the members -->
<table class="table table-striped table-bordered">
<thead class="table-dark">
<tr>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Country</th>
<th>Status</th>
<th>Created</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
if(!empty($members)){ $count = 0;
foreach($members as $row){ $count++;
?>
<tr>
<td><?php echo $count; ?></td>
<td><?php echo $row['first_name']; ?></td>
<td><?php echo $row['last_name']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['country']; ?></td>
<td><?php echo $row['status'] == 1?'<span class="badge text-bg-success">Active</span>':'<span class="badge text-bg-danger">Blocked</span>'; ?></td>
<td><?php echo $row['created']; ?></td>
<td>
<a href="addEdit.php?id=<?php echo $row['id']; ?>" class="btn btn-warning">edit</a>
<a href="userAction.php?action_type=delete&id=<?php echo $row['id']; ?>" class="btn btn-danger" onclick="return confirm('Are you sure to delete?');">delete</a>
</td>
</tr>
<?php } }else{ ?>
<tr><td colspan="8">No member(s) found...</td></tr>
<?php } ?>
</tbody>
</table>
</div>
The addEdit.php handles the create and update form functionality.
userAction.php file to insert/update the record in the PostgreSQL server.<?php
// Start session
if(!session_id()){
session_start();
}
// Retrieve session data
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:'';
// Get status message from session
if(!empty($sessData['status']['msg'])){
$statusMsg = $sessData['status']['msg'];
$statusMsgType = $sessData['status']['type'];
$statusMsgType = ($statusMsgType == 'error')?'danger':$statusMsgType;
unset($_SESSION['sessData']['status']);
}
// Get member data
$memberData = $postData = array();
if(!empty($_GET['id'])){
// Include database configuration file
require_once 'dbConfig.php';
// Fetch data from PostgreSQL server by row ID
$sql = "SELECT * FROM members WHERE id = ".$_GET['id'];
$query = $conn->prepare($sql);
$query->execute();
$memberData = $query->fetch(PDO::FETCH_ASSOC);
}
$postData = !empty($sessData['postData'])?$sessData['postData']:$memberData;
unset($_SESSION['sessData']['postData']);
$actionLabel = !empty($_GET['id'])?'Edit':'Add';
?>
<!-- Display status message -->
<?php if(!empty($statusMsg)){ ?>
<div class="col col-md-12">
<div class="alert alert-<?php echo $statusMsgType; ?>"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>
<!-- Add/Edit form fields -->
<div class="col col-md-12">
<form method="post" action="userAction.php">
<div class="mb-3">
<label class="form-label">First Name</label>
<input type="text" class="form-control" name="first_name" placeholder="Enter your first name" value="<?php echo !empty($postData['first_name'])?$postData['first_name']:''; ?>" required>
</div>
<div class="mb-3">
<label class="form-label">Last Name</label>
<input type="text" class="form-control" name="last_name" placeholder="Enter your last name" value="<?php echo !empty($postData['last_name'])?$postData['last_name']:''; ?>" required>
</div>
<div class="mb-3">
<label class="form-label">Email</label>
<input type="email" class="form-control" name="email" placeholder="Enter your email" value="<?php echo !empty($postData['email'])?$postData['email']:''; ?>" required>
</div>
<div class="mb-3">
<label class="form-label">Country</label>
<input type="text" class="form-control" name="country" placeholder="Enter country name" value="<?php echo !empty($postData['country'])?$postData['country']:''; ?>" required>
</div>
<div class="mb-3">
<label class="form-label">Status</label>
<div class="form-check">
<input class="form-check-input" type="radio" name="status" value="1" <?php echo !isset($postData['status']) || (!empty($postData['status']) && $postData['status'] == 1)?'checked':''; ?>>
<label class="form-check-label">Active</label>
</div>
<div class="form-check">
<input class="form-check-input" type="radio" name="status" value="0" <?php echo isset($postData['status']) && $postData['status'] == 0?'checked':''; ?>>
<label class="form-check-label">Block</label>
</div>
</div>
<input type="hidden" name="id" value="<?php echo !empty($memberData['id'])?$memberData['id']:''; ?>">
<input type="submit" name="userSubmit" class="btn btn-primary" value="Submit">
</form>
</div>
PHP CRUD Operations with Search and Pagination
This example script helps you to perform select/insert/update/delete options in the PostgreSQL database with PHP. Hope you understand how to use PostgreSQL as a database in PHP application. Use this CRUD script to list, view, add, edit, update, and delete functionality with PostgreSQL server using PHP. Not only MySQL and PostgreSQL, but you can also use MS SQL database in PHP applications.
Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request
