Php

jQuery Ajax Add, Edit and Delete Using PHP and PostgreSQL- Part I

In the Previous PostgreSQL tutorial, We have learned about PostgreSQL connection with PHP and listing data using non ajax.

This tutorial help to create a listing with CRUD operation using PostgreSQL and Ajax jQuery, Which have a listing record, insert a record into PostgreSQL table, Update record into postgre database and delete record from PostgreSQL table.

This is the First part of the tutorial, So I will create a listing with insert record functionality. I am not sharing postgre connection and table creation in PostgreSQL.

You get information from my previous tutorial How to connect postgreSQL Database connection with PHP.

I will use the following files in this PostgreSQL and PHP tutorial,

  • index.php : This file is main entry file of PHP application.This file will have listing and CRUD HTML form.
  • response.php : This file will contain all action method to get and insert data into postgre database method.
  • libs/ : This folder will have all dependencies files.
  • libs/common.js : This file will have all jQuery ajax method to get data from server side PHP.

So, Lets start AJAX listing with insert record into postgreSQL using PHP. We have employee_name, employee_salary and employee_age column into employee table, also 'id' column as Serial Key into employee table.

HTML table AJAX listing Using PHP and PostgreSQL

Add an HTML layout into index.php file to add table listing.I am using bootstrap CSS framework, So create HTML table with bootstrap classes, Included all libs file into head section of index.php file.

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
  
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script src="libs/common.js"></script>

Added below HTML code into index.php for employee record binding with table.

<table id="employee_grid" class="table" width="100%" cellspacing="0">
  <thead>
    <tr>
     <th>Name</th>
     <th>Salary</th>
     <th>Age</th>
     <th>Action</th>
    </tr>
  </thead>
<tbody id="emp_body">
</tbody>
</table>

Create AJAX jQuery request into common.js file, This method will fetch employee data from postgreSQL database. We will create GET HTTP request and passed response.php as url param value.

$(document).ready(function(){
 /* Handling get employee */ function get_employee() {
  $.ajax({    
   type : 'GET',
   url  : 'response.php?action=list',
   success : function(response){
    response = JSON.parse(response);
    var tr;
         $('#emp_body').html('');
         $.each(response, function( index, emp ) {
      tr = $('');
              tr.append("" + emp.employee_name + "");
              tr.append("" + emp.employee_salary + "");
              tr.append("" + emp.employee_age + "");

              var action = "
<div class="btn-group" data-toggle="buttons">";
              action += "<a href="#" target="_blank" class="btn btn-warning btn-xs" data-toggle="modal" data-target="#edit_model">Edit</a>";
              action += "<a href="#" target="_blank" class="btn btn-danger btn-xs">Delete</a>";
              tr.append(action);
              $('#emp_body').append(tr);
    });
   }
  });
 }
 
 //initialize method on load
  function init() {
   get_employee();
  }
  init();
});
</div>

I am binding employee_name, employee_salary and employee_age column with 'td' element of HTML table, finally bind all rows with '#emp_body' tbody of table. We are using jQuery append() method to create HTML string of row HTML with data.

The url param 'response.php?action=list' stands for calling response.php file and passing 'action' variable with 'list' value.

We will create response.php file and added some logic to handle action param and call appropriate method as per passed action name.

include("connection.php");
 
$params = $_REQUEST;
$action = isset($params['action']) &amp;&amp; $params['action'] !='' ? $params['action'] : 'list';
$empCls = new Employee();
 
switch($action) {
 case 'list':
  $empCls->getEmployees();
 break;
 default:
 return;
}

class Employee {
  protected $conn;
  protected $data = array();
  function __construct() {

 $db = new dbObj();
 $connString =  $db->getConnstring();
    $this->conn = $connString;
  }
  
  function getEmployees() {
    $sql = "SELECT * FROM employee";
 $queryRecords = pg_query($this->conn, $sql) or die("error to fetch employees data");
 $data = pg_fetch_all($queryRecords);
 echo json_encode($data);
  }
}
?>

I used PHP switch loop to call method as per pass action params, if we will not pass action variable then we will get NULL values. We have created postgreSQL DB connection and passed db instance variable to class variable $this->conn.

Related Post

How to Insert Record into PostgreSQL using PHP and jQuery AJAX

in this php tutorial, we’ll learn how to add record into database table. We will insert record into PostgreSQL database employee table.The PHP postgreSQL libs providing many helpful method to communicate with PostgreSQL database.

We will create HTML code to add button for show modal bootstrap box.

<div class="well clearfix">
<div class="pull-right"><a class="btn btn-info action-btn" data-toggle="modal" data-target="#add_model">Add</a></div>
</div>

Create a notification div container on above of table, That will use to show error OR success message.

<div id="msg"></div>

Let’s create a Bootstrap modal Box that will show on click of add record button.

<div id="add_model" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header"><button class="close" type="button" data-dismiss="modal" aria-hidden="true">×</button>
<h4 class="modal-title">Add Employee</h4>
</div>
<div class="modal-body"><form id="frm_add" method="post"><input id="action" name="action" type="hidden" value="add">
<div class="form-group"><label class="control-label" for="name">Name:</label> <input id="employee_name" class="form-control" name="employee_name" type="text"></div>
<div class="form-group"><label class="control-label" for="salary">Salary:</label> <input id="employee_salary" class="form-control" name="employee_salary" type="text"></div>
<div class="form-group"><label class="control-label" for="salary">Age:</label> <input id="employee_age" class="form-control" name="employee_age" type="text"></div>
</form></div>
<div class="modal-footer"><button class="btn btn-default" type="button" data-dismiss="modal">Close</button> <button id="btn_add" class="btn btn-primary" type="button">Save</button></div>
</div>
</div>
</div>

We have created Bootstrap modal box with '#add_model' id, that used into 'Add' button to show Modal Box.We have created an input box to enter employee_name, employee_salary and employee_age of employee, The HTML input field name and id is the same as employee table column.

Added '#btn_add' button to call save ajax method to insert record into database.

We will create method to fire AJAX jquery method into common.js file,

$( "#btn_add" ).click(function() {
  ajaxAction('add');
});

We have bind "#btn_add" button with click event and calling ajaxAction() by passing action param 'add' value. We will create ajaxAction() method to into common.js file.

function ajaxAction(action) {
  data = $("#frm_"+action).serializeArray();
  $.ajax({
    type: "POST",  
    url: "response.php?action=add",  
    data: data,
    dataType: "json",       
    success: function(response)  
    {
   $('#msg').html('');
     if(response.status) {
      $('#'+action+'_model').modal('hide');
    $('#msg').html('<div class="alert alert-success">Successfully! added record</div>');
    get_employee();
     } else {
      $('#msg').html('<div class="alert alert-danger ">Error! to insert record</div>'); 
     }
   
    },
    error: function(jqXHR, textStatus, errorThrown) {
     $('#msg').html('<div class="alert alert-danger ">Error'+textStatus+'!'+errorThrown);
   }  
  });
 }
</div>

We are sending HTML from data into serialize manner to response.php file, We can use this method for Update record as well.We are show error and success message as per ajax call response.

We will add insert record option into Swtich() php loop, We will add below code into response.php file,

case 'add':
 $empCls->insertEmployee();
 break;

We will create 'insertEmployee' record into response.php file.The look like as below,

function insertEmployee() {
 $data = $resp = array();
 $resp['status'] = false;
 $data['employee_name'] = $_POST["employee_name"];
 $data['employee_salary'] = $_POST["employee_salary"];
 $data['employee_age'] = $_POST["employee_age"];
 
 $result = pg_insert($this->conn, 'employee' , $data) or die("error to insert employee data");
 
 
 $resp['status'] = true;
 $resp['Record'] = $data;
 echo json_encode($resp);  // send data as json format*/ 
}

in the above code, we are using pg_insert() method to insert record into PostgreSQL database, The data format would be associative array. You can also use pg_query method to run an insert query to add a record but I had used pg_insert() method, It looks like you are using ORM to handle add data into the database.

Next tutorial will have edit and delete record using AJAX,php and PostgreSQl database.

Recent Posts

Configure and Retrieve S3 Information Using Laravel PHP-AWS-SDK

This tutorial helps integrate a PHP SDK with Laravel. We'll install aws-php-sdk into laravel application and access all aws services… Read More

2 months ago

What is the Purpose of php_eol in PHP?

in this quick PHP tutorial, We'll discuss php_eol with examples. PHP_EOL is a predefined constant in PHP and represents an… Read More

8 months ago

Laravel Table Relationship Methods With Example

This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More

8 months ago

Exploring the Power of Laravel Eloquent Join?

We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More

9 months ago

Quick and Easy Installation of Laravel Valet

in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More

9 months ago

What is Laravel Soft Delete and How Does it Work?

I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More

9 months ago

Categories