php - 按表头对 MySql 行进行排序

Question

我有一个表，其中包含从此 MySql 语句检索的数据:

$sql = "SELECT log_date, log_id, log_time, network_protocal, client_name, client_ip FROM log_table";

我希望能够通过单击表标题(或它们旁边的向上和向下箭头，这就是我现在的方式)来按表标题进行排序。

这是我的 HTML:

<?php

$sort = "";

$sql = "SELECT log_date, log_id, log_time, network_protocal, client_name, client_ip
FROM log_table $sort";

if(isset($_GET['sort'])) {
    switch ($_GET['sort'] ) {
        case 0:
            $sort = " ORDER BY log_date ASC";
            break;
        case 1:
            $sort = " ORDER BY log_date DESC";
            break;
        case 2:
            $sort = " ORDER BY log_time ASC";
            break;
        case 3:
            $sort = " ORDER BY log_time DESC";
            break;
        case 4:
            $sort = " ORDER BY network_protocal ASC";
            break;
        case 5:
            $sort = " ORDER BY network_protocal DESC";
            break;
        case 6:
            $sort = " ORDER BY client_name ASC";
            break;
        case 7:
            $sort = " ORDER BY client_name DESC";
            break;
        case 8:
            $sort = " ORDER BY client_ip ASC";
            break;
        case 9:
            $sort = " ORDER BY client_ip DESC";
            break;
    }
}

$sql = "SELECT log_date, log_id, log_time, network_protocal, client_name, client_ip
FROM log_table $sort";
$query = $this->db->prepare($sql);
$query->execute();
$query->fetchAll();
?>

<div class="container">

<table class="table table-striped">
    <tr>
        <td><a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=0";?>" >&#9650;</a>Date<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=1";?>" >&#9660;</a></td>
        <td><a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=2";?>" >&#9650;</a>Time<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=3";?>" >&#9660</a></td>
        <td><a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=4";?>" >&#9650;</a>Network Protocol<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=5";?>" >&#9660</a></td>
        <td><a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=6";?>" >&#9650;</a>Client Name<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=7";?>" >&#9660;</a></td>
        <td><a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=8";?>" >&#9650;</a>Client IP<a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=9";?>" >&#9660</a></td>
    </tr>
    <?php foreach ($logs as $log) { ?>
        <tr>
            <td>
                <?php if (isset($log->log_date)) echo (string)$log->log_date; ?>
            </td>
            <td>
                <?php if (isset($log->log_time)) echo (string)$log->log_time; ?>
            </td>
            <td>
                <?php if (isset($log->network_protocal)) echo (string)$log->network_protocal; ?>
            </td>
            <td>
                <?php if (isset($log->client_name)) echo (string)$log->client_name; ?>
            </td>
            <td>
                <?php if (isset($log->client_ip)) echo (string)$log->client_ip; ?>
            </td>
        </tr>
    <?php } ?>
</table>
</div>

当我单击向上和向下箭头对网址进行排序时，URL 正确显示，例如“index.php?sort=3”，但没有任何反应。有谁知道我做错了什么？

Answer 1

使用 (`) 作为参数，如下所示:

SELECT `log_date`,` log_id`, `log_time`, `network_protocal`, `client_name`, `client_ip`
FROM `log_table` ORDER BY `log_time` DESC`