php - 在php while循环中对输出mysqli数据进行排序

Question

我想显示两个(稍后更多)表的最后 10 行。此刻我坚持以正确的方式排序(atm 使用 2 个表)。每个表都有 uuid 作为对用户的引用。

表 1:

+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field     | Type        | Null | Key | Default           | Extra                       |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| id        | int(11)     | NO   | PRI | NULL              | auto_increment              |
| uuid      | varchar(36) | NO   |     | NULL              |                             |
| text      | text        | NO   |     | NULL              |                             |
| server    | text        | NO   |     | NULL              |                             |
| timestamp | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-------------+------+-----+-------------------+-----------------------------+
5 rows in set

表 2:

+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field     | Type        | Null | Key | Default           | Extra                       |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| id        | int(11)     | NO   | PRI | NULL              | auto_increment              |
| uuid      | varchar(36) | NO   |     | NULL              |                             |
| text      | text        | NO   |     | NULL              |                             |
| entry     | text        | NO   |     | NULL              |                             |
| timestamp | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-------------+------+-----+-------------------+-----------------------------+
5 rows in set

当前代码:

$T1SelQ = "SELECT * FROM `table1` WHERE `uuid` = '$UserUUID' ORDER BY `id` DESC LIMIT 5";
$T1Q = mysqli_query($connect_c, $T1SelQ) OR DIE(mysqli_error($connect_c));
$T1NumR = mysqli_num_rows($T1Q);

$T2SelQ = "SELECT * FROM `table2` WHERE `uuid` = '$UserUUID' ORDER BY `id` DESC LIMIT 5";
$T2Q = mysqli_query($connect_c, $T2SelQ) OR DIE(mysqli_error($connect_c));
$T2NumR = mysqli_num_rows($T2Q);

if ($T1NumR >= 1 OR $T2NumR >= 1) {
    echo '<table>
            <tr>
                <th>#</th>
                <th>Date</th>
                <th>Extra</th>
                <th>Text</th>
            </tr>';
}
if ($T1NumR >= 1) {
    while ($T1Fetch = mysqli_fetch_array($T1Q)) {
        $total++;
        $Date = date('d.m.Y H:i:s', strtotime($T1Fetch['timestamp']));
        $Server = $T1Fetch['server'];
        $Message = $T1Fetch['text'];
        if ($T1NumR >= 1) {
            echo '<tr>';
            echo '<td>'. $total .'</td>';
            echo '<td>'. $Date .'</td>';
            echo '<td>'. $Server .'</td>';
            echo '<td>'. $Message .'</td>';
            echo '</tr>';
        }
    }
}

if ($T2NumR >= 1) {
    while ($T2Fetch = mysqli_fetch_array($T2Q)) {
        $total++;
        $Date = date('d.m.Y H:i:s', strtotime($T2Fetch['timestamp']));
        $Entry = $T2Fetch['entry'];
        $Message = $T2Fetch['text'];
        if ($T2NumR >= 1) {
            echo '<tr>';
            echo '<td>'. $total .'</td>';
            echo '<td>'. $Date .'</td>';
            echo '<td>'. $Entry .'</td>';
            echo '<td>'. $Message .'</td>';
            echo '</tr>';
        }
    }
}

echo '</table>';

实际输出:

#1 | 2018-03-24 12:42:21 | Server-1 | This is some text
#2 | 2018-03-24 12:42:23 | Server-1 | waaay blocked?!
#3 | 2018-03-24 12:42:22 | sh*t | shit
#4 | 2018-03-24 12:42:24 | sh*t happens | shit happens

预期输出示例:

#1 | 2018-03-24 12:42:21 | Server-1 | This is some text
#2 | 2018-03-24 12:42:22 | sh*t | shit
#3 | 2018-03-24 12:42:23 | Server-1 | waaay blocked?!
#4 | 2018-03-24 12:42:24 | sh*t happens | shit happens

(我知道，准备好的语句；从不使用 * 但这目前只能由我自己访问)

Answer 1

您可以将查询与 union all 结合起来，让 MySQL 完成繁重的工作，而不是在 PHP 中处理这个问题:

SELECT * FROM table1
UNION ALL
SELECT * FROM table2
ORDER BY `timestamp` DESC
LIMIT 10