Wednesday 16 June 2021

Ajax Pagination using PHP with JavaScript


Pagination in Web Application is one of the required feature for load data on web page, in which include load large number of data from database. The main benefit of Pagination is the load dynamic data faster because it has divided data loading process in multiple pages. This data loading functionality we can easily implement with the help of PHP script. In PHP Pagination which has load data by click on navigation links and when we have click on navigation link the it has reload page with dynamic data. Suppose you want to improve your web application user interface and if you want to make easy to use your web application data, then Ajax pagination is the best option because with the help of Ajax pagination it has load dynamic data without reloading whole page when we have click on pagination navigation link.

With the help of Ajax Pagination which will make a dynamic navigation links for pages and then after it has load data without reloading whole web page content. So by using Ajax Pagination we can add dynamic page content in the data list without refreshing the web page content by using Ajax and PHP. Under this tutorial you can find how to integrate Pagination into your website by using JavaScript with PHP script and MySQL database.

Advantages of Pagination


  • Pagination has separate the whole data into different page, so it has reduce load on fetch whole data from database.
  • By using Pagination we can easily go to any number of page data by directly click on navigation link.
  • In pagination we can directly go to last records or first record by directly click on First page or Last page navigation link.

In this tutorial, we have make simple script in which we have fetch dynamic data from a MySQL database with Pagination dynamic navigation links with previous and next link. In this tutorial, we will use pure javaScript function which will send Ajax request to PHP script for fetch data from database and then after in PHP script we will also write PHP script for create pagination link with next and previous button link. So this page link will used for fetch a number of data from database without reload page using JavaScript and Ajax.


Ajax Pagination using PHP with JavaScript







Create Database Table



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `post`
--

CREATE TABLE `post` (
  `id` mediumint(8) UNSIGNED NOT NULL,
  `post_title` text,
  `post_description` text,
  `author` varchar(255) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `post_image` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `post`
--
ALTER TABLE `post`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `post`
--
ALTER TABLE `post`
  MODIFY `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT;


Create HTML Page for Display Data from MySQL Database



<!--index.html-->

<!DOCTYPE HTML>
<html>
<head>
	<meta charset="utf-8" />
	<title>Ajax Pagination using JavaScript with PHP and MySQL</title>
	<meta name="viewport" content="width=device-width, initial-scale=1" />
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
</head>
<body>
    <div class="container">
    	<h2 class="text-center mt-4 mb-4">Ajax Pagination using JavaScript with PHP and MySQL</h2>
    	
    	<div class="card">
    		<div class="card-header">
    			<div class="row">
    				<div class="col-md-6">Sample Data</div>
    				<div class="col-md-3 text-right"><b>Total Data - <span id="total_data"></span></b></div>
    				<div class="col-md-3">
    					<input type="text" name="search" class="form-control" id="search" placeholder="Search Here" onkeyup="load_data(this.value);" />
    				</div>
    			</div>
    		</div>
    		<div class="card-body">
    			<table class="table table-bordered">
    				<thead>
    					<tr>
    						<th width="5%">#</th>
    						<th width="35%">Post Title</th>
    						<th width="60%">Description</th>
    					</tr>
    				</thead>
    				<tbody id="post_data"></tbody>
    			</table>
    			<div id="pagination_link"></div>
    		</div>
    	</div>
    	
    </div>
</body>
</html>


JavaScript & Ajax Code for Retrive Data and Pagination



function load_data(query = '', page_number = 1)
{
	var form_data = new FormData();

	form_data.append('query', query);

	form_data.append('page', page_number);

	var ajax_request = new XMLHttpRequest();

	ajax_request.open('POST', 'process_data.php');

	ajax_request.send(form_data);

	ajax_request.onreadystatechange = function()
	{
		if(ajax_request.readyState == 4 && ajax_request.status == 200)
		{
			var response = JSON.parse(ajax_request.responseText);

			var html = '';

			var serial_no = 1;

			if(response.data.length > 0)
			{
				for(var count = 0; count < response.data.length; count++)
				{
					html += '<tr>';
					html += '<td>'+response.data[count].post_id+'</td>';
					html += '<td>'+response.data[count].post_title+'</td>';
					html += '<td>'+response.data[count].post_description+'</td>';
					html += '</tr>';
					serial_no++;
				}
			}
			else
			{
				html += '<tr><td colspan="3" class="text-center">No Data Found</td></tr>';
			}

			document.getElementById('post_data').innerHTML = html;

			document.getElementById('total_data').innerHTML = response.total_data;

			document.getElementById('pagination_link').innerHTML = response.pagination;

		}

	}
}





PHP Script for Fetch Data & Pagination



<?php

//process_data.php

if(isset($_POST["query"]))
{

	$connect = new PDO("mysql:host=localhost; dbname=testing", "root", "");

	$data = array();

	$limit = 5;

	$page = 1;

	if($_POST["page"] > 1)
	{
		$start = (($_POST["page"] - 1) * $limit);

		$page = $_POST["page"];
	}
	else
	{
		$start = 0;
	}

	if($_POST["query"] != '')
	{

		$condition = preg_replace('/[^A-Za-z0-9\- ]/', '', $_POST["query"]);

		$condition = trim($condition);

		$condition = str_replace(" ", "%", $condition);

		$sample_data = array(
			':post_title'			=>	'%' . $condition . '%',
			':post_description'		=>	'%'	. $condition . '%'
		);

		$query = "
		SELECT id, post_title, post_description 
		FROM post 
		WHERE post_title LIKE :post_title 
		OR post_description LIKE :post_description 
		ORDER BY id DESC
		";

		$filter_query = $query . ' LIMIT ' . $start . ', ' . $limit . '';

		$statement = $connect->prepare($query);

		$statement->execute($sample_data);

		$total_data = $statement->rowCount();

		$statement = $connect->prepare($filter_query);

		$statement->execute($sample_data);

		$result = $statement->fetchAll();

		$replace_array_1 = explode('%', $condition);

		foreach($replace_array_1 as $row_data)
		{
			$replace_array_2[] = '<span style="background-color:#'.rand(100000, 999999).'; color:#fff">'.$row_data.'</span>';
		}

		foreach($result as $row)
		{
			$data[] = array(
				'post_id'			=>	$row["id"],
				'post_title'		=>	str_ireplace($replace_array_1, $replace_array_2, $row["post_title"]),
				'post_description'	=>	str_ireplace($replace_array_1, $replace_array_2, $row["post_description"])
			);
		}

	}
	else
	{

		$query = "
		SELECT id, post_title, post_description 
		FROM post 
		ORDER BY id DESC
		";

		$filter_query = $query . ' LIMIT ' . $start . ', ' . $limit . '';

		$statement = $connect->prepare($query);

		$statement->execute();

		$total_data = $statement->rowCount();

		$statement = $connect->prepare($filter_query);

		$statement->execute();

		$result = $statement->fetchAll();

		foreach($result as $row)
		{
			$data[] = array(
				'post_id'				=>	$row["id"],
				'post_title'			=>	$row['post_title'],
				'post_description'		=>	$row['post_description']
			);
		}

	}

	$pagination_html = '
	<div align="center">
  		<ul class="pagination">
	';

	$total_links = ceil($total_data/$limit);

	$previous_link = '';

	$next_link = '';

	$page_link = '';

	if($total_links > 4)
	{
		if($page < 5)
		{
			for($count = 1; $count <= 5; $count++)
			{
				$page_array[] = $count;
			}
			$page_array[] = '...';
			$page_array[] = $total_links;
		}
		else
		{
			$end_limit = $total_links - 5;

			if($page > $end_limit)
			{
				$page_array[] = 1;

				$page_array[] = '...';

				for($count = $end_limit; $count <= $total_links; $count++)
				{
					$page_array[] = $count;
				}
			}
			else
			{
				$page_array[] = 1;

				$page_array[] = '...';

				for($count = $page - 1; $count <= $page + 1; $count++)
				{
					$page_array[] = $count;
				}

				$page_array[] = '...';

				$page_array[] = $total_links;
			}
		}
	}
	else
	{
		for($count = 1; $count <= $total_links; $count++)
		{
			$page_array[] = $count;
		}
	}

	for($count = 0; $count < count($page_array); $count++)
	{
		if($page == $page_array[$count])
		{
			$page_link .= '
			<li class="page-item active">
	      		<a class="page-link" href="#">'.$page_array[$count].' <span class="sr-only">(current)</span></a>
	    	</li>
			';

			$previous_id = $page_array[$count] - 1;

			if($previous_id > 0)
			{
				$previous_link = '<li class="page-item"><a class="page-link" href="javascript:load_data(`'.$_POST["query"].'`, '.$previous_id.')">Previous</a></li>';
			}
			else
			{
				$previous_link = '
				<li class="page-item disabled">
			        <a class="page-link" href="#">Previous</a>
			    </li>
				';
			}

			$next_id = $page_array[$count] + 1;

			if($next_id >= $total_links)
			{
				$next_link = '
				<li class="page-item disabled">
	        		<a class="page-link" href="#">Next</a>
	      		</li>
				';
			}
			else
			{
				$next_link = '
				<li class="page-item"><a class="page-link" href="javascript:load_data(`'.$_POST["query"].'`, '.$next_id.')">Next</a></li>
				';
			}

		}
		else
		{
			if($page_array[$count] == '...')
			{
				$page_link .= '
				<li class="page-item disabled">
	          		<a class="page-link" href="#">...</a>
	      		</li>
				';
			}
			else
			{
				$page_link .= '
				<li class="page-item">
					<a class="page-link" href="javascript:load_data(`'.$_POST["query"].'`, '.$page_array[$count].')">'.$page_array[$count].'</a>
				</li>
				';
			}
		}
	}

	$pagination_html .= $previous_link . $page_link . $next_link;


	$pagination_html .= '
		</ul>
	</div>
	';

	$output = array(
		'data'				=>	$data,
		'pagination'		=>	$pagination_html,
		'total_data'		=>	$total_data
	);

	echo json_encode($output);

}

?>


Conclusion


So Lastly, In this post, We have learned how to make Ajax Pagination in PHP & MySQL by using Pure Vanilla JavaScript.

If you want to get complete source with .sql file, so please write your email address in comment box. We will send you complete source code file at your define email address.











91 comments:

  1. Replies
    1. Hi, Yogendra Kumar thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  2. the complete source code, please

    ReplyDelete
    Replies
    1. Please share your email, we will send source code and .sql file at your email address.

      Delete
  3. Replies
    1. Hi, Alex thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  4. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  5. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  6. Replies
    1. Hi Rajesh Kumar thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  7. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  8. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  9. Thaks sir I got it..sir when online examination system next updated version come

    ReplyDelete
  10. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  11. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  12. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  13. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  14. durupatience.c@gmail.com
    Thank you

    ReplyDelete
    Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  15. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  16. nox.kelron@gmail.com

    Thanks

    ReplyDelete
  17. Hello i am Fahmi, fahmi.nsk@gmail.com from Indonesia. I like your article

    ReplyDelete
    Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  18. sandeepsingh@pau.edu

    ReplyDelete
    Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  19. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  20. Thank you for the knowledge provided

    ReplyDelete
  21. dinhlamhuytak489@gmail.com
    Thanks!

    ReplyDelete
    Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  22. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  23. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  24. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  25. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  26. hi can you send me source pleas thank's more
    nebios@live.com

    ReplyDelete
  27. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  28. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  29. thank you for tutoring my email larryluis20@gmail.com

    ReplyDelete
    Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  30. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  31. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  32. amir.bakhtiiarii@gmail.com

    ReplyDelete
    Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  33. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  34. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  35. thanks for your source code.

    ReplyDelete
  36. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  37. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  38. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  39. Replies
    1. Hi thanks for sharing your email, we have recently send you source code file at your email address, so please check your email and confirm here, have you received source code file or not.

      Delete
  40. Hello, thanks for the tutorial. Definitely this isn't the first time you're saving me with lots of work in my backend...On internet out there there isn't anything like pagination with data that doesn't involve datatables...everyone is doing with datatables and yet here you are with a perfect exception...not my first time getting the exact code I was looking for on this site...some time back I got concept of unique slug from here and sure it worked magically. You're really doing a great work here and I wouldn't hesitate on sharing this site with my friends.
    I think you just need to focus on the requests people are making and find solutions for them...me personally I'm satisfied with this great work...awesome article.
    The only issue I'm requesting is that is there a way of preventing the getData.php from being opened manually...I mean I could do a redirect if there was a POST request to the file so I prevent accessing the RAW data directly if not on index.php. I hope my issue isn't confusing, besides that thanks for the great articles.

    ReplyDelete
  41. Hi, thanks for the project! but the source code isn't updated like the demo. There has search function error. Could you give me the source code of the link, please? https://demo.webslesson.info/javascript-live-data-search/process_data.php

    ReplyDelete
  42. Hi, thanks for the awesome project! We are getting an error when try to search something which not on the database. We should get "No Data Found" if the data not have on the database but We are getting "

    Warning: Undefined variable $page_array in E:\xampp\htdocs\everything\best-pagi\ajax-pagination-using-javascript-with-php-and-mysql\process_data.php on line 188


    Fatal error: Uncaught TypeError: count(): Argument #1 ($value) must be of type Countable|array, null given in E:\xampp\htdocs\everything\best-pagi\ajax-pagination-using-javascript-with-php-and-mysql\process_data.php:188"

    this error on the process_data.php file in the code line of "for($count = 0; $count < count($page_array); $count++)"

    Could you please give me the latest PHP code source of the demo PHP file which is absolutely fine on the demo? the php code link is: https://demo.webslesson.info/javascript-live-data-search/process_data.php

    Please send me the link source code to my gmail nn.nirob.nn@gmail.com

    ReplyDelete