In previous article – Loading More Results (jQuery/PHP), the records are loaded when the users clicked on “Load more Data” button, which is great but how about loading database records automatically when user scrolls down to the bottom of the page? The technique can be seen in Twitter, Facebook and several other websites. Let’s use examples from previous article, with some modification we can create Ajax based auto loading script, which loads records when user scrolls to bottom of the page.
Run MySql query below to create a table in MySql for this demo. You can also find a sql file in download, which you can import in PhpMyAdmin.
1 2 3 4 5 6 | CREATE TABLE IF NOT EXISTS `paginate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(60) NOT NULL, `message` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=51 ; |
Let’s start with a configuration file to store our MySql database username and password.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <?php $db_username = 'xxxxx'; $db_password = ''; $db_name = 'xxxxxx'; $db_host = 'localhost'; $item_per_page = 5; $mysqli = new mysqli($db_host, $db_username, $db_password, $db_name); //Output any connection error if ($mysqli->connect_error) { die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); } ?> |
Create a UL element with ID “results”, this is where we are going to display the results fetched from server. You can wrap it with a DIV element, it will give you more control when you style them with CSS:
1 2 3 | <div class="wrapper"> <ul id="results"><!-- results appear here as list --></ul> </div> |
I have made few changes in jQuery code below. The .click() method is replaced with .scroll(), our code can now detect the page scroll, and auto loads remaining data when user scrolls to bottom of the page.
Just make sure, the height of initially loaded content is greater than the height of page itself for scroll to work. Which means if there’s no vertical scrollbar in browser, you might want to increase $item_per_page number in PHP config script.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | (function($){ $.fn.loaddata = function(options) {// Settings var settings = $.extend({ loading_gif_url : "ajax-loader.gif", //url to loading gif end_record_text : 'No more records found!', //no more records to load data_url : 'fetch_pages.php', //url to PHP page start_page : 1 //initial page }, options); var el = this; loading = false; end_record = false; contents(el, settings); //initial data load $(window).scroll(function() { //detact scroll if($(window).scrollTop() + $(window).height() >= $(document).height()){ //scrolled to bottom of the page contents(el, settings); //load content chunk } }); }; //Ajax load function function contents(el, settings){ var load_img = $('<img/>').attr('src',settings.loading_gif_url).addClass('loading-image'); //create load image var record_end_txt = $('<div/>').text(settings.end_record_text).addClass('end-record-info'); //end record text if(loading == false && end_record == false){ loading = true; //set loading flag on el.append(load_img); //append loading image $.post( settings.data_url, {'page': settings.start_page}, function(data){ //jQuery Ajax post if(data.trim().length == 0){ //no more records el.append(record_end_txt); //show end record text load_img.remove(); //remove loading img end_record = true; //set end record flag on return; //exit } loading = false; //set loading flag off load_img.remove(); //remove loading img el.append(data); //append content settings.start_page ++; //page increment }) } } })(jQuery); $("#results").loaddata(); //load the results into element |
The page number passed to PHP script using jQuery $.post() method in script above, is used to determine the position of the records in database query as you can see in PHP script below. I am using MySqli Prepared Statements to fetch the records, which is very secure and faster way. If you are confused or curious about MySqli, you can see PHP MySqli Basic usage.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <?php include("config.inc.php"); //include config file //sanitize post value $page_number = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH); //throw HTTP error if page number is not valid if(!is_numeric($page_number)){ header('HTTP/1.1 500 Invalid page number!'); exit(); } //get current starting point of records $position = (($page_number-1) * $item_per_page); //fetch records using page position and item per page. $results = $mysqli->prepare("SELECT id, name, message FROM paginate ORDER BY id DESC LIMIT ?, ?"); //bind parameters for markers, where (s = string, i = integer, d = double, b = blob) //for more info https://www.sanwebe.com/2013/03/basic-php-mysqli-usage $results->bind_param("dd", $position, $item_per_page); $results->execute(); //Execute prepared Query $results->bind_result($id, $name, $message); //bind variables to prepared statement //output results from database while($results->fetch()){ //fetch values echo '<li>'.$id.') <strong>'.$name.'</strong> : '.$message.'</li>'; } ?> |
Thanks a lot, very helpfully
ReplyVery Nice. Would you kindly how do it with Java Script?
Replyi failed to download the project please help me
ReplyHi,
Thank you for this. I just would like to ask how to go about implementing this with a search capability?
Replyif i want not to scroll the complete page just a single div then can you tell me how to do it
ReplyThe previous bug already handled. Thanks.
But i found another bug, if data load from mysql were fewer than the $item_per_page limit, not all data displayed.
Example:
data count to be displayed = 7
$item_per_page = 12
the result will only 4 row instead of 7 row.
Please help me. Thanks!
ReplyIam getting this error:
ReplyWarning: mysqli_stmt::bind_param():Number of variables doesn’t match number of parameters in prepared statement
Can you show me your prepared statement?
Please can you give a tutorial on comment on a post as well as post likes.
ReplyWill be very glad. Great post
Iam getting this error:
Warning: mysqli_stmt::bind_param():Number of variables doesn’t match number of parameters in prepared statement in my root directory\fetch_pages.php on line 20
Please can you help me fix it.
ReplyIam getting this error:
ReplyWarning: mysql_stmt::bind_param():Number of variables doesn’t match number of parameters in prepared statement in my root directory\fetch_pages.php on line 20
This is a bug, when scroll at bottom untikl no data and then refresh page, and then scroll to the bottom again until no data, and see the numbers is skipped alot
ReplyI’ve updated the code, please try again and let me know.
Can you provide link of updated code ?
When u refresh at the bottom of the page, some rows are missing…
Replywhat if i wanna create overflow x:scroll and make it the trigger for autoload?
Replywhat should i do if i wanna change from scroll overflow y to scroll overflow x ??
Replyiam trying
if($(window).scrollBottom() + $(window).width() >= $(document).width())
Very Nice tutorial. i want to use it for http://howcanisolve.com . is it work with all browsers?
ReplyThanks for sharing! :)
Reply–
From
Uganda, East Africa
Thanks!!!
ReplyGreat tutorial
Replythanks for the post,,,,great tutorial
ReplyNice and easy! Thanks!
ReplyFound script jumping records on fast scrolling
So modified this
2
3
4
5
6
7
8
9
10
11
12
if($(window).scrollTop() + $(window).height() >= $(document).height()) { //if user scrolled to bottom of the page
function checkVariable() {
if(loading == false){
track_page++; //page number increment
load_contents(track_page); //load content
}
}
$('.loading-info').show(); //show loading animation
setTimeout(checkVariable,1000);
}
});
Thank you!
Thank you so much! I was trying to find a solution for this on my own and probably wouldn’t have thought of this.
suggestion for fade effect on display
$(data).hide().appendTo(“#results”).fadeIn(1000);
ReplyNice tutorial
ReplyThat nice, do you have “Auto Load More Data” plugin for wordpress?
ReplyIf not, I need to convert your code to wordpress plugin.
Anyway thanks,
This artical was more helpful to me. Thank you!
Replyhi, I hope you make the same tutorial for CodeIgniter. thanks
ReplyNothing relation with codeigniter. This is javascript not php. You must know difference between them before ask.
its not working for me
Replyhope u can check and tell me the error
the page is not loading by itself whn i try to inspect (ctrl+shift+I) it loads
Thanks a lot for all tutorials. are awesome. I learn a lot.
Replyiam using in my site thanx alot
Replyhi friend i i don’t know how to correct the error of ajax utf8 decode because my all my data are with arabic language and i see result like this ??????????? if someone have solution please tell me and thank you
ReplyTry using mysqli_set_charset($conn, “utf8”); before your mysqli_query.
$conn is the mysqli_connect().
Nice post save my time to get this point.
ReplyDoes anyone know how to do the same using a database in postgresql instead of MySQL?
Any website or Manual?
I tried to use this one and change it but it is not working.
config.php
connect_error) {
die(‘Error : (‘. $db->connect_errno .’) ‘. $db->connect_error);
}
?>
Not really sure on how to change the autoload_process.php and index.php
I just changed the $mysqli for $db.
THANKS
Replyworks well on opera n firefox but does not work on chrome
ReplyHi I have a problem, im using bootstrap and using pills/tabs, now when I go to other tab, the window height function seems not working. can you suggest any thing to do?
ReplyPlease, where should i save the PHP file?
ReplyHi,
ReplyThanks for sharing code, it very helpful,. i have one problem, this load more i need center page. when i am going footer section it will works,.
Good, Could you please let me know if I want to load pages on scroll let say 3 pages on scroll at a time and then there is button to click to load more and then on click 1 page can be loaded until there is some content on the page?? Thanks
Regards
ReplySateesh
http://www.savedimes.com
Great tutorial. Made my day. J
It would be good to have a ‘show more’ button after scrolls down. But, it doesn’t load more records if, on the first screen, if there’re not enough records (example, items_per_group= 2) to enable first scroll. This behavior can be tacked.
Great post. Thx
Replythis file won’t download
Replyplease fix it or email it to please
Thank you
Sam
Nice tutorial by the way. save my day
Replywill this work on MVC framworks like CodeIgniter?
ReplyIt can be better if You can give alto scroll for 3 scroll downs after tat a show more button to load further data
Reply