So I have an infinite scroll setup for my page. The data loads and looks good without infinite scroll, when I add LIMIT to my query, it seems to not want to work properly at all. When I var_dump($start) or ($limit) they both show the proper values. What seems to happen is that it only grabs and displays the first 5 results instead of adding 5 to $start in my AJAX code.
Here is my code to grab the data:
if(isset($_POST['start']) && isset($_POST['limit'])){
$start = $_POST['start'];
$limit = $_POST['limit'];
}
$query = "SELECT
count(replies.reply_topic) as replyCount,
topics.topic_id,
topics.topic_subject,
topics.topic_date,
topics.topic_cat,
topics.topic_creator,
topics.topic_likes,
users.user_id,
users.username,
profile.profile_id,
profile.profile_pictureMain,
profile.profile_users
FROM
topics
LEFT JOIN
users
ON
topics.topic_creator = users.user_id
LEFT JOIN
replies
ON
replies.reply_topic = topics.topic_id
LEFT JOIN
profile
ON
profile.profile_users = users.user_id
GROUP BY
topics.topic_id
ORDER BY
topics.topic_likes DESC
LIMIT :start, :limit //<-When I add this line it will only display 5 items max
";
$stmt = $conn->prepare($query);
$stmt->bindParam(':start', intval($start), PDO::PARAM_INT); //<-not sure if bindParam is causing an issue...intval() is needed in order to prevent an SQL error
$stmt->bindParam(':limit', intval($limit), PDO::PARAM_INT);
$stmt->execute();
$returnAmt = $stmt->fetchAll();
if($stmt->rowCount() > 0){
$returnValues = "";
foreach($returnAmt as $row){
$returnValues .=
'data to display';
}
echo $returnValues;
exit();
}else{
exit('No more results');
}
Then in my jquery file I have the AJAX call
function infiniteScrollData(){
if(reachedMax){
return;
}
$.ajax({
method: "POST",
url: "sortResults.php",
dataType: "text",
data: {
infiniteScrollData: 1,
start: start,
limit: limit
},
success: function(returnValues){
if(returnValues == "No more Results"){
reachedMax = true;
}
else{
start += limit;
$("#rowDisplayResults").append(returnValues);
}
}
});
}
And finally I display the data here on the main page:
<div id="rowDisplayResults">
<!--Display results here-->
</div>
<script type="text/javascript">
//Variables for infinite scroll
var start = 0;
var limit = 5;
var reachedMax = false;
$(window).scroll(function(){
if($(window).scrollTop() == $(document).height() - $(window).height()){
infiniteScrollData();
}
});
$(document).ready(function(){
//infinite scroll
infiniteScrollData();
});
</script>
Thanks in advance!