Flagging last overall row in MySQL query using LIMIT

I am retrieving results from a database in batches of 5, each time taking the last ‘id’ and returning it via jquery so only rows with smaller ‘id’ are returned. I have a load more button which should disappear after all rows are loaded, even if that means 5, 7 or 11 rows.

I have this query:

 $query = $pdo->prepare("SELECT * FROM names WHERE id < ? ORDER BY id DESC LIMIT 5");
 $query->execute([$_POST["id"]]);
 while($row = $query -> fetch()) {
echo "<div id="$row["id"].">".$row["name"]."</div>";
 });

JavaScript
​x
 
 $query = $pdo->prepare("SELECT * FROM names WHERE id < ? ORDER BY id DESC LIMIT 5"); $query->execute([$_POST["id"]]); while($row = $query -> fetch()) {echo "<div id="$row["id"].">".$row["name"]."</div>"; });​

The question is, is there an easy and performant way to add a column to flag each row if it’s last or not in a single query? Considering I am using LIMIT of 5, obviously the query should also check the potential existence of 5+1 row.

Answer

Set the limit to 6 and check the number of returned rows.

for the while loop, use a counter:

$query = $pdo->prepare("SELECT * FROM names WHERE id < ? ORDER BY id DESC LIMIT 6");
$query->execute([$_POST["id"]]);

$n=5;
while($row = $query -> fetch() && $n-->0) 
{
  echo "<div id="$row["id"].">".$row["name"]."</div>"; 
}

JavaScript
 
$query = $pdo->prepare("SELECT * FROM names WHERE id < ? ORDER BY id DESC LIMIT 6");$query->execute([$_POST["id"]]);​$n=5;while($row = $query -> fetch() && $n-->0) {  echo "<div id="$row["id"].">".$row["name"]."</div>"; }​

Advertisement

Answer