user1468448 user1468448 - 2 years ago 76
SQL Question

Three different tables (sql) show like one content

My question is simple. How to show content from three tables on one page together ordered by date.

It's a blog where i have posts like Articles, Statuses and Quotes.

ARTICLE - Id, Title, Photo, Text, Type, Datetime (php - time())

STATUS - Id, Title, Text, Type, Datetime (php - time())

QUOTE - Id, Quote, Author, Type, Datetime (php - time())

I need to show the content on page ordered by Datetime. Then i can change the html/css by Type of post.

It'll be better to Join tables or show it in Array?

Thanks for any help.

Answer Source

If you want to do the sorting in the MySQL you need to unify fields from three tables and do UNION ALL, not JOIN. You can use join when you have some relation between three, as far as I can see those tables are not related.

Other option for you is to merge results from those three queries in an array.

$merged = array();

foreach($articles as $k => $article){
    $merged[$article['Datetime']] = array('table' => 'articles', 'id' => $k);
foreach($statuses as $k => $statuse){
    $merged[$statuse['Datetime']] = array('table' => 'statuses', 'id' => $k);
}foreach($quotes as $k => $quote){
    $merged[$quote['Datetime']] = array('table' => 'quotes', 'id' => $k);


foreach ($merged as $key => $value)
    $arr = $$value['table'];
    echo $arr[$value['id']]; 

Of course you can do some refactoring but this snippet shows in short how you can do it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download