SQL Count total number of rows whilst using LIMIT

Question

Hi, I'm trying to create a simple image gallery, showing 16 images per page. I'm using LIMIT 16 to display the correct amount on the page, but if there are more than 16 rows, I want to have links at the bottom, allowing the user to navigate to the next page.

I know I could achieve the desired result by removing the limit and simply using a loop to display the first 16 items, but this would be inefficient. Obviously, COUNTING the number of rows would always = 16.

$sql .= "posts p, images im, postimages pi WHERE
    i.active = 1 
    AND pi.post_id = p.id
    AND pi.image_id = im.image_id 
    ORDER BY created_at LIMIT 16";

Can anyone suggest a more efficient way of doing it?

Thanks

Answer 1

Are you trying to fully paginate, or just have this page and next page? If it's the latter you can simply LIMIT 17 and count the rows of the result. If you get 17 you need to paginate.

Answer 2

Run a separate query with no limit that only returns the count.

Answer 3

use two queries, where one is your query and the other is:

SELECT COUNT(primary_key) FROM ... WHERE...

Answer 4

You need to offset your results for the given page you are on in addition to the separate query for getting the count that everyone else has mentioned.

$sql .= "posts p, images im, postimages pi WHERE
    i.active = 1 
    AND pi.post_id = p.id
    AND pi.image_id = im.image_id 
    ORDER BY created_at 
    LIMIT ". ($page - 1) * $pagesize .", ". $pagesize;

The normal warnings about SQL injection would apply here.

Answer 5

In MySQL:

SELECT  SQL_CALC_FOUND_ROWS
        *
FROM    posts p, images im, postimages pi
WHERE   i.active = 1 
        AND pi.post_id = p.id
        AND pi.image_id = im.image_id 
ORDER BY
        created_at
LIMIT 16;

SELECT  FOUND_ROWS();

The first query will return 16 rows, and the second query will return you the number of rows which would be returned be there no LIMIT clause in the first query.

You may use it to decide whether to show the "next" link or not.