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