How to fetch records from PostgreSQL using jQuery AJAX PHP

When working with a database then retrieving data from the database is one of the basic requirements.

With the use of AJAX, you can fetch records without reloading the page.

In this tutorial, I show how you can fetch records from PostgreSQL using jQuery AJAX and PHP.

I am using users table in the example. It has the following structure.

CREATE TABLE users (
   id serial PRIMARY KEY,
   username varchar(60) NOT NULL,
   fullname varchar(80) NOT NULL,
   email varchar(80) NOT NULL
)

2. Configuration

Create a config.php for the database connection.

Completed Code

<?php

$host = "localhost";
$user = "postgres";
$password = "root";
$dbname = "tutorial";
$con = pg_connect("host=$host dbname=$dbname user=$user password=$password");

if (!$con) {
   die('Connection failed.');
}

3. HTML

Create a textbox to enter the user id and 2 buttons. 1st button to fetch record by user id and 2nd button to fetch all users list using jQuery AJAX.

Display fetched records using <table >.

Completed Code

<input type='text' id='search' name='search' placeholder='Enter userid 1-8'>
<input type='button' value='Search' id='but_search'>
<br/>
<input type='button' value='Fetch all records' id='but_fetchall'>

<!-- Table -->
<table border='1' id='userTable' style='border-collapse: collapse;'>
  <thead>
    <tr>
       <th>ID</th>
       <th>Username</th>
       <th>Name</th>
       <th>Email</th>
    </tr>
  </thead> 
  <tbody></tbody>
</table>

4. PHP

Create ajaxfile.php file to handle AJAX requests.

• If $request == ‘fetchall’ then fetch all records from the users table and initialize $response Array with fetched data.

Return $response Array in JSON format.

• If $response == ‘fetchbyid’ then read POST userid value and assign to $userid variable. Fetch a record from users table where $userid exists in id field. Initialize $response Array with fetched data.

Return $response Array in JSON format.

Completed Code

<?php

include "config.php";

$request = "";
if(isset($_POST['request'])){
  $request = $_POST['request'];
}

// Fetch all records
if($request == 'fetchall'){

  $query = "SELECT * FROM users";

  $result = pg_query($con, $query);

  $response = array();

  while ($row = pg_fetch_assoc($result) ){

     $id = $row['id'];
     $username = $row['username'];
     $fullname = $row['fullname'];
     $email = $row['email'];

     $response[] = array(
        "id" => $id,
        "username" => $username,
        "fullname" => $fullname,
        "email" => $email,
     );
  }

  echo json_encode($response);
  die;
}

// Fetch record by id
if($request == 'fetchbyid'){

  $userid = 0;
  if(isset($_POST['userid']) && is_numeric($_POST['userid']) ){
    $userid = $_POST['userid'];
  }

  $query = "SELECT * FROM users WHERE id=".$userid;
  $result = pg_query($con, $query);

  $response = array();
  if (pg_numrows($result) > 0) {

    $row = pg_fetch_assoc($result);

    $id = $row['id'];
    $username = $row['username'];
    $fullname = $row['fullname'];
    $email = $row['email'];

    $response[] = array(
      "id" => $id,
      "username" => $username,
      "fullname" => $fullname,
      "email" => $email,
    );
  }

  echo json_encode($response);
  die;
}

5. Script

If #but_fetchall is gets clicked then send AJAX POST request to 'ajaxfile.php', set dataType: 'json', pass request: 'fetchall' as data. On successful callback pass response to createRows() function to create table rows.

If #but_search is gets clicked then read value from the textbox and assign it to userid variable. Send AJAX POST request to 'ajaxfile.php', pass request: 'fetchbyid', userid: userid as data, set dataType: 'json'. On successful callback pass response to createRows() function to create table rows.

createRows() – Empty <table> <tbody>. If response length is greater than 0 then loop on the response and create new <tr > and append in #userTable tbody otherwise, append “No record found” <tr> in <tbody>.

Completed Code

$(document).ready(function(){

  // Fetch all records
  $('#but_fetchall').click(function(){

     // AJAX GET request
     $.ajax({
        url: 'ajaxfile.php',
        type: 'post',
        data: {request: 'fetchall'},
        dataType: 'json',
        success: function(response){

           createRows(response);

        }
     });
  });

  // Search by userid
  $('#but_search').click(function(){
     var userid = Number($('#search').val().trim());
     if(!isNaN(userid)){
        userid = Number(userid);
     }else{
        userid = 0;
     }
     if(userid > 0){

        // AJAX POST request
        $.ajax({
           url: 'ajaxfile.php',
           type: 'post',
           data: {request: 'fetchbyid', userid: userid},
           dataType: 'json',
           success: function(response){
              createRows(response);
           }
        });
     }

  });

});

// Create table rows
function createRows(response){
   var len = 0;
   $('#userTable tbody').empty(); // Empty <tbody>
   if(response != null){
     len = response.length;
   }

   if(len > 0){
     for(var i=0; i<len; i++){
        var id = response[i].id;
        var username = response[i].username;
        var fullname = response[i].fullname;
        var email = response[i].email;

        var tr_str = "<tr>" +
          "<td align='center'>" + id + "</td>" +
          "<td align='center'>" + username + "</td>" +
          "<td align='center'>" + fullname + "</td>" +
          "<td align='center'>" + email + "</td>" +
         "</tr>";

        $("#userTable tbody").append(tr_str);
     }
   }else{
     var tr_str = "<tr>" +
       "<td align='center' colspan='4'>No record found.</td>" +
      "</tr>";
 
     $("#userTable tbody").append(tr_str);
   }
}

6. Output

View Output

7. Conclusion

In the example, I showed how you can fetch all and specific records using jQuery AJAX. Use according to your requirement in your project.

You can also use GET in AJAX request instead of POST.