CODEWITHSUNDEEP
phpjquerymysqlajaxpdo
Liam
Liam

Reputation: 9855

Get results from table using AJAX and PHP on infinite scroll?

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

Ive the follwoing so far...

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 query seem to work fine, the problem im having though is its just pulling asll of the records again, as opposed to the next 20?

Ive never done this before so sorry if i sound naive but any help would be much appreciated!

Upvotes: 6

Views: 1869

Answers (3)

Ripa Saha
Ripa Saha

Reputation: 30

You need to add limit in your mysql query. please see the 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

Upvotes: 1

RisingSun
RisingSun

Reputation: 1733

Well, the short answer is to add offset to your query. It is just like pagination. You just keep track of which page you are in and offset the query by certain value. In this case, instead of changing pages, you just load it at the end. Here is an example You can google it and read about it more

Upvotes: 0

akamaozu
akamaozu

Reputation: 705

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

edit: Whoops. That's for regular mysql, not using a prepared statement. Based on php docs, here is what your answer should look like:

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

What this does is it grabs the next 20 results after $lastIndex. All you need to do is to send the value of lastIndex (which should coincide with number of results on the page) with your request and use it accordingly.

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

Upvotes: 0

Related Questions