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个项目来实现期望的结果，但是这将是低效的。显然，COUNTING的行数总是= 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屋！