How do i get data from three different tables and list together ordering by date?

[eluser]Andy UK[/eluser]
Good evening all,

This is the first time I've had to do this so I would appreciate some help.

I have a client table and the individual clients have different events linked to them via their id. For example, a client will have notes, requests to view properties and a viewings log. These three types of events each have their own table. One for notes with a certain set of fields, another table for requests to view properties and a third for logging the viewings.

Normally if I wanted to list only the viewing requests made by a client I would just query the database, ordering by date and filtering by client id and the use a foreach() to loop through the results. Simple.

What i need though is to mix notes, viewing requests and viewing logs all together and sort by date. Something like this:

Client John Smith
---------------

Jan 25th. Viewed property #555. Comments...

Jan 24th. Requested viewing property #555

Jan 20th. Note - Client called office

Jan 19th. Viewed property #434. Comments...

Jan 18th. Requested viewing property #434


As you can see, it's not a table as such with similar columns. It's a list of events related to that particular client, but the events come from three different tables with different data requirements.

How should i tackle this situation? I Just need to be pointed in the right direction and i can do the rest!

Thanks in advance

[eluser]CroNiX[/eluser]
By using JOIN. http://www.w3schools.com/sql/sql_join.asp

[eluser]Andy UK[/eluser]
Thanks CroNiX, but JOIN isn't what I'm looking for. The data from the three different tables doesn't need to be mixed together as though it were one single table. These are disparate blocks of information that i wish to list in chronological order. In other words, first there might be two rows from table one, then a row from table three, then another row from table one and then a row from table two, etc. While tables may share some similar fields, the data will be outputted to individual divs with different layouts depending on which table it came from. The trick is have the divs appear in date order.

[eluser]jonez[/eluser]
If I understand correctly, the three tables have different sets of columns and that's why you can't use a JOIN or UNION to merge them into a single record set. If that assumption is correct, perform 3 queries separately then merge the array's. As long as they all include a date field (with the same name, may need to alias them in queries) you can do an array sort on the merged array to order them all as needed.

[eluser]Andy UK[/eluser]
Thanks jonez, you understood correctly! I suspected that arrays might be the way to go...

Could you expand on the part where i merge the arrays? Up until now I've been returning results as objects, looping through with foreach and accessing like this: $row->value. I know I can also return result sets as arrays, so all good there. I'm just not clear on how I can merge the arrays together.

Thanks again!