Sorting output mysqli data in php while-loop

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)

Checkout SQL's UNION ALL. That allows you to combine results from two queries into one result set. — M. Eriksson
– M. Eriksson, Commented Mar 24, 2018 at 11:54

Mureinik · Accepted Answer · 2018-03-24 11:58:43Z

4

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

answered Mar 24, 2018 at 11:58

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

K4XN Over a year ago

Thanks for your answer @Mureinik, If I want to sort by uuid, I have to add bevor "UNION ALL" the where-statement, correct?

Mureinik Over a year ago

@Kaan2106 I don't understand what you're asking. If you want to order by the uuid instead of timestamp, just change the order by clause

K4XN Over a year ago

No, by timestamp is correct, I just wanted to use "uuid" to select one user instead of all users. Thats why I'm asking where to put the Where statement.

Mureinik Over a year ago

@Kaan2106 you'd need a wrapper query to apply the where clause:

select * from (select * from table1 union all select * from table2) t where uuid = 'someid' order by timestamp desc limit 10

.

K4XN Over a year ago

Thank you very much, @Mureinik. Works like a charm.

Collectives™ on Stack Overflow

Sorting output mysqli data in php while-loop

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related