Help with MySpace like bulletin system

Trying to reduce queries

[madk]

Hello all,

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!

[Demaestro]

If I understand you correctly and the friends table is a many to many table then something like this should be what you need.

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))

[madk]

It takes me a while to wrap my head around these complex queries but I am going to try it out.

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.

[madk]

Okay I think I got it working. I had a stray comma that was messing things up. Thanks loads!

[Demaestro]

Nice... that should be what you need then.

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.

[madk]

Demaestro,

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.

[Demaestro]

A union should work.....

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