I have two different tables with different structures. When user does action A, a row gets inserted into table A in respect to the action, the same way for table B. When user does action B, a row gets inserted into table B.
Now I have a news feed that I want to show both these actions to other friends. How do I query both tables with different structure using MySQL, and then use PHP to construct update posts in a time sorted manner?
Just like in Facebook, A user update on his relationship status is different than a post he makes and attach a photo to it but they both appear in the same news feed although their structures and the type of data they carry is different. I appreciate if someone can shed some light on this and discuss a possible solution.
The general approach would be something like this:
SELECT
'A' AS ActionType,
ActionA_PK AS ActionPK,
ActionA_Date AS ActionDate,
<a description of action A> AS ActionDesc
FROM ActionA
UNION SELECT
'B',
ActionB_PK,
ActionB_Date,
<a description of action B>
FROM ActionB
ORDER BY ActionDate DESC
That folds the different actions together and orders them by date, with the most recent first. You can display them on the front end, and because you have their type (A or B) and their primary key you can identify them when posting back for processing when their link or button or whatever is clicked.
There's a SQLFiddle example here.
The another SQLFiddle example here which explains how the tables don't need to have the same structure to make this work.