排序由XXX按ASC或DESC排序，动态排序，MySQL ... [英] order by XXX sort by ASC or DESC , dynamic ordering, mysql...

问题描述

这更像是一个查询而不是一个问题。我想为我的mysql数据库结果创建动态排序。我目前用查询输出结果。我想创建一个2链接，它将按所选链接对结果进行排序，再次单击并链接ASC或DSEC结果。

我一直在搜索但是我无法找到正确的方式。

我甚至下载了一个框架来看看如何完成，但没有成功。
的例子如下所示：

TITLE TOTAL

这听起来很简单，我无法在网上找到一个动态的例子。

其他人发现谷歌提供更多结果过时和论坛比实际有用的页面？
即使你按去年和相关性排序。希望有人能给我一些建议，谢谢

<？php
//构建查询的基础
$ sql ='
SELECT
`id`，
`title`，
`total`
FROM
`my_table`
';

//检查排序字段
$ sort_by = isset（$ _ GET ['s']）？ $ _GET ['s']：false;
//验证排序字段（避免Bobby Tables！）并提供默认的
开关（$ sort_by）{
case'title'：
case'id'：
'总数'：
中断;
默认值：
$ sort_by ='id';
}

$ sql。='ORDER BY'。$ sort_by。'';

//获取方向，或使用默认的
$ direction = isset（$ _ GET ['d']）？ $ _GET ['d']：false;
if（$ direction！='ASC'&& $ direction！='DESC'）
$ direction ='DESC';
$ sql。= $ direction;

//执行查询，得到结果
$ res = mysql_query（$ sql）;
$ results = array（）;
if（$ res）{
while（$ r = mysql_fetch_assoc（$ res））{
$ results [] = $ r;



//在表格标题中用于表示排序方向
$ sort_arrow =（$ direction =='ASC'？'< img src = up_arrow.png/>'：'< img src =down_arrow.png/>'）;

//用于构建网址以反转当前的排序方向
$ reverse_direction =（$ direction =='DESC'？'ASC'：'DESC'）;
？>

< table>
< thead>
< th scope =colclass =<？php echo $ sort_by =='id'？'sortColumn'：'';？>>
< a href =myscript.php？s = id& d =<？php echo $ reverse_direction;？>> ID< / a>
<？php echo $ sort_by =='id'？ $ sort_arrow：''; ？>
< / th>
< th scope =colclass =<？php echo $ sort_by =='id'？'sortColumn'：'';？>>
< a href =myscript.php？s = title& d =<？php echo $ reverse_direction;？>> Title< / a>
<？php echo $ sort_by =='title'？ $ sort_arrow：''; ？>
< / th>
< th scope =colclass =<？php echo $ sort_by =='id'？'sortColumn'：'';？>>
< a href =myscript.php？s = total& d =<？php echo $ reverse_direction;？>>总计< / a>
<？php echo $ sort_by =='total'？ $ sort_arrow：''; ？>
< / th>
< / thead>
< tbody>
<？php
if（count（$ results）> 0）{
foreach（$ results as $ r）{
print'< tr>';
print'< th scope =row>'。$ r ['id']。'< / th>';
print'< td>'。$ r ['title']。'< / td>';
print'< td>'。$ r ['total']。'< / td>';
print'< / tr>';
$ b $}其他{
print'< tr>< td colspan = 3>未找到结果< / td>< / tr>';
}
？>
< / tbody>
< / table>


this is more of a query than a problem. I would like to create dynamic sorting for my mysql database results. I currently output the results with a query. I would like to create a 2 links which would sort the results by that selected link, click again and the link ASC or DSEC the results.

I have been searching but am unable to find the right way about doing this.

I even downloaded a framework to see how this is done but no to success. the example is like follows:

TITLE TOTAL

As simple as this sounds I am unable to find a dynamic example online.

ANyone else finding google supplies more results to outdated and forums than actual helpful pages? Even if you sort by last year and relevance. Hope someone could give me some advice on this, thanks

解决方案

<?php
    // build the basis for the query
    $sql = '
        SELECT 
            `id`,
            `title`,
            `total`
        FROM 
            `my_table`
    ';

    // check for sort field
    $sort_by = isset($_GET['s']) ? $_GET['s'] : false;
    // validate the sort field (avoid Bobby Tables!) and provide default
    switch ($sort_by) {
        case 'title':
        case 'id':
        case 'total':
            break;
        default:
            $sort_by = 'id';
    }

    $sql .= ' ORDER BY '.$sort_by.' ';

    // get the direction, or use the default
    $direction = isset($_GET['d']) ? $_GET['d'] : false;
    if ($direction != 'ASC' && $direction != 'DESC')
        $direction = 'DESC';
    $sql .= $direction;

    // execute query, get results
    $res = mysql_query($sql);
    $results = array();
    if ($res) {
        while ($r = mysql_fetch_assoc($res)) {
            $results[] = $r;
        }
    }

    // used in table heading to indicate sort direciton
    $sort_arrow = ($direction == 'ASC' ? '<img src="up_arrow.png" />' : '<img src="down_arrow.png" />');

    // used to build urls to reverse the current sort direction
    $reverse_direction = ($direction == 'DESC' ? 'ASC' : 'DESC');
?>

<table>
    <thead>
        <th scope="col" class="<?php echo $sort_by == 'id' ? 'sortColumn' : ''; ?>">
            <a href="myscript.php?s=id&d=<?php echo $reverse_direction; ?>">ID</a>
            <?php echo $sort_by == 'id' ? $sort_arrow : ''; ?>
        </th>
        <th scope="col" class="<?php echo $sort_by == 'id' ? 'sortColumn' : ''; ?>">
            <a href="myscript.php?s=title&d=<?php echo $reverse_direction; ?>">Title</a>
            <?php echo $sort_by == 'title' ? $sort_arrow : '';  ?>
        </th>
        <th scope="col" class="<?php echo $sort_by == 'id' ? 'sortColumn' : ''; ?>">
            <a href="myscript.php?s=total&d=<?php echo $reverse_direction; ?>">Total</a>
            <?php echo $sort_by == 'total' ? $sort_arrow : '';  ?>
        </th>
    </thead>
    <tbody>
        <?php
            if (count($results) > 0) {
                foreach ($results as $r) {
                    print '<tr>';
                    print '<th scope="row">'.$r['id'].'</th>';
                    print '<td>'.$r['title'].'</td>';
                    print '<td>'.$r['total'].'</td>';
                    print '</tr>';
                }
            } else {
                print '<tr><td colspan=3>No results found</td></tr>';
            }
        ?>  
    </tbody>
</table>

这篇关于排序由XXX按ASC或DESC排序，动态排序，MySQL ...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！