SQL Count 使用 LIMIT 时的总行数 [英] SQL Count total number of rows whilst using LIMIT

问题描述

我正在尝试创建一个简单的图片库，每页显示 16 张图片.我使用 LIMIT 16 在页面上显示正确的数量，但如果行数超过 16，我希望在底部有链接，允许用户导航到下一页.

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.

我知道我可以通过删除限制并简单地使用循环来显示前 16 个项目来达到预期的结果，但这会是低效的.显然，计算行数总是 = 16.

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?

谢谢

推荐答案

除了用于获取其他人提到的计数的单独查询之外，您还需要抵消您所在给定页面的结果.

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;

关于 SQL 注入的正常警告将适用于此.

The normal warnings about SQL injection would apply here.

这篇关于SQL Count 使用 LIMIT 时的总行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！