CODEWITHSUNDEEP
phpmysqljoin
JeremyW
JeremyW

Reputation: 743

Sorting content from different MySQL tables by date

I have 3 different tables :

I'm trying to create a timeline on my website to display all the content sorted by the pubdate from my 3 tables in a unique list. Is there a way to do that in MySQL ?

Maybe creating a 'general' table that would include all the ids sorted by date from my 3 other tables? I should then link all the element with PHP. Is it not too much power consuming ?

Upvotes: 1

Views: 555

Answers (1)

J.D. Pace
J.D. Pace

Reputation: 616

Try this and see if it will meet your needs:

SELECT
    `news_id`      AS `id`,
    `news_content` AS `content`,
    `news_pubdate` AS `pubdate`,
    ""             AS `person`,
    "news"         AS `type`
FROM `news`

UNION
SELECT
    `song_id`      AS `id`,
    `song_link`    AS `content`,
    `song_pubdate` AS `pubdate`,
    ""             AS `person`,
    "song"         AS `type`
FROM `song`

UNION
SELECT
    `interview_id`      AS `id`,
    `interview_content` AS `content`,
    `interview_pubdate` AS `pubdate`,
    `interview_artist`  AS `person`,
    "interview"         AS `type`
FROM `interview`

ORDER BY `pubdate`;

Upvotes: 3

Related Questions