SQL Querry of and array of numbers

[dclamp]

Ok. here is what i need/asking: I have to query from a table "members_friends" to get all the friend id's of the friends, then i need to query "bulletins" with the array of member_friends to gett all of the bulletins that the friends of the user have posted.

Sounds confusing. The code that i have now, it horrible. I have no idea how it works, but i do know that is it making the page load way to long. 5 minutes.

[penagate]

Sounds like you are doing work in PHP where it should be done in the SQL.

select * from bulletins where bulletin_author_id in (select friend_id from members_friends where member_id = ?)

Replace field names as appropriate, obviously.

It's probably equally horrible - I have no idea how to use unions or anything. I am not a SQL guy.

[CornedBee]

No unions, this needs a join. I'm assuming that members_friends is an m:n connection table containing two fields:
from INT NOT NULL REFERENCES members,
to INT NOT NULL REFERENCES members

which indicates that from has made to a friend.

SELECT fields, you, need FROM bulletins b INNER JOIN members_friends m ON m.to = b.author
  WHERE m.from = ?

[dclamp]

No unions, this needs a join. I'm assuming that members_friends is an m:n connection table containing two fields:
from INT NOT NULL REFERENCES members,
to INT NOT NULL REFERENCES members

which indicates that from has made to a friend.

SELECT fields, you, need FROM bulletins b INNER JOIN members_friends m ON m.to = b.author
  WHERE m.from = ?

I am quite confused with your code.... can you please explain?

[CornedBee]

I can try.

SELECT fields, you, need

This part should be clear. As a general rule, it is not recommended to do a SELECT *. Always name exactly the fields you need.

FROM bulletins b

We select these fields from the table bulletins (aliased to "b") ...

INNER JOIN members_friends m

... joined with the table members_friends (aliased to "m") ...

ON m.to = b.author

... on the condition that the author of the bulletin post is the friended one of the connection table.

WHERE m.from = ?

Of all the just generated entries (ALL bulletin posts connected to ALL people who friended their authors), take only those where the "friender" is the one you supply as a parameter.

Let's try this in practice.
Assume you have a table "members" with the columns "id", "name", and a few others.
Assume further that you have a table "members_friends" with the columns "from", which is the user who did the friending, and "to", which is the user who was friended.
Finally, you have the table "bulletins", which contains "id", "text", "timestamp", and "author", which contains the user ID of the author of the post.
The tables are filled like so:
MEMBERS
=======
id=1, name=foo
id=2, name=bar
id=3, name=you
id=4, name=him

MEMBERS_FRIENDS
==============
from=1, to=2
from=2, to=1
from=3, to=1
from=3, to=4
from=1, to=3

In this table, "foo" and "bar" friended each other, as did "foo" and "you". "you" friended "him", but "him" didn't friend back.

Now finally, there's the bulletins table. I'm leaving out timestamp and text, as they're not relevant.
BULLETINS
========
id=1, author=1
id=2, author=1
id=3, author=2
id=4, author=2
id=5, author=3
id=6, author=3
id=7, author=4
id=8, author=4

Every member wrote two bulletins.

Now go through the query. We're looking for, let's say, text and timestamp of the bulletins that friends of "you" wrote.

SELECT text, timestamp FROM bulletins b INNER JOIN members_friends m ON m.to = b.author WHERE m.from = 3

So. The first part to evaluate is the FROM clause. It's a join of bulletins and members_friends. It is an inner join, which means that columns in one that have no match in the other fall out. The resulting merged table contains the columns "b.id", "b.text", "b.timestamp", "b.author", "m.to", and "m.from". "b.author" and "m.to" are the join condition, so the resulting merged table contains this:

id text timestamp author/to from
1  ...    ...      1         2
1  ...    ...      1         3
2  ...    ...      1         2
2  ...    ...      1         3
3  ...    ...      2         1
4  ...    ...      2         1
5  ...    ...      3         1
6  ...    ...      3         1
7  ...    ...      4         3
8  ...    ...      4         3

As you see, the posts where the author was friended by two people are now in there twice. Had an author been friended by noone, his posts wouldn't be in there at all.

The next part is the WHERE clause. We only want those rows where the "from" field is 3.

id text timestamp author/to from
1  ...    ...      1         3
2  ...    ...      1         3
7  ...    ...      4         3
8  ...    ...      4         3

Finally we apply the SELECT filter.

text timestamp
...    ...
...    ...
...    ...
...    ...

And you are left with exactly the posts you wanted.

You could now extend the query to also JOIN against the members table and get the authors' names in there, for example. SQL is powerful; it's worth the time really learning it.

[dclamp]

ok here is what i have:

SELECT * FROM bulletins b INNER JOIN members_friends m ON m.friend_id = '".$_SESSION[member_id]."' WHERE b.posted_by = m.member_id

and i am getting this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/jphendr/public_html/logincomplete.php on line 301

[CornedBee]

The query failed, for whatever reason, so what mysql_query returned is not actually a result.

Also, you have the join condition and the where filter the wrong way around in your query.