PHP MySQL排序顺序ASC / DESC仅用于显示的记录

Question

I have a query that returns close to a 1000 records. Using pagination, I'm showing a 100 records per page. Great...no problem. I can also sort by last name or first name in either ascending of descending order. ok so far. The first page returns records for last name starting with A to C. The problem I'm having is that when I click last name to descend I get records with last name starting with Z. The records at the end of my query, I want to get results going from C to A (what is shown on my first page...repeating the same functionality in each page.

Here is what I got...

$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>";

}

Answer 1

To what I was saying in comment.. BTW I'm on my mobile phone so this may be unformatted and or take a while...

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