我有一个返回近1000条记录的查询。使用分页,我显示每页100条记录。太棒了没问题。我也可以按姓氏或名字升序或降序进行排序。到目前为止还可以。第一个页面返回以A到C开头的姓氏记录。我遇到的问题是,当我单击姓氏下降时,我会得到以Z开头的姓氏的记录。查询结束时的记录,我想得到从C到A的结果(第一个页面上显示的内容……在每个页面中重复相同的功能。
这是我得到的。。。
$orderColumn = 'lastName';
$orderDirection = 'ASC';
if( isset($_POST["oc"]) && $_POST["oc"] !== '' ) { $orderColumn = $_POST["oc"]; }
if( isset($_POST["od"]) && $_POST["od"] !== '' ) { $orderDirection = $_POST["od"]; }
$per_page = 100;
$query = "SELECT * FROM table as t
LEFT JOIN table_2 as t2 ON t.pk_uID = t2.fk_uID
LEFT JOIN table_3 as t3 ON t3.fk_utID = t2.pk_utID
WHERE t3.fk_utID = 7 and t.interviewed = 0";
$result = $db->query($query);
$count = mysql_num_rows($result);
$total = ceil($count/$per_page);
if ($_GET['page']) {
$page = $_GET['page'];
}
$offset = (($page-1)*$per_page);
$query2 = "SELECT firstName as first, lastName as last FROM table
LEFT JOIN table_2 as t2 ON t.pk_uID = t2.fk_uID
LEFT JOIN table_3 as t3 ON t3.fk_utID = t2.pk_utID
WHERE t3.fk_utID = 7 and interviewed = 0 order by $orderColumn $orderDirection LIMIT $offset, $per_page";
$res = $db-> query($query2);
while($row = mysql_fetch_array($res)){
echo "<span style='display: inline-block; width: 15%;'>$row[first]</span>";
echo "<span style='display: inline-block; width: 15%;'>$row[last]</span>";
}
对于我在评论中所说的。。顺便说一句,我在用手机,所以这可能没有格式化,或者需要一段时间。。。
Select what_you_need
From
( select your_inner_select
From table t
LEFT JOIN table_2 as t2 ON t.pk_uID = t2.fk_uID
LEFT JOIN table_3 as t3 ON t3.fk_utID = t2.pk_utID
WHERE t3.fk_utID = 7 and interviewed = 0 LIMIT $offset, $per_page
ORDER BY $orderColumn ASC
)t
order by $orderColumn $orderDirection