Infinite Scrolling (mysql, php & js) Loop Issue

Question

Big disclaimer to start with: I'm still super new to php & js, so please be gentle... haha

Anyway, I'm trying to create a simple infinite scroll that retrieves posts from a mysql table. I've cobbled together code from various internet tutorials to create something that is accessing and retrieving information ok. The problem is the number of posts and which one's it's retrieving.

With "$perpage" in the PHP file set to 10, The first 20 posts load, then every time it loads "new posts", it just loads posts 11-20 over and over. I imagine it's probably something simple, but my level of php & js is really only at the level where I can sort of understand it to read, but can't write it.

I've tried adding in LIMIT to the end of the $sql line, but that just seemed to break the code altogether.

Any help would be greatly appreciated!

If you want to see this in action in real life, the (very) draft site is here: site

$(document).ready(function(){
  function getresult(url) {
    $.ajax({
      url: url,
      type: "GET",
      data:  {rowcount:$("#rowcount").val()},
      beforeSend: function(){
        $('#loader-icon').show();
      },
      complete: function(){
        $('#loader-icon').hide();
      },
      success: function(data){
        $("#faq-result").append(data);
      },
      error: function(){}             
    });
  }
  $(window).scroll(function(){
    if ($(window).scrollTop() == $(document).height() - $(window).height()){
      if($(".pagenum:last").val() <= $(".total-page").val()) {
        var pagenum = parseInt($(".pagenum:last").val()) + 1;
          getresult('../php/best_result.php?page='+pagenum);
      }
    }
  }); 
});


<?php
require_once("login.php");
$db_handle = new DBController();
$perPage = 10;

mysql_query("SET NAMES utf8");

$sql = '
  SELECT letter, dayfield, monthfield, yearfield, nickname, country
  FROM BestWorst
  WHERE `lettertype` = "best" ORDER BY `ID` DESC';



$page = 1;
if(!empty($_GET["page"])) {
$page = $_GET["page"];
}

$start = ($page-1)*$perPage;
if($start < 0) $start = 0;

$query =  $sql . " limit " . $start . "," . $perPage; 
$faq = $db_handle->runQuery($query);

if(empty($_GET["rowcount"])) {
  $_GET["rowcount"] = $db_handle->numRows($sql);
}
$pages  = ceil($_GET["rowcount"]/$perPage);
$output = '';
if(!empty($faq)) {
  $output .= '<input type="hidden" class="pagenum" value="' . $page . '" /><input type="hidden" class="total-page" value="' . $pages . '" />';
  foreach($faq as $k=>$v) {
    $output .= '<div class="entry wow fadeInUp lefttext"><br/><p>'
      . nl2br($faq[$k]["letter"])
      . '</p><br/><p class="small" align="right">- This story took place on '
      . $faq[$k]["dayfield"] . '/'
      . $faq[$k]["monthfield"] . '/'
      . $faq[$k]["yearfield"]
      . '. Posted by ' . $faq[$k]["nickname"]
      . ' from ' . $faq[$k]["country"]
      .'<p></div><br/><br/><br/>';
  }
}
print $output;
?>

Put in some debugging info. Have you confirmed the correct URL is being passed to $.ajax() and that the expected query is being built for MySQL? — miken32
– miken32, Commented Jan 1, 2016 at 3:00
Your jQuery code looks like it's expecting multiple elements with the class pagenum but there's only one output by the PHP. And it just outputs the passed value from $_GET["page"] without changing it. — miken32
– miken32, Commented Jan 1, 2016 at 3:05
Thanks Miken32! Ok, some noob answers coming your way: 1) I didn't realise the URL needed a specific page. I had inadvertently left it literally as "url". I've tried inserting the php file above as the url, but that just then loops posts 1-10 over and over and over again. 2) In regards to "jQuery being specifically built for MySQL", I'm not sure what this means..? However, it's been retrieving information from the sql database with no issue, it's just the order of the retrieval that's the issue. 3) Your last point about expecting multiple elements, what changes to the code would you recommend? — Leesome
– Leesome, Commented Jan 1, 2016 at 3:17
@miken32 He's got a :last in his selector. Each time the jQuery completes it adds more html. That html will have another class="pagenum" element. After he fjnds the value, he adds 1 to it and then uses that incremented value to build his url. — user5051310
– user5051310, Commented Jan 1, 2016 at 4:54
@Leesome what miken32 is asking about "the expected query being built for MySQL" is: is the string that is assigned $query the actual query you expect it to be? So if you were to echo $query;, is what you see, what you expect AND does that query work as expected in phpMyAdmin (or whatever tool that you use to run SQL queries directly on the database) — user5051310
– user5051310, Commented Jan 1, 2016 at 4:58

Ahmad Baktash Hayeri · Accepted Answer · 2016-01-01 08:56:20Z

1

You have a little confusion regarding the usage of $start and $perPage in your query.

In MySQL, the following query gets the first 15 results, starting from 11th row (OFFSET 10) in that set of results:

SELECT * FROM BestWorst LIMIT 15, 10

which is the same as

SELECT * FROM BestWorst LIMIT 15 OFFSET 10

The usage of $start in your case should map to the OFFSET of the query, whereas the $perPage is literally LIMIT in MySQL terms.

Therefore, swapping the two mentioned variables in your query should solve the problem.

$query =  $sql . " limit " . $perPage . "," . $start;

Now, as you scroll down the page, and get the value of pagenum (sent via AJAX to your PHP script) in the $page variable, $start is multiplied accordingly, giving you the values (0, 10, 20, ...) for the OFFSET part of your query - which is quite what you expect.

edited Jan 1, 2016 at 8:56

answered Jan 1, 2016 at 8:49

Ahmad Baktash Hayeri

5,9004 gold badges33 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Infinite Scrolling (mysql, php & js) Loop Issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related