mysql_query SELECT FROM table_1 ORDER BY table_2?

[shortysbest]

I want to select all favorite friends, but I want to order them in order from who has posted something most recent.

 

 

$fav_query = mysql_query("SELECT * FROM favorite_friends WHERE user_id='$session' ORDER BY id");

 

Posts table name is "posts"

The row that it should be ordered by is "id"

[voip03]

Order by date desc /asc

[shortysbest]

I'm selecting from two tables, table favorite_friends, and table posts.

 

Right now it just orders it by the id of the favorite_friends, but i want it to order by the id/date of latest posts.

 

This is the first part of my script right now:

$fav_query = mysql_query("SELECT * FROM favorite_friends WHERE user_id='$session' ORDER BY id");
while($row = mysql_fetch_array($fav_query))
{
$friend_id = $row['friend_id'];

$lastPost = mysql_fetch_assoc(mysql_query("SELECT * FROM posts WHERE from_id='$friend_id' AND to_id='$friend_id' ORDER BY id DESC LIMIT 1"));

 

 

however both queries need to be combined into one, So the result of the 1st query can be ordered by the 2nd query

[voip03]

http://www.tizag.com/mysqlTutorial/mysqljoins.php

or use 'join'

[shortysbest]

I have tried that before but I still have the problem, sort of, using the query below it does order them in the correct order however it prints out many many more rows then it should.

 

For instance, user 1 has 4 favorited friends, so the query should only print out those four results in the order of their posts, however I suspect it is printing out them the number of times as posts the user has made, so if user 6 is on user 1 favorite list and user 6 has made 10 posts, it prints his name 10 times, etc.

 

$fav_query = mysql_query("SELECT favorite_friends.*, posts.* FROM favorite_friends, posts WHERE favorite_friends.user_id='$session' AND favorite_friends.friend_id = posts.to_id  AND posts.to_id=posts.from_id ORDER BY posts.id DESC");

[voip03]

******************************

your table look like

******************************

User post table

User id No of post

1 5

2 9

3 4

4 9

5 8

6 0

7 2

 

Friend list

User id Friend id

4 6

3 8

1 9

2 1

3 1

1 4

 

[shortysbest]

Favorite Friends table:

 

id---|---user_id---|---friend_id---|---date

1----|---1----------|-------3---------|------

2----|---1----------|-------5---------|-------

3----|---1----------|------18--------|------

4----|---5----------|-------21-------|--------

5----|---1----------|-------14-------|-------

etc.

 

Posts table:

 

id---|---to_id---|---from_id---|----post---|---state---|---date

1----|---3----------|-----3------|-----Me------|----0---------------------

2----|---5----------|-----5------|-----You----|----0-----------------

3----|---1----------|----14-----|-----None---|---0--------------------

4----|---21--------|-----21----|-----ABC-----|----0-----------------

5----|---5----------|----18-----|-------Q-----|----0------------------

etc.

 

so what I need to do is use my session, my session would be #1 let's say, so all rows with the user_id of 1 in the favorite_friend table should be selected, and then I need to compare each row of the friend_id column where user_id is still matched to #1 and find the to_id and from_id that is equal to that, then the result of the favorite friends should be ordered by the id of the posts table so the newer posts are on top. Also it should only look for the posts where the state is equal to 0 (that changed to 1 when a user hides a post)

 

To break it down:

1. User_id has to be my id (the session I'm in)

2. friend_id has to match to_id and from_id

3. state has to be equal to 0

4. to_id and from_id have to be the same as each other because that means that it's a status update on their own profile, not a comment on someone elses.

 

 

so a working example of the output would be:

 

1. Q

2. None

3. You

4. Me

 

because the id is is incremented the higher the id, the newer the posts.

 

I hope this isn't too confusing, some what difficult to explain real well.