组合多个唯一的MySQL表并按一列排序

Question

I've been trying to accomplish this MySQL query for the past few days now with very little luck. I'd like to combine these multiple tables and their columns, then order by one that they have in common (not by name, but by content). I have the following database tables:

mb_bans:

mb_ban_records:

mb_kicks:

mb_mutes:

mb_mutes_records:

mb_warnings:

What I'm trying to accomplish is something among the lines of this:

Unfortunately I am beyond stumped on how to combine these MySQL tables together but also at the same time keeping them in separate categories - ordered by the _time columns in each table. How would I approach this? I have been unsuccessful with my attempts at retrieving them.. The closest I could get was combining just the _time columns of each, then giving it a value in the query as "date", however I cannot do much with the results. I would still need to call it as the individual rows, correct? I could probably use fetchAll but then I would be unable to add anything to the values..

$banq = $db->prepare('SELECT banned, banned_by, ban_reason, ban_time, ban_expires_on FROM '.BAN_TABLE.' ORDER BY ban_time DESC');
$kickq = $db->prepare('SELECT kicked, kicked_by, kick_reason, kick_time FROM '.KICK_TABLE.' ORDER BY kick_time DESC');
$muteq = $db->prepare('SELECT muted, muted_by, mute_reason, mute_time, mute_expires_on FROM '.MUTE_TABLE.' ORDER BY mute_time DESC');
$warnq = $db->prepare('SELECT warned, warned_by, warn_reason, warn_time FROM '.WARN_TABLE.' ORDER BY warn_time DESC');
$banq->execute();
$kickq->execute();
$muteq->execute();
$warnq->execute();

Essentially I'd like to combine all of those queries together as one + the two _record tables. Any advice that could help me is greatly appreciated as I've spent countless hours trying to figure this out on my own.

Thanks in advance!

Answer 1

Try this:

SELECT * from (
SELECT banned as Punisher, banned_by as Punished, ban_reason as Reason, ban_expires_on  as Expire, ban_time as Date  FROM mb_bans 
UNION
SELECT kicked as Punisher, kicked_by as Punished, kick_reason as Reason, NULL  as Expire, kick_time as Date FROM mb_kicks 
UNION
SELECT muted as Punisher, muted_by as Punished, mute_reason as Reason, mute_expires_on  as Expire, mute_time as Date  FROM mb_mutes 
UNION
SELECT warned as Punisher, warned_by as Punished, warn_reason as Reason, NULL  as Expire,  warn_time as Date FROM mb_warnings
) d order by d.Date DESC;

EDIT

how could I get the type of record? (IE. whether the returned result is from the bans table, mutes table, kicks table etc.)

SELECT * from (
    SELECT banned as Punisher, banned_by as Punished, ban_reason as Reason, ban_expires_on  as Expire, 'ban' as TableType, ban_time as Date  FROM mb_bans 
    UNION
    SELECT kicked as Punisher, kicked_by as Punished, kick_reason as Reason, NULL  as Expire, 'kick' as TableType, kick_time as Date FROM mb_kicks 
    UNION
    SELECT muted as Punisher, muted_by as Punished, mute_reason as Reason, mute_expires_on  as Expire, 'mute' as TableType, mute_time as Date  FROM mb_mutes 
    UNION
    SELECT warned as Punisher, warned_by as Punished, warn_reason as Reason, NULL  as Expire,  'warn' as TableType, warn_time as Date FROM mb_warnings
    ) d order by d.Date DESC;