PHP分页查询解决方案
程序员文章站
2022-06-06 18:32:51
...
PHP分页查询
假设表的名称为A,记录条数据为未知.
各个问题最好用一条SELECT 实现
1.取出表中前10条记录.(按物理逆序又应如写)
2.取出表中的后10条记录.(按物理逆序又应如写)
3.取出表中第10条到第20条记录.(按物理逆序又应如写)
4.取出表中的所用记录,并加上两列分别是行号及物理行号.
---------------------------
1、select * from a limit 0,9
2、select top(10) from a order by id desc
3、select * from a limit 9,19
---------------------------
CREATE TABLE sort ('Col1' INT (3) UNSIGNED NOT NULL, 'Col2' CHAR (1) NOT NULL, PRIMARY KEY (Col1),INDEX Col2 (Col2));
INSERT INTO sort (Col1, Col2) VALUES (1, 'A');
INSERT INTO sort (Col1, Col2) VALUES (2, 'C');
INSERT INTO sort (Col1, Col2) VALUES (3, 'B');
INSERT INTO sort (Col1, Col2) VALUES (4, 'D');
INSERT INTO sort (Col1, Col2) VALUES (5, 'F');
INSERT INTO sort (Col1, Col2) VALUES (6, 'E');
INSERT INTO sort (Col1, Col2) VALUES (7, 'H');
INSERT INTO sort (Col1, Col2) VALUES (8, 'G');
INSERT INTO sort (Col1, Col2) VALUES (9, 'K');
INSERT INTO sort (Col1, Col2) VALUES (10, 'J');
INSERT INTO sort (Col1, Col2) VALUES (11, 'L')
INSERT INTO sort (Col1, Col2) VALUES (12, 'I');
INSERT INTO sort (Col1, Col2) VALUES (13, 'N');
INSERT INTO sort (Col1, Col2) VALUES (14, 'M');
SELECT * FROM Table1 Limit 0,10;
#按物理顺序取前10个
SELECT * FROM Table1 Limit 10,10;
#按物理顺序从第10开始取10个
取后几个,要先查询了总记录数
SELECT Count(*) As Count FROM Table1;
然后在自己程序里处理成如上所示的SQL
至于按物理顺序逆序取,
呵呵,
:《
---------------------------
$sql = "select cardid,company,name from namecard where";
......
$sql .= " limit $offset,10";
$result2 = mysql_query($sql);
while ($row = mysql_fetch_array($result2))
{
show_card($row);
}
$pages = ceil($num/10);
echo "";
echo "(共{$pages}页)\n\n\n";
if($offset>=10) //$offset大于9,在第二页面以上就会显示前一页
{
$preoffset = $offset-10;
echo "前一页 \n";
}
for ($i=1; $i
{
$newoffset=10*($i-1);
echo "[$i] \n";
}
if ($pages!=0 && ($offset/10+1)
{
echo "下一页 \n";
}
echo "";
------解决方案--------------------
假设表的名称为A,记录条数据为未知.
各个问题最好用一条SELECT 实现
1.取出表中前10条记录.(按物理逆序又应如写)
2.取出表中的后10条记录.(按物理逆序又应如写)
3.取出表中第10条到第20条记录.(按物理逆序又应如写)
4.取出表中的所用记录,并加上两列分别是行号及物理行号.
---------------------------
1、select * from a limit 0,9
2、select top(10) from a order by id desc
3、select * from a limit 9,19
---------------------------
CREATE TABLE sort ('Col1' INT (3) UNSIGNED NOT NULL, 'Col2' CHAR (1) NOT NULL, PRIMARY KEY (Col1),INDEX Col2 (Col2));
INSERT INTO sort (Col1, Col2) VALUES (1, 'A');
INSERT INTO sort (Col1, Col2) VALUES (2, 'C');
INSERT INTO sort (Col1, Col2) VALUES (3, 'B');
INSERT INTO sort (Col1, Col2) VALUES (4, 'D');
INSERT INTO sort (Col1, Col2) VALUES (5, 'F');
INSERT INTO sort (Col1, Col2) VALUES (6, 'E');
INSERT INTO sort (Col1, Col2) VALUES (7, 'H');
INSERT INTO sort (Col1, Col2) VALUES (8, 'G');
INSERT INTO sort (Col1, Col2) VALUES (9, 'K');
INSERT INTO sort (Col1, Col2) VALUES (10, 'J');
INSERT INTO sort (Col1, Col2) VALUES (11, 'L')
INSERT INTO sort (Col1, Col2) VALUES (12, 'I');
INSERT INTO sort (Col1, Col2) VALUES (13, 'N');
INSERT INTO sort (Col1, Col2) VALUES (14, 'M');
SELECT * FROM Table1 Limit 0,10;
#按物理顺序取前10个
SELECT * FROM Table1 Limit 10,10;
#按物理顺序从第10开始取10个
取后几个,要先查询了总记录数
SELECT Count(*) As Count FROM Table1;
然后在自己程序里处理成如上所示的SQL
至于按物理顺序逆序取,
呵呵,
:《
---------------------------
$sql = "select cardid,company,name from namecard where";
......
$sql .= " limit $offset,10";
$result2 = mysql_query($sql);
while ($row = mysql_fetch_array($result2))
{
show_card($row);
}
$pages = ceil($num/10);
echo "";
echo "(共{$pages}页)\n\n\n";
if($offset>=10) //$offset大于9,在第二页面以上就会显示前一页
{
$preoffset = $offset-10;
echo "前一页 \n";
}
for ($i=1; $i
{
$newoffset=10*($i-1);
echo "[$i] \n";
}
if ($pages!=0 && ($offset/10+1)
{
echo "下一页 \n";
}
echo "";
------解决方案--------------------
相关文章
相关视频