javascriptjquerysqlajax

load more records script show the same last record from a previous set of records if a new record is added

I'm very confused and I don't know how to solve this problem i'm not sure if I have to adjust the jQuery or the SQL to properly show this without duplicates. I just don't know, so I have this script that loads a set of records every time you press the load more button.

It works great but if I would add a new record before I load the next set of records it will show a duplicate of the last record from the previous set of records into the next set of records. The two examples below represent the numbers as records

Example 1

Normal situation no records added from any source

Total records 6

6,5,4 (Load more) 3,2,1

Example 2

A new record is added from any source before the next set of records are loaded with the load more button

Total records 6

6,5,4 (A new record is added for example in php my admin without using this script) 4,3,2

As you can see in example 2 I added a new record in php my admin before I loaded the next set of records and you can see the first load contains records 6,5,4 and the next load of records contains 4,3,2 notice it shows 4 again it's suppose to show 3,2,1

That is the easiest way that I can describe my problem. I added gif screenshots of my problem to better understand what i'm facing. Pay attention to number 228 and 227 that are inside the yellow ball this is a normal working situation that is illustrated in the first gif screenshot note no new record is added from any source.

Normal example

In this gif screenshot I added a new record from another source for example in php my admin before I loaded the next set of records by the load more button. This is my problem situation that i'm trying to fix pay attention to 228 in the yellow ball you can see it shows 228 two times because I added a new record behind the scenes. It should show 228 one time regardless if I added a new record behind the scenes or not.

Unexpected example

So how can I fix this problem? I don't want a duplicate record to show in the next load because I added a new record before I load the next set of records.

Let me know if it's the jQuery that is causing this or the SQL that is causing.

My code

index.html

<!DOCTYPE html>
<html>
<head>
<style>

#load-more-button {
width: 100px;
margin: auto;
display: block;
}

.results-container {
width: 350px;
margin: auto;
}

</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>        
<script type="text/javascript">
$(document).ready(function (){

/*<load more by pressing a button script>*/

//<settings define
var start = 0;
var limit = 10;
//

//Call the ajax request on page load
getData();
//

//load more records by pressing a button
$('#load-more-button').click(function(){
getData();
});
//

/*<function>*/
function getData() {

//settings variable
var settings = {
getData: 1,
start: start,
limit: limit
};
//

//result variable
var result= function(response) {
start += limit;
$(".results-container").append(response);
}
//

//request
$.ajax({
url: 'data.php',
type: 'POST',
dataType: 'text',
data: settings,
success: result 
});
}
/*</function>*/
/*</load more by pressing a button script>*/

});
</script>
</head>
<body>

<div class="results-container"></div>
<button id='load-more-button'>Load more</button>
</body>
</html>

data.php

<?php
$servername='localhost';
$username='jd';
$password='1234';
$db_name= 'test';

$connect = new mysqli($servername,$username,$password,$db_name);

$start = $connect->real_escape_string($_POST['start']);
$limit = $connect->real_escape_string($_POST['limit']);

$query = "SELECT*FROM records WHERE NOT status='remove' ORDER BY ID DESC LIMIT $start, $limit";

$result= $connect->query($query);

?>
<style>
#number{
background-color: gold;
color: black;
border-radius: 100%;
padding: 5px;
}

h2{
display: inline-block;
}

</style>

<?php while($row = $result->fetch_assoc()) { ?>

<h2 id='number' ><?php echo $row['id']; ?></h2>
<h2><?php echo $row['html_id']; ?></h2>
<p><?php echo $row['source']; ?></p>

<?php } ?>

<h1>
<?php 
//Conditional statement if there is no more records to load
if($result->num_rows == 0){
    echo 'End';
    echo'
    <script>
    $("#load-more-button").css({"display": "none"});
    </script>
    ';
}
//
?>
</h1>
Solution

  • Your SQL query is correct but wrong for this situation.

    SELECT*FROM records WHERE NOT status='remove' ORDER BY ID DESC LIMIT $start, $limit";

    e.g

    You have 1,2,3,4,5,6 record in your database. Your query retrieves records in descending order. so first 3 records retrieved like 6, 5, 4 then in next request 3,2,1 will be retrieved.

    But when your first 3 records retrieved from database (6,5,4) then you are adding one more record with id = 7. Now your table has total 7 records. When you click load more again it skips first 3 records. Means it skips 7, 6, 5. So 4, 3, 2 will be returned in next query. that's why 4 is repeated.

    Make sense ??