Ajax Fetch records by php/mysql with Pagination ?
36 Votes, Rating: 4.44
How to fetch records by ajax/php/mysql with pagination ?
Some time i think why i spare my time to add my codes here for all, but really im telling from my heart i want any other guy not waste his/her time to search about these simple things as i did. I juat want to save other time and time is money 
so in sort im saving your money guy’s 
If you like my post please leave comments, subscribe rss feed
How to Ajax fetch records by php mysql?
Here is the very simple example to fetch records from mysql by php and ajax. In this post i will show you how to make a list without refresh a page. To develop this type of program you just need 2 pages 1 for display list (we can it frontend) and 2nd for coding (call backend ).
we will send request to backend from frontend by ajax and ajax create list.
ajax listing page
<script> window.onload = function() { SANAjax('Listing','1'); }; ////////////////////// AJAX var HttPRequest = false; function SANAjax(Mode,Page) { HttPRequest = false; if (window.XMLHttpRequest) { // Mozilla, Safari,... HttPRequest = new XMLHttpRequest(); if (HttPRequest.overrideMimeType) { HttPRequest.overrideMimeType('text/html'); } } else if (window.ActiveXObject) { // IE try { HttPRequest = new ActiveXObject("Msxml2.XMLHTTP"); } catch (e) { try { HttPRequest = new ActiveXObject("Microsoft.XMLHTTP"); } catch (e) {} } } if (!HttPRequest) { alert('Cannot create XMLHTTP instance'); return false; } var url = 'SANajax.php'; var pmeters = 'mode='+Mode+'&Page='+Page; HttPRequest.open('POST',url,true); HttPRequest.setRequestHeader("Content-type", "application/x-www-form-urlencoded"); HttPRequest.setRequestHeader("Content-length", pmeters.length); HttPRequest.setRequestHeader("Connection", "close"); HttPRequest.send(pmeters); HttPRequest.onreadystatechange = function() { if(HttPRequest.readyState == 3) // Loading Request { document.getElementById("listingAJAX").innerHTML = '<img src="loader.gif" align="center" />'; } if(HttPRequest.readyState == 4) // Return Request { var response = HttPRequest.responseText; document.getElementById("listingAJAX").innerHTML = response; } } }</script> <p> <div id="listingAJAX"></div> </p>
Add this div on your page where you want to display all list
Backend page ( SANAjax.php)
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'test';
mysql_select_db($dbname);
############# must create your db base connection
$strPage = $_REQUEST[Page];
if($_REQUEST[mode]=="Listing"){
$query = "SELECT * FROM user";
$result = mysql_query($query) or die(mysql_error());
$Num_Rows = mysql_num_rows ($result);
########### pagins
$Per_Page = 5; // Records Per Page
$Page = $strPage;
if(!$strPage)
{
$Page=1;
}
$Prev_Page = $Page-1;
$Next_Page = $Page+1;
$Page_Start = (($Per_Page*$Page)-$Per_Page);
if($Num_Rows<=$Per_Page)
{
$Num_Pages =1;
}
else if(($Num_Rows % $Per_Page)==0)
{
$Num_Pages =($Num_Rows/$Per_Page) ;
}
else
{
$Num_Pages =($Num_Rows/$Per_Page)+1;
$Num_Pages = (int)$Num_Pages;
}
$query.=" order by Id ASC LIMIT $Page_Start , $Per_Page";
$result = mysql_query($query) or die(mysql_error());
?>
<table border="0">
<tbody>
<tr>
<td>Name</td>
<td>Email</td>
</tr>
<?php
// Insert a new row in the table for each person returned
if(mysql_num_rows($result) > 0 ) {
while($data= mysql_fetch_array($result)){ ?>
<tr>
<td><?php echo $data['Name'] ?></td>
<td><?php echo $data['Email'] ?></td>
</tr>
<div class="resultbg pagination">
<!--Total <?php //echo $Num_Rows;?> Record : -->
<?
}
if($Prev_Page)
{
echo " <li><a href=\"JavaScript:SANAjax('Listing','$Prev_Page')\"> << Back</a> </li>";
}
for($i=1; $i<=$Num_Pages; $i++){
if($i != $Page)
{
echo " <li><a href=\"JavaScript:SANAjax('Listing','$i')\">$i</a> </li>";
}
else
{
echo "<li class='currentpage'><b> $i </b></li>";
}
}
if($Page!=$Num_Pages)
{
echo " <li><a href=\"JavaScript:SANAjax('Listing','$Next_Page')\">Next >></a> </li>";
}
?>
</div>
<?php
############
}
else{
echo "<div class='error'>No Records Found!</div>";
}
}
################ end
August 6th, 2009 at 12:50 pm
hi thanks for sharing but i am not yet use ur script ..just just now copy the code.
August 10th, 2009 at 11:36 pm
this doesnt work.
http://www.worldwidedrugeconomy.com/testpage.php
August 11th, 2009 at 6:27 am
Bob, i have seen your codes, it has some problems.
1. like function name it should be
function SANAjax(Mode,page) {
2. where is the php page ( SANAjax.php ) please add it
3. <div id="listingAJAX"></div>
you need to add this div in your html page where all records will display
September 4th, 2009 at 6:22 am
Not working… Your codes seems to be need help.
September 5th, 2009 at 6:52 am
have you created db connection.. ???
without db connection it not fetch any records. send us url.
September 8th, 2009 at 4:59 pm
not checked yet …….
September 9th, 2009 at 10:35 am
Please check working demo now
September 24th, 2009 at 3:27 pm
I have copied your script from above but it still doesn’t work. As well as updating the demo did you update the script?
September 25th, 2009 at 7:07 am
Jamie, Post is updated already, but if you check, you need to add your database connection codes yourself. above codes not included that.
September 25th, 2009 at 7:07 am
jamie, or you can give me your website url, so i can check and let you know exactly that problem
September 29th, 2009 at 11:59 am
Thank you so much.
I have implemented ur code with little change according to my requirement.
October 30th, 2009 at 6:27 pm
This doesn’t work. Can you please post the php file? I have revised the php file, found some errors and still doesn’t work.
October 31st, 2009 at 1:55 am
Your script is full of errors! unexpexted T_Else.. having a else on a while.. whats with the
? Most of the php is displayed on the page along with "invalid mysql_query" codes.. please email me about itOctober 31st, 2009 at 6:33 am
you guys need to know.. that this codes need your db connection, and your table names etc. in sort this just a example.. if you change all php codes as per you, it will work well
November 1st, 2009 at 6:18 pm
wow..i have test these codes…nice..very nice..i’ll waiting for your next post..thank you, you solve my problem guys..
November 7th, 2009 at 10:51 pm
SAN .. by any chance, could you release the exact code for the demo page you made? thank you
November 9th, 2009 at 6:07 am
Jeepsion, the codes are exact as on demo page, just we had different database connected to show you working
November 17th, 2009 at 8:01 am
ITs great and very helpful
November 17th, 2009 at 4:05 pm
oh nevermind SAN, actually I got it working just after I posted the comment ..
.. thanx by the way
December 2nd, 2009 at 2:37 am
I tried to implement your code but getting following error.
Parse error: syntax error, unexpected T_ELSE in C:\AppServ\www\radio\files\includes\staffrequestline.php on line 81
I have connected database etc. But still same error. Please help me.
December 4th, 2009 at 7:38 am
Gulfam, please send me url of those pages so we can check that out
December 8th, 2009 at 2:47 pm
hi san, is it possible to fetch posts from my blog(local wordpress) and place them in a div. as the above post helps me to create a sidebar, i want those post fetched act as links and display on the main column. I mean one post displayed at a time..
here is my link. http://www.ead09.org.uk/nars. that my website and planning to use your code for (tubelog). please do help. this site(www.madebysofa.com/#blog) is doing what i’m looking for..!! within the blog link!!
December 9th, 2009 at 8:11 am
Vemkat, yes its possible, if you check “demo link” http://www.w3cgallery.com/examples/Ajax-Fetch-records-by-php-mysql-with-Pagination.php
we also fetching blogs by same technique. you also can fetch that.
December 9th, 2009 at 11:49 am
san, i’ve seen the demo link and its so cool!! Am even looking forward to assign those title of posts to their content. but, dont get to see.. how to create it.!! i’ve managed to pull the first post from database using this(http://www.jamischarles.com/blog/how-to-add-5-full-wordpress-posts-on-an-external-html-page/). but, i want to use the fetching of all posts in a side bar and the recent most one on the content place holder. any help appreciated..!
December 10th, 2009 at 12:09 pm
parse error: syntax error, unexpected T_IF in /Library/WebServer/Documents/nars/SANAjax.php on line 14
any help please,,,!!
December 12th, 2009 at 6:44 am
your code has been nothing display
December 14th, 2009 at 5:59 am
Sankar, creat you db conntion your self with your codes, then it will work out
December 28th, 2009 at 8:23 am
hi San this code is using in jsp then how it is work and what is the file in front end and back end.
thanks
kamal
December 31st, 2009 at 7:41 am
Kamal, it will be so simple. just change Php codes in JSP on SANAjax.php page.
rest all are ajax codes, can be used in JSP codes too
January 4th, 2010 at 11:05 am
Its nice. But is it possible to show page number as 1 2 3 4 5….100 Next
January 6th, 2010 at 7:50 am
yes.. its possible.. need some changes in php file
February 23rd, 2010 at 5:51 pm
Thanks, you really saved my money(time)
March 4th, 2010 at 7:35 pm
K7BvaU Thank you for the material. Do you mind if I posted it in her blog, of course, with reference to your site?
March 6th, 2010 at 5:15 pm
Rcsp9v Excellent article, I will take note. Many thanks for the story!
April 1st, 2010 at 12:23 pm
This line is giving me an error.
Parse error: parse error in C:\htdocs\mywebsite\SANAjax.php on line 9
if($_REQUEST[mode]==”Listing”){
April 21st, 2010 at 9:08 pm
/////a lot error but finally solved ——thx
//////////////////////////code start (SANAjax.php)//////////////
<?php
define(’DB_HOST’, ‘localhost’);
define(’DB_USER’, ‘root’);
define(’DB_PASSWORD’, ”);
define(’DB_DATABASE’, ‘carhaat_db’);
//Array to store validation errors
$errmsg_arr = array();
//Validation error flag
$errflag = false;
//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
die(’Failed to connect to server: ‘ . mysql_error());
}
//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
die(”Unable to select database”);
}
$strPage = $_REQUEST[Page]; //got error
if($_REQUEST[mode]==”Listing”){
$query = “SELECT * FROM members”;
$result = mysql_query($query) or die(mysql_error());
$Num_Rows = mysql_num_rows ($result);
########### pagins
$Per_Page = 5; // Records Per Page
$Page = $strPage;
if(!$strPage)
{
$Page=1;
}
$Prev_Page = $Page-1;
$Next_Page = $Page+1;
$Page_Start = (($Per_Page*$Page)-$Per_Page);
if($Num_Rows
Name
Email
<!–Total Record : –>
<?php
if($Prev_Page)
{
echo ” << Back “;
}
for($i=1; $i<=$Num_Pages; $i++){
if($i != $Page)
{
echo ” $i “;
}
else
{
echo “ $i “;
}
}
if($Page!=$Num_Pages)
{
echo ” Next >> “;
}
?>
<?php
############
}else{
echo “No Records Found!”;
}
//} eror
?>
////////////////////////code end SANAjax.php//////////////////////
May 25th, 2010 at 11:20 am
Love the demo but the script does not work. Can someone email me the html and php that is working to minhkhoa@hotmail.com. thanks.
May 29th, 2010 at 1:11 am
SANajax.php code — working
just replate the connection command with your connection settings
the below code reads from table called ‘randoms’ and displays one field ‘val’
Backend
<?php
require(’connect.php’);
include ‘library/config.php’;
include ‘library/opendb.php’;
$strPage = $_REQUEST[Page];
if($_REQUEST[mode]==”Listing”){
$query = “SELECT * FROM randoms”;
$result = mysql_query($query) or die(mysql_error());
$Num_Rows = mysql_num_rows ($result);
########### pagins
$Per_Page = 5; // Records Per Page
$Page = $strPage;
if(!$strPage)
{
$Page=1;
}
$offset = ($Page - 1) * $Per_Page;
$Prev_Page = $Page-1;
$Next_Page = $Page+1;
$Page_Start = (($Per_Page*$Page)-$Per_Page);
$query.=” order by val ASC LIMIT $Page_Start , $Per_Page”;
$result = mysql_query($query) or die(mysql_error());
if($Num_Rows<=$Per_Page)
{
$Num_Pages =1;
}
else if(($Num_Rows % $Per_Page)==0)
{
$Num_Pages =($Num_Rows/$Per_Page) ;
}
else
{
$Num_Pages =($Num_Rows/$Per_Page)+1;
$Num_Pages = (int)$Num_Pages;
}
while($data = mysql_fetch_array($result))
{
echo $data['val'] . ”;
}
$query = “SELECT COUNT(val) AS numrows FROM randoms”;
$result = mysql_query($query) or die(’Error, query failed’);
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
$maxPage = ceil($numrows/$Per_Page);
?>
$maxPage?$maxPage:$pagetotal;
if ($pagenum - $pagelimit =1 && $pagenum + $pagelimit =1 && $pagenum + $pagelimit > $maxPage) {
$pagemin = ($maxPage-$pagetotal+1) $maxPage) {
$pagemax = $maxPage;
}
for($ppage = 1; $ppage <= $pagemax; $ppage++)
{
if ($ppage == $pagenum)
{
$nav .= ” $ppage “; // no need to create a link to current page
}
else
{
$nav .= ” “.floor($ppage).” “;
}
}
if ($pagenum > 1)
{
$ppage = $pagenum - 1;
$prev = ” [Prev] “;
$first = ” [First Page] “;
}
else
{
$prev = ‘ ’; // we’re on page one, don’t print previous link
$first = ‘ ’; // nor the first page link
}
if ($pagenum < $maxPage)
{
$ppage = $pagenum + 1;
$next = ” [Next] “;
$last = ” [Last Page] “;
}
else
{
$next = ‘ ’; // we’re on the last page, don’t print next link
$last = ‘ ’; // nor the last page link
}
// print the navigation link
echo $first . $prev . $nav . $next . $last;
include ‘../library/closedb.php’;
}else{
echo “No Records Found!”;
}
?>
July 5th, 2010 at 2:46 pm
your code is working fine i used it on my new website for populating data like twitter thanks allot for sharing i donno its optimised in terms of bandwidth or not but who cares
its working so hurrey …
July 23rd, 2010 at 2:27 am
Great work. Thanks. I have implemented the same in my website…
Thanks again.
August 17th, 2010 at 12:10 pm
Hi
If page count is 100 than, page numbers will be shown as 1 2 3 4 5 6….100. It will look so terrible. how to do like “Next 1 2 3 4 5 Prev” and rest of them (up to 100 pages) be hidden. when click on 4 or 5 it will show the hidden pagination what is the solution for it? Can you help me?
October 1st, 2010 at 9:48 am
Hi
I found this working good by making little adjustments…thanks for the efforts taken..but then i need the preloader image to be loaded each and every time before navigating to other pages.Can you help me?
October 28th, 2010 at 8:51 am
Hi, Thanks for the nice code. With small fixes its working fine for me.
October 30th, 2010 at 3:22 am
Thanks for useful code. It works so good and easy to rectify.
November 13th, 2010 at 11:46 am
Do you need any Javascript Library Plugins for this, and also what about being able to include input from the user ?
Thanks
December 15th, 2010 at 8:01 am
Can anybody post an updated download link for this script as it seems my dreamweaver giving alot of errors even though i have duplicated same as u guys have posted..thanks in advance.
January 6th, 2011 at 11:26 am
Above code is not please send working code.
January 6th, 2011 at 5:47 pm
hi SAN i think there are some errors in your php code. i did my db connection…but still not working. i need help can you add this msn? vodsnap@hotmail.com
January 7th, 2011 at 8:06 am
mena, rakesh, we have tested these codes, if you make them little change as per your db connection these works
January 7th, 2011 at 8:39 am
Guys! I have added all files to download now, try and let us know
February 24th, 2011 at 1:15 pm
Thanks Mr. SAN. I just download though. People like you help us in many way. I’m proud to say that your dedication just lead us bright future. Thanks a Lot. All the best. Wish you success in every step.
March 26th, 2011 at 8:45 am
Hi This code is not working..try it as many times u can..but demo is very nice…Please email if any body can to kommuri.nagarjuna@gmail.com
March 29th, 2011 at 7:12 am
I managed to use it in my project - an image gallery. Slight alterations and it is brilliant. Thanks SAN.
May 3rd, 2011 at 2:58 pm
SAN please help i installed your cod is perfect but my prob that all pages was display and link not working heeeepl !! thx
May 3rd, 2011 at 3:14 pm
my DB connection work perfectly !!!
May 3rd, 2011 at 3:34 pm
finallyyyy it’s oki thx !!
June 14th, 2011 at 2:29 am
Hey First off all i’d like to thank you for sharing this wonderfull code
But i have a question is it possible to set the page numbers align to the left under the results ?
June 18th, 2011 at 1:57 pm
Hello,
Can you please show us how to implement the pagination like 1234….10111213?
June 23rd, 2011 at 12:29 am
I have got the ajax paginatoin script working but I’m having trouble getting it into my already ajaxed page
so I have a link that loads a page into a div into my main page that page loads data from a mysql db
the ajax pagination works if it’s on a page by itself but if I try to load it into the ajaxed div element it does not load the data
June 25th, 2011 at 6:36 pm
I had to strip it down quite a bit used my existing function to create my XMLHttpRequest(); placed it all in my head and had a onclick() event on my menu that executed the ajax function to call the paggination script.
This helped set me in the right direction
July 9th, 2011 at 4:19 am
previous error was solved..but i have some new errors like:
Notice: Use of undefined constant Page - assumed ‘Page’ in H:\wamp\www\Ajax-Fetch-records-by-php-mysql-with-Pagination\SANajax.php on line 12
Notice: Use of undefined constant mode - assumed ‘mode’ in H:\wamp\www\Ajax-Fetch-records-by-php-mysql-with-Pagination\SANajax.php on line 13
July 9th, 2011 at 4:37 am
Okk. Done…
Thanks SAN..its working now..
i have to make four changes in ur script..
PAGE : SANajax.php
1) on line 69 :- <?php instead of <?
2) on line 13 :- $_REQUEST['Page'] instead of $_REQUEST[Page]
3) on line 14 :- $_REQUEST['mode']==”Listing” instead of _REQUEST[mode]==”Listing”
in HTML FIRST page:
4) on line 154&155 :-
var pmeters = ‘mode=’+Mode+’&Page=’+Page;
var url = ‘SANajax.php?’+pmeters;
you wrote :
var url = ‘SANajax.php’;
var pmeters = ‘mode=’+Mode+’&Page=’+Page;
now, its ok..100% work for me..thanks again
July 14th, 2011 at 1:52 pm
Hi,
Is it possible to make it seo friendly? The content is not shown in page source. Is it possible that it will be shown at least in the first page? Thanks.
July 23rd, 2011 at 11:28 am
Hellow friends. may b this script working. but i need a ajax pagination with a drop down by which any one can select to display number of record per page eg (5/10/25 rec per page)…. and complete works of without refreshing page
August 2nd, 2011 at 10:51 am
okkk
August 5th, 2011 at 9:12 am
Parse error: syntax error, unexpected T_VARIABLE in B:\wamp\www\experiment\pagination\SANajax.php on line 68
i`m getting this error please help me out
August 6th, 2011 at 9:39 am
Hi
If page count is 100 than, page numbers will be shown as 1 2 3 4 5 6….100. It will look so terrible. how to do like “Next 1 2 3 4 5 Prev” and rest of them (up to 100 pages) be hidden. when click on 4 or 5 it will show the hidden pagination what is the solution for it? Can you help me?
August 6th, 2011 at 9:44 am
Please help me it`s quite urgent for me
August 22nd, 2011 at 2:48 pm
Hi, this is working great! Thanks for sharing!
September 2nd, 2011 at 12:35 pm
Hi, this is working great! But one think
We want to paging like that 1 2 3 4 5 ——- 24 25
If total No. of Pages is 25.
Please help me it`s quite urgent for me.
October 4th, 2011 at 7:09 am
Hi friends,
This script helps me a lot.
November 21st, 2011 at 11:48 pm
Hi, i’m trying to get the script working under wordpress and query only on one category (id 8). Any idea?
this is not working
$query = “SELECT ID, guid, post_title, post_date, post_content, post_category, FROM wp_posts WHERE category_id = ‘8′ post_type=’post’ AND post_status=’publish’”;
November 27th, 2011 at 2:37 pm
demo not working. just make it……
November 28th, 2011 at 1:57 pm
Sachin@ it works
November 28th, 2011 at 6:35 pm
Hi this is the correct query for a selected category under wordpress:
$query = “SELECT distinct ID, post_title, post_date, guid, post_content, post_name FROM wp_posts, wp_terms, wp_term_relationships WHERE wp_posts.id = wp_term_relationships.object_id AND wp_term_relationships.term_taxonomy_id = wp_terms.term_id AND post_type=’post’ AND post_status=’publish’ AND wp_terms.slug = ‘your-category’”;
I also have other questions.
How do i take a custom field named foto?
Why the first result doesn’t take the styles i assign to elements result?
January 4th, 2012 at 8:32 pm
I do not even know how I stopped up right here, however I believed this put up was once great. I don’t recognise who you might be but certainly you are going to a famous blogger if you aren’t already. Cheers!
January 24th, 2012 at 8:12 am
thanks very nice Tutor, its run and work