Ajax Fetch records by php/mysql with Pagination ?
12 Votes, Rating: 4.83
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
<code> <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="loading.gif" align="center" />'; } if(HttPRequest.readyState == 4) // Return Request { var response = HttPRequest.responseText; document.getElementById("listingAJAX").innerHTML = response; } } }</script> <p> <div id="listingAJAX"></div> </code>
Add this div on your page where you want to display all list
Backend page ( SANAjax.php)
<code>
############# must create your db base connection
$strPage = $_REQUEST[Page]
if($_REQUEST[mode]=="Listing"){
$query = "SELECT * FROM Ajax_php_example";
$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 CustomerID 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
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 : -->
<?php
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>";
}
}
?>
</tbody></table>
</code>
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.