Complicated database and PHP ordering and combining

[rockinaway]

I have 5 tables.

 

1 is which a user specifies another partner id.

 

The other 4 are different attributes of the partner id, with times for each.

 

Now what I want to do is pull the data from ALL 4 different tables and then 'mix' them together and order by their respective times (and only choose the values matching the partner id).

 

I'm getting mighty confused trying left joins, wheres and anything else I know. It seems too complex to be done :s

[Psycho]

I'm not really following your explanation. Can you give some sample data from the tables and how you want it returned/displayed?

[rockinaway]

I haven't got any sample data as such because i've been doing a plan in writing before i start. However, I'll explain as best as I can.

 

I have a table that lists users. This tables lists ALL the users I have. Users can then make partners. This relationship is then stored in a new table with the user id and the partner id.

 

There are then a few more tables which have updates that the partner has submitted. These are stored here with a time. There are also updates from the primary user in these tables.

 

I want to create a query that will get data from all these tables, based on the the fact that a user is a partner with a person, and then order them by time.

 

The closest example to what i want, that I can think of is the facebook feed. Where data from different tables is pulled and updated based upon whether someone is a friend and then put together and ordered by their time

[jcbones]

Is your database normalized?  This would be your first step.

[Psycho]

Hmm... I would probably have a better idea if I understood what is being stored in the tables for the updates. Do you really need separate tables for them? It would be much easier if you had one table for the "updates" and used a column in that table to indicate the "type" of update.

 

However, it can be done with the separate tables, it is just going to be more complicated. It will be hard to provide an explanation without some context to the actual tables and their structure, but I'll try. It seems you want the data from the "updates" tables to be ordered by their timestamp. But, if all the data is coming from separate tables if you just do JOINS the timestamps will be in separate columns and you can't order all the associated records as you want.

 

So, you need to pull data from the Users/Relationship table a you normally would and then pull data from the "updates" tables as a single result associated with the first - to do this you would use UNION.

 

Here is a mock query that could possibly work.

 

SELECT users.*, 
FROM users
JOIN relationships ON users.id = relationships.user_id
LEFT JOIN (SELECT user_id, activityName, activityDate
           FROM activities
           UNION
           SELECT user_id, userupdateName, updateDate
           FROM updates
           UNION
           SELECT juser_id, obsName, jobsDate
           FROM jobs
           UNION
           SELECT user_id, othersName, othersDate
           FROM others
           ) as events
    ON events.user_id = relationships.user_id
    OR events.user_id = relationships.friend_id

 

But, to be honest I doubt that actually query would work. I would need the tables and to do some testing to figure out the right structure.

[rockinaway]

jcbones - yes thats been done to an extent on paper. im just finding it hard to get my head around how to get it to work through a single or multiple queries (as ordering by time is required)..

 

mjdamato - thanks i'll try that.. well it could be in one table, however there will be A LOT of information and then too many columns etc. as updates wold include images and audio etc.. so i was splitting these up to their own tables and linking back..

[Psycho]

mjdamato - thanks i'll try that.. well it could be in one table, however there will be A LOT of information and then too many columns etc. as updates wold include images and audio etc.. so i was splitting these up to their own tables and linking back..

 

So, do this. Create one table called "updates". That table would have columns for user id, an update date, update type and update id. You can use that table as an intermediary between the user and the various "updates" in the four other tables! Then those four tables can store the data that is unique to each update type. The query becomes much easier then.

 

Something like:

SELECT *
FROM users
JOIN relationships on users.id = relationships.user_id
JOIN updates ON relationships.user_id = updates.user_id
LEFT JOIN audio_updates ON updates.update_type = 'a' AND updates.update_id = audio_updates.id
LEFT JOIN video_updates ON updates.update_type = 'v' AND updates.update_id = video_updates.id
LEFT JOIN text_updates ON updates.update_type = 't' AND updates.update_id = text_updates.id
LEFT JOIN other_updates ON updates.update_type = 'o' AND updates.update_id = other_updates.id

ORDER BY updates.update_date

[rockinaway]

Thats perfect. I was thinking of doing that initially, but I thought there would be too much data in the same table. Okay, I want to complicate things further. What if, as well as the partner's updates, I want to get updates from the user as well.. (i.e. using user id)... or is this one step too far?

[rockinaway]

Also, another question, how can I access all the different values? Would it just be using mysql_fetch_array? Or would I have to use different keys etc based on which table?