Help with a tricky query

Taking data from 2 tables to make one result

[nfs2]

Here's what im trying to do. Picture a site like myspace or livejournal, and you have a blog, and you have friends. Now, you want to see which of your friends have recently updated their blog. Im trying to make a query for my site that will return that info.

So i have to get information from 2 tables. The "journal_entries" table to see when the latest entries were made, and the "friends" table, so i only return results from people who are your friend.

$result7=mysql_query("SELECT friend_id FROM friends WHERE user_id = '$username'");

//this gets the names of your friends. As you can see, the query is unused so far.

$result8=mysql_query("SELECT journal_id, MAX(entry_timestamp) AS entry_timestamp FROM journal_entries GROUP BY journal_id ORDER BY entry_timestamp DESC LIMIT 0, 5");

//this gets all the latest entries for everybody on the site, not just your friends. To limit the results from $result8 to only those names that turned up from $result7, how would i put that?

Or am i going about this all wrong?

[Habtom]

I am not sure about your structure, but you need to use your friends ID from $friendid = mysql_fetch_row($result7) and use the $friendid in the other query in WHERE clause of the journal query like friend = $friendid.

But really it depends on the structure you have.

Let me know if I have been any help.

Habtom

[nfs2]

Hi. I changed my query to;

$result7=mysql_query("SELECT friend_id FROM friends WHERE user_id = '$username'");
$friendid = mysql_fetch_row($result7);

$result8=mysql_query("SELECT journal_id, MAX(entry_timestamp) AS entry_timestamp FROM journal_entries WHERE journal_id = '$friendid' GROUP BY journal_id ORDER BY entry_timestamp DESC LIMIT 0, 5");

But it returns no results. Any other ideas?

[Habtom]

// journal_id = '$friendid'

It should look like friend_id = '$friendid'

Change the friend_id to the field storing friend_id.

Tell us the structure of the two tables(all the fields).

Habtom

[nfs2]

Thats the problem. The table "journal_entries" doenst have a "friend_id" feild (and logically, it shouldnt). The journal_entries table has a lot of feilds, but the important ones are journal_id (the username of the owner) and entry_timestamp.

The friends table has friend_id (the username of your friends) and user_id (your username). It also has pending_id but its not relavent here.

[nfs2]

Keep in mind, the result from the first query ($result7) will be more then one row. You could have say 10 friends for example. So when i query the journal_entries table, i need to get more then one journal_id (the usernames of your friends)

[nfs2]

after toying around wih it, i came up with the following code that works;

$result7=mysql_query("SELECT friend_id FROM friends WHERE user_id = '$username'");
$f = array();

while ($i = mysql_fetch_array($result7)) {
$f[] = $i['friend_id'];
}

$f1 = $f[0];
$f2 = $f[1];
$f3 = $f[2];
$f4 = $f[3];
$f5 = $f[4];

$result8=mysql_query("SELECT journal_id, MAX(entry_timestamp) AS entry_timestamp FROM journal_entries WHERE journal_id = '$f1' ¦¦ journal_id = '$f2' ¦¦ journal_id = '$f3' ¦¦ journal_id = '$f4' ¦¦ journal_id = '$f5' GROUP BY journal_id ORDER BY entry_timestamp DESC LIMIT 0, 5");

$result8 now returns what i want.