dsbqfrr098575666
2014-10-07 01:34 阅读 125
已采纳

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

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

}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    drtldt55533 drtldt55533 2014-10-07 01:57

    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
    
    点赞 评论 复制链接分享

相关推荐