PHP MySQL排序顺序ASC/DESC只显示记录
问题描述:
我有一个查询返回接近1000条记录。使用分页,我每页显示100条记录。很好...没问题。我也可以按降序升序排列姓氏或名字。好吧到目前为止。第一页返回姓氏从A开始到C的记录。我遇到的问题是,当我单击姓氏下降时,我得到的姓氏以Z开头的记录。我查询结束时的记录,我想得到的结果会从C到A(什么是显示我的第一页上...重复在每一页相同的功能。PHP MySQL排序顺序ASC/DESC只显示记录
这里是我...
$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>";
}
答
要我是什么在评论中说.. BTW我在我的手机,所以这可能是未格式化,或需要一段时间...
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
+0
要使结果集确定,在“LIMIT”子句之前的内联视图“t”中应该有一个“ORDER BY”子句。 ORDER BY子句总是ASC,不会被切换。 – spencer7593 2014-10-07 03:55:43
请确保你是在放置desc之前限制查询! Aka通过推杆查询使限制成为子查询和顺序 – 2014-10-07 01:52:55