Forum Moderators: coopster
I'm hoping someone could shed a bit of light onto this issue for me. I'm trying to add MySpace like bulletin system to my site for our users to use and to make site wide announcements.
Right now I have a table that stores all the user info and a table that stores friend info. The friend table just stores the user_id of each of the friends. Here is a simplified version of the db:
Users
-----
used_id
name
etc.
Friend
-----
user_id
friend_id
Bulletin
-----
user_id
message
My problem isn't in creating the actual bulletin, it is in displaying them. This could be just as easy as querying all your friend user_ids and then for each user ID query the bulletin table and check to see if this user has posted a bulletin. This seems to be very db intensive. I for example have over 200 friends. So it'd be 1 query to get my friends user ids and then another 200+ to check each bulletin.
I also could just make one long query but I guess I just don't know how long is too long.
What I am looking for is a less db intensive. Does anyone have any solutions or ideas? These bulletins are going to be included on every page and I would hate to have to make that many db calls.
Please guide me and thanks in advance.
M.Kris
****************************** Method 1 ************************
//first we select all your friends.
$result1 = mysql_query("select friend_id from friend where user_id=$user_id");
//second we start with a basic query string and selecting the first friend
$content = mysql_fetch_assoc($result1);
$sql = "select message from bulletin where user_id=".$content['friend_id'];
//and lastly we select the rest of your friends by concatenation(sp?)
while($content = mysql_fetch_assoc($result1)){
$sql.= " or user_id=".$content['friend_id'];
}
**************** Method 2 ******************************************
//one magical query (hope it works ^_^)
$sql = "select t1.friend_id, t2.message from friend t1, bulletin t2 where t1.user_id = $user_id and t2.user_id = t1.friend_id";
