I'm creating a social network. I want to have a user be able to subscribe to people. In theory, when a user clicks the subscribe button, PHP would add that user's ID to an array in their MYSQL listing under the column "subscribed-to". Now, I want to have a news feed style home page. I want to know, if I pull the "subscribed-to" data via PHP, how could I show only the posts (also in a MYSQL table) of the people the user is subscribed to?
Asked
Active
Viewed 145 times
2 Answers
1
Use UNION to combine all the posts from different tables, then use the "IN" clause to get all the userID's you want to follow based on the current userID:
Example:
$userFavs = "SELECT userID FROM favs WHERE followerID = '#loggedinUserID#'"
mysql_query("SELECT 'wall' as type, body, date FROM wall WHERE userID IN ($userFavs)
UNION
SELECT 'photo' as type, caption, date FROM photos WHERE userID IN ($userFavs) UNION... etc
ORDER BY date DESC");
If you run the query thru mysql_fetch_array() it'll return something like this:
Array => {
[0] => { type = "wall", body = "This is a wall update", date = "1234567890" },
[1] => { type = "photo", caption = "Us at the beach", date = "..." },
...
}
You need to make sure that you have the same amount of columns in each SELECT statement everytime you call "UNION", otherwise it will not work. You can /fake/ columns as I have done with the type column above.
Moe
- 4,744
- 7
- 28
- 37
-
Okay. Thanks for that info. I have the basic idea now, but I'm very beginner. All I want for now is text statuses. I'm assumng that all I have to do is use `$userFavs = "SELECT userID FROM favs WHERE followerID = '#loggedinUserID#'" mysql_query("SELECT 'wall' as type, body, date FROM wall WHERE userID IN ($userFavs) ORDER BY date DESC"); ` – Sam Clark Apr 12 '12 at 02:03
-
Yep. That will work. Make sure you use the correct indexes and caching though, otherwise that will probably be a very slow query. – Moe Apr 12 '12 at 02:48
