PHP MySQL仅对显示的记录按ASC/DESC排序 - PHP MySQL sort order ASC/DESC for only the records shown

PHP MySQL sort order ASC/DESC for only the records shown

本文关键字：ASC DESC 排序记录 MySQL 显示 PHP | 更新日期: 2023-09-27

我有一个返回近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