Get results from a table using AJAX and PHP with infinite scrolling?

Question

I am trying to get 20 results from my table on the fly (when the user scrolls down) using AJAX and PHP.

Still...

Ajax

// Infinite Scroll loadData( 0 ); //Hide Loader for Infinite Scroll $('div.ajaxloader').hide(); }); function loadData ( last_id ) { var $entries = $('.directory'), $loader = $('.ajaxloader', $entries).show(); $.get( '/getentries.php', { last_id : last_id }, function( data ) { $entries.append( data ).append( $loader.hide() ); filterEntries(); }); }; //Isotope filter - no changes to this code so I didn't include it $(window).scroll(function () { if ($(window).scrollTop() >= $(document).height() - $(window).height() - 10) { $('div.ajaxloader').show('slow'); loadData( $( '.directory item:last' ).data('id') ) } 

Php

 try { // Connect and create the PDO object $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb); $conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8 // Define and perform the SQL SELECT query $sql = "SELECT * FROM `directory` WHERE user_active != ``"; $result = $conn->query($sql); $sth = $conn->prepare("SELECT * FROM directory WHERE user_active != ''"); $sth->execute(array(':county' => $county)); $c = 1; while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { echo '<div class="entry'; if (($c % 4) == 1) echo ' alpha '; // ECHO RESULT STYLES ETC HERE $c++; } $conn = null; // Disconnect } catch(PDOException $e) { echo $e->getMessage(); } 

Now my AJAX and the request seem to work, is there a problem with it, although it's just pulling all the data over with it again, unlike the next 20?

I never did this before so sorry if I would be naive, but any help would be greatly appreciated!

Answer 1

You need to add a limit to your mysql query. see below code

 // Infinite Scroll loadData( 0 ); //Hide Loader for Infinite Scroll $('div.ajaxloader').hide(); }); function loadData ( last_id ) { var $entries = $('.directory'), $loader = $('.ajaxloader', $entries).show(); $.get( '/getentries.php?startp=0&endp=20', { last_id : last_id }, function( data ) { $entries.append( data ).append( $loader.hide() ); filterEntries(); }); }; //Isotope filter - no changes to this code so I didn't include it $(window).scroll(function () { if ($(window).scrollTop() >= $(document).height() - $(window).height() - 10) { $('div.ajaxloader').show('slow'); loadData( $( '.directory item:last' ).data('id') ) } PHP try { // Connect and create the PDO object $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb); $conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8 // Define and perform the SQL SELECT query $sql = "SELECT * FROM `directory` WHERE user_active != ``"; $result = $conn->query($sql); $sth = $conn->prepare("SELECT * FROM directory WHERE user_active != '' limit $_REQUEST['startp'],$_REQUEST['endp']"); $sth->execute(array(':county' => $county)); $c = 1; while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { echo '<div class="entry'; if (($c % 4) == 1) echo ' alpha '; // ECHO RESULT STYLES ETC HERE $c++; } $conn = null; // Disconnect } catch(PDOException $e) { echo $e->getMessage(); } 

Thanks Ripa Saha

Answer 2

SELECT * FROM foo WHERE id > $lastIndex ORDER BY id LIMIT 20;

edit: Oops. This is for regular mysql without using a prepared statement. Based on php docs , here is what your answer looks like:

 $sql = "SELECT * FROM directory WHERE id > :lastIndex AND user_active != '' LIMIT 20"; $sth = $conn->prepare($sql); $sth->execute(array(':lastIndex' => $lastIndex)); 

What this means is, it captures the next 20 results after $lastIndex . All you have to do is send the lastIndex value (which should match the number of results on the page) with your request and use it accordingly.

Alternatively, you can do the opposite and send $lastIndex with your result to the javascript client for storage and use when the user makes a new request.

Answer 3

Well, the short answer is to add an offset to your query. This is like pagination. You simply keep track of which page you are on, and bias the request by a specific value. In this case, instead of changing pages, you simply load it at the end. Here is an example you can google and read more about it