Display data from MYSQL database table using PHP and AJAX |filter by

AJAX stands for Asynchronous JavaScript and XML. AJAX is a technique for creating fast and dynamic web pages. AJAX is used to creating dynamic and fast website pages. AJAX is known as a technique through which you can create a kind of website. You can display (fetch) data from the MYSQL table using PHP and AJAX. AJAX helps you to fetch data from the MySQL table without reloading (refreshing) the PHP page. You can search for data, filter by date and edit data on the same page without refreshing (reloading) page. AJAX technique is a fast technique for the process of the data between the PHP page and MYSQL database table.AJAX helps to update parts of a web page, without reloading the whole page. Display the data from the MySQL table and filter by the days and month etc without reloading the page. If you do not want to use AJAX then you have to reload all PHP pages after the update, edit, delete. Most of the popular websites are using AJAX bellow - Google(Gmail, map ), Youtube, and Facebook tabs.

To display or fetch data from the MYSQL database table then you have to create a database and a table. Insert some data into the table.

Create MYSQL database table

Database name "technosmarter" table name "persons "



CREATE TABLE `persons` (
  `id` int(50) NOT NULL,
  `fname` varchar(40) NOT NULL,
  `lname` varchar(30) NOT NULL,
  `email` varchar(50) NOT NULL,
  `address` varchar(50) NOT NULL,
  `mobile` varchar(50) NOT NULL,
  `password` varchar(40) NOT NULL,
  `acno` varchar(40) NOT NULL,
  `bank_name` varchar(30) NOT NULL,
  `owner_name` varchar(30) NOT NULL,
  `ac_type` varchar(30) NOT NULL,
  `ifsc` varchar(50) NOT NULL,
  `name` blob NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We created a table for fetch data via PHP and Ajax. We have taken some table fields

- id` int(50) NOT NULL,

`fname` varchar(40) NOT NULL, = First Name (fetch by Ajax column 2)(Display)

`lname` varchar(30) NOT NULL, = Last Name (fetch by Ajax column 2)(Display)

`email` varchar(50) NOT NULL, = Email (fetch by Ajax column 3)(Display)

`address` varchar(50) NOT NULL, = Address (fetch by Ajax column 4)(Display)

`mobile` varchar(50) NOT NULL,= Mobile (fetch by Ajax column 5)(Display)

`password` varchar(40) NOT NULL,= Password (Not fetch )(Not Display)

`acno` varchar(40) NOT NULL, = Account Number (fetch by Ajax column 6)(Display)

`bank_name` varchar(30) NOT NULL, = Bank Name (fetch by Ajax column 6)(Display)

`owner_name` varchar(30) NOT NULL,= Accont Holder Name (fetch by Ajax column 6)(Display)

`ac_type` varchar(30) NOT NULL, = Account Type (fetch by Ajax column 6)(Display)

`ifsc` varchar(50) NOT NULL, = IFSC Code (fetch by Ajax column 6)(Display)

`name` blob NOT NULL, = Image Name(fetch by Ajax column 1)(Display)

`date` date NOT NULL = Date( fetch data Filter by date ) (Display)

You want to display the data from the database then you need to insert some data in the table. Insert some data in the table and fetch through PHP and Ajax.


INSERT INTO `persons` (`id`, `fname`, `lname`, `email`, `address`, `mobile`,
 `password`, `acno`, `bank_name`, `owner_name`, `ac_type`, `ifsc`,`date`) VALUES
(81, 'Mohan', 'Rana', '[email protected]', 'Shamli', '7812828887', 'admin@123', 
'739837927193', 'SBI', 'Mohan Rana', 'Saving ', 'SBI00875464',
 '2019-09-18');

In the above insertion, you can insert all data and fetch via ajax and display it on the webpage.

Make a connection file

When we work on MYSQL database ,we need a connection between MYSQL database and PHP script. The data is displayed through the PHP and AJAX on the page after database connectivity. Let's create a config file.

Create a table for display data by Ajax techniques

Now we will fetch data and display it in the table using Ajax. Create a simple table and columns name.


        
    
     
      
       
	   
         User Image
        Full Name 
        Email 
        Mobile
          Address 
		
		  Payment Details

In the above table, we have taken the columns.
User Image = User image (Idiplayed )(column 1)

Full Name = Fname+lname(diplayed )(column 2)

Email = Email Id (diplayed )(column 3)

Mobile = Mobile Number (diplayed )(column 4)

Address = Address(diplayed )(column 5)

Payment Details = Ac no + bank name+ifsc code +owner name (dsplayed)(column 6)

If you want to display the data without reloading the page then you have to create a script. This script will run the ajax on the same page. Create a Script for the AJAX.

In above js script we have create load_data() function . The load_data() function will help ton get the data usin AJAX techniques . We have defined a URL: fetch-details. fetch-details is another PHP script where all ajax techniques will write. We used load_data(no_of_days); to filter the data by the days.

Now we will create AJAX techniques to fetch the data, search data and filter by the date


<?php
//fetch.php
include("config.php");
$column = array("name","fname", "lname", "email", "address","mobile","acno","bank_name","owner_name","ac_type","ifsc");
$query = "
 SELECT * FROM persons WHERE 
";
if(isset($_POST["is_days"]))
{
 $query .= "date BETWEEN CURDATE() - INTERVAL ".$_POST["is_days"]." DAY AND CURDATE() AND ";
}
if(isset($_POST["search"]["value"]))
{
 $query .= '(fname LIKE "%'.$_POST["search"]["value"].'%" ';
  $query .= 'OR name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR lname LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR email LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR address LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR acno LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR bank_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR owner_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR ac_type LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR ifsc LIKE "%'.$_POST["search"]["value"].'%") ';
}
if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['7']['dir'].' ';
}
else
{
 $query .= 'ORDER BY id DESC ';
}
$query1 = '';

if($_POST["length"] != -1)
{
 $query1 .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$number_filter_row = mysqli_num_rows(mysqli_query($mysqli, $query));

$result = mysqli_query($mysqli, $query . $query1);

$data = array();

while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $sub_array[] = " ";
 $sub_array[] = $row["fname"].' '.$row["lname"];
$sub_array[] = $row["email"];
$sub_array[] = $row["mobile"];
$sub_array[] = $row["address"];
$sub_array[] = $row["acno"].'
'.$row["bank_name"].'
'.$row["owner_name"].'
'.$row["ac_type"].'
'.$row["ifsc"];
 $data[] = $sub_array;
}
function get_all_data($mysqli)
{
 $query = "SELECT * FROM persons";
 $result = mysqli_query($mysqli, $query);
 return mysqli_num_rows($result);
}
$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  get_all_data($mysqli),
 "recordsFiltered" => $number_filter_row,
 "data"    => $data
);
echo json_encode($output);
?>

In the above Ajax technique, we have to fetch the data, search the data by keyword and filter by N days. 1. First of all, we defined the table columns values. 2. We Performed the search operation. 3. Defined the 7 columns, 4. Fetch operation. 5. Filter by N days 6. Echo output through json_encode. Now you can run the code. Try to create yourself if you are not able to create then download the complete script.