How can I combine multiple tables of mysql (to create a flowing news feed), but still tell apart the records?

Question

Hello there people of the internet!

I'm building an application in

php

(oop) and

mysql

db, that has a

'News Feed'

as its main page.

I have in my

MySQL

database tables for

announcements

,

events

,

notifications

etc. which I want to combine in the following way:
I want to join all these tables (that only have in-common a

datetime

field) to create a flowing and centeral news feed, which is mixed and ordered by the

datetime

field, but still know which type is each record.

The reason is- I want to create a different layout, and display different details for each record type (just like in facebook - where you have statuses, photos, events, articles and what not in the news feed).

As much that I'm sure that I'm not the only one asking this - I have no clue how to search for something like this in the internet (since it took me all these lines to explain the problem..).

My desired outcome is this (just for example purposes):

<!-- Announcement = red block !-->

-------------------------------------------------

[title]                 [post date=12.9.15 16:40]

[author]

-------------------------------------------------

[content]

-------------------------------------------------



<!-- Event = blue block !-->

-------------------------------------------------

[title]             [creation date=12.9.15 12:55]

[place]                 [start-time] - [end-time]

-------------------------------------------------

[description]

-------------------------------------------------

10 people are going..

-------------------------------------------------



<!-- Announcement = red block !-->

-------------------------------------------------

[title]                 [post date=12.9.15 11:23]

[author]

-------------------------------------------------

[content]

-------------------------------------------------

Thanks in advance for each of you that took time to read this question and help me.
Peace.

EDIT: by the way, im using smarty template engine, if it does any difference.

Answer 1

HOW DID I SOLVED THIS PROBLEM? this is my 'thank you' to stack overflow:)

[ INSERT COFFEE POT PIC HERE ]

I built an ORM abstract class that connects to the DB and has an interface such that the derived object is constructed by just entering its table in the db, primary-key column, etc..
In this ORM abstract class I have a function that fetches all the records in the specific table. It's a static function called all(). It works in a way that for each record in the table it creates an object of its kind and pushes it into an array. At the end, I get an array filled with object that represent the entire table data.
To create the feed - I took the two modules I wanted - announcements and events, created each class respectively - Announcement and Event, each one fetches data from its own table in the db.
I used array_merge to merge the results of Announcement::all() and Event::all(). Now I have an array which contains both all announcements and events in the db.
Now, I need to sort these posts by their date. I made in the ORM abstract class a magic method to do this -> __get, that returns the specific field I needed from the record details. Now I can use usort to sort the array by its data.
Final fix - I wanted to know the type of each post so I can have a different layout for each one. I solved it with instanceof that is supported by smarty.

FINAL RESULT:

private function get_feed_content()
{
    $feed = array_merge(Announcement::all(), Event::all());

    usort($feed, function($a, $b)
    {
        return (strtotime($b->create_date) - strtotime($a->create_date));
    });

    return $feed;
}

Simple & elegant, just the way I wanted it to be. :)

Hope I inspired someone. Thanks for all those who read it and tried to help me.

How can I combine multiple tables of mysql (to create a flowing news feed), but still tell apart the records?

Sign up