Skip to content
Advertisement

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>";
 });

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.

Advertisement

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>"; 
}
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement