Forum Moderators: open
I posted this in the PHP forum as well but I think my question is more of a database question. I am by no means a MySql expert so please bear with me.
I am trying to reduce the number of queries it take to output my bulletin system I am working on for my users.
I have a user table, a friends table and a bulletin table that look like:
user
---
user_id
name
etc.
friends
---
user_id
friend_id
bulletin
---
user_id
message
What I need to do is pull all the bulletins that are my friends. Right now I am just looking through my friends and then running another query agains the bulletin table to see if they posted anything. I have over 200 friends so this is causing a massive load.
Any suggestions to simplify this will be greatly appreciated. Thanks in advance!
select
bulletin.user_id,
bulletin.message,
user.name,
user.etc
from
user, bulletin
where
user.user_id = bulletin.user_id
and user.user_id in (select friend_id from friends where user_id = $user_id_you_want_to_check))
To clarify things a bit, the friends table looks something like this:
¦ user_id ¦ friend_id ¦
¦ 1 ¦ 2 ¦
¦ 1 ¦ 65 ¦
¦ 1 ¦ 4 ¦
¦ 1 ¦ 9 ¦
The user with the id of 1 is friends with the 4 other users listed. I tried your query but I keep getting a "not a valid MySQL result resource " error.
Thanks for the help so far.
If you need help tweaking it or you aren't getting back everything you think you should be let me know and I will give it another go with you.
Glad to help.
The one thing I would like to change is for the query to include the current users bulletins as well.
I tried adding a simple or statement but it didn't work. Any suggestions.
select
bulletin.user_id,
bulletin.message,
user.name,
user.etc
from
user, bulletin
where
user.user_id = bulletin.user_id
and user.user_id in (select friend_id from friends where user_id = $user_id_you_want_to_check))
Union all
select
bulletin.user_id,
bulletin.message,
user.name,
user.etc
from
user, bulletin
where
user.user_id = bulletin.user_id
and bulletin.user_id = $user_id_you_want_to_check
Let me know if this works
