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

Question

I want to display the 10 last rows from two (later more) tables. At this moment I'm stuck at sorting the correct way (atm using 2 tables). Every table has uuid as reference to the user.

table 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

table 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

Currently code:

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

Actual output:

#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

Expected output example:

#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

(I know, prepared statements; never use * but this is currently only accessable by myself)

Answer 1

Instead of handling this in PHP, you could combine the queries with union all and let MySQL do the heavy lifting:

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