php - 图像分页 - 如何在一个查询中获取所有参数

Question

我有一个分页系统，用于查看数据库中的图像。
所以每次我点击一个next / prev按钮时，我都需要三个变量
问题 - 有没有办法让它们在一个查询中而不是为每个查询使用单独的查询？

$lm = 100; // limit per page  
$off = 0; // offset - click on next prev buttons  
$dim = '960 x 540'; // img dimensions  

function get_images($lm, $off, $dim){
global $db;
//firstly I need total number of images 
$st = $db->query("select count(*) as cnt from images");
$total = $st->fetchColumn(); 

//then I need number of 960 x 540 images  
$st = $db->query("select * from images where dim = '" . $dim . "'");
$insel = $st->rowCount();  

//then I need number of viewed images on a pagination system        
$st = $db->query("select * from images where dim = '" . $dim . "' order by date desc limit " . $lm . " 
offset " . $off);
$inview = $st->rowCount();

标签： phpmysql

Answer 1

$st = $db->query("select * from images where dim = '" . $dim . "' count(id) over(partition by id) as cnt  count(dim) over(partition by dim) as insel order by date desc limit " . $lm . " offset " . $off);

但请确保您使用的 MySQL 版本大于或等于 8.0