How to join multiple tables by matching #id and collecting all other data? [closed]

Closed. This question needs details or clarity. It is not currently accepting answers.

---

Want to improve this question? Add details and clarify the problem by editing this post.

Closed 4 years ago.

Improve this question

I have good 开发者_开发技巧5-7 tables. I then have one separate table that has the post_id for each tables content. Now, I want to a user feed similar to Facebook. So how can I join the feed table with lets say photos, articles, videos.

Structure:

//content_id = post_id from all other unique tables

User Feed Table:

id
content_id
user_id
link

Posts:

id
post_id
post_content
tags
user_id

links:

id
post_id
link_content
tags
user_id

link_comments:
id
post_comment_id
post_id
tags
user_id

---

You have to use the MYSQL JOIN-statement. http://dev.mysql.com/doc/refman/5.1/en/join.html

---

You could use join or you could just say content_id = post_id ... the way you specify multiple tables in case you dont know, is like this ....

select f.* from feed f, posts p, links l, contents c where f.content_id = l.post_id etc.. etc ..

---

I would suggest adding an extra field to the feed table for 'type'. This would be the type of content. Everytime someone adds a new post then insert a record into the feed table with content_id set to the id of the post and type=1 (1 could mean post). Then you would construct a query like this:

// get all records from feed
// then foreach record
// $type_id = feed->type

// then do a switch statement
switch($type_id){
    case 1:
        $type="articles";
        break;
    case 2:
        $type="videos"; 
}

// then do another query joining the feed to the content
SELECT * FROM '$type'
JOIN feed ON '$type'.id = feed.content_id
WHERE '$type'.id = feed->content_id

//execute query and add to an array of feed items
//endforeach

I'm sorry my syntax is all wrong this is more of a problem solving answer...