PHP查询从pals和self检索文本和/或图像状态更新

Question

Im building a social networking website. A user can have pals and be able to see their updates(like facebook). Furthermore, one can be able upload a text update only or a text and image update.

I want my home page to display the updates of pals and themselves in descending order starting with the latest first.

I would like the display to be like this:

Avatar_picture   Update_text    Update_image         ...OR
Avatar_picture   Update_text    no_image             ...OR
Default_avatar_picture   Update_text    no_image 

I have a users table with:

user_id, username, user_telephone, user_address etc

I also have a pictures table where images, including avatars are stored:

picture_id   picture_url  picture_thumb_url  user_id  type                timestamp  
105          68253.png    68253.png          95       other               2011-02-21 22:42:41 
106          44391.jpg    44391.jpg          95       other               2011-02-21 22:42:57 
107          73154.jpg    73154.jpg          95       main_avatar_large   2011-02-21 22:43:06 

As can be seen it stores a user's id and type. Any image uploaded and selected as avatar is renamed as type main_avatar_large.

I have a pals table:

pal_id  user1_id  user2_id   status  timestamp  
25      92         104       1       2011-02-24 09:34:43 
26      95         92        1       2011-02-24 09:35:12 
27      92         107       1       2011-02-24 09:35:17 
28      97         92        1       2011-02-24 09:35:21 
31      106        92        1       2011-02-24 09:36:32 
32      105        92        1       2011-02-24 09:36:48 
33      92         103       0       2011-02-24 09:37:09 
36      92         99        0       2011-02-24 22:54:28 
37      108        92        1       2011-02-25 08:45:58 

user1_id stores the user_id of the friendship initiator. Status is set to 1 when friendship is confirmed.

I also have a updates table called wid_updates:

update_id  update_text     attached_picture_id   user_id   timestamp  
87         Hi there        NULL                  92        2011-02-21 18:57:46 
88         A new update    NULL                  92        2011-02-21 22:53:19 
89         Another one     112                   92        2011-02-21 22:53:32 
90         123             NULL                  108       2011-02-24 09:39:24 
91         Jamo's plan     124                   108       2011-02-25 08:42:35 

As can be seen, if a status update does not have a picture, the attached picture id is null.

I have this query that is able to get the user_id's of all pals that a user has plus their avatars:

$query_pal_info =  "SELECT DISTINCT users.user_id, picture.picture_thumb_url FROM pals, users, picture
                    WHERE (
                    pals.user1_id = '$user_id' AND pals.user2_id = users.user_id
                    AND pals.status = '1'  AND picture.user_id = users.user_id AND picture.type='main_avatar_large')
                    UNION 
                    SELECT DISTINCT users.user_id, picture.picture_thumb_url FROM pals, users, picture
                    WHERE (
                    pals.user2_id = '$user_id' AND pals.user1_id = users.user_id
                    AND pals.status = '1' AND picture.user_id = users.user_id AND picture.type='main_avatar_large')";

$pal_info = mysql_query($query_pal_info , $connections) or die(mysql_error());

I can also retrieve in my profile page, the logged in user's past status updates with avatar and update images:

//query update
$query_wid_updates = "SELECT update_text, picture_thumb_url, picture_id FROM wid_updates LEFT JOIN picture ON wid_updates.attached_picture_id = picture.picture_id 
WHERE wid_updates.user_id = '$user_id' ORDER BY wid_updates.update_id DESC";
$wid_updates = mysql_query($query_wid_updates, $connections) or die(mysql_error());

How do I build a query to display the updates of pals and a user's own updates in descending order starting with the latest first with text and images? Any help will be greatly appreciated.

Answer 1

If the id of the currently logged user is current_user_id try that

SELECT av.picture_thumb_url as avatar_pic, update_text, upd_pic.picture_thumb_url as update_pic FROM  
wid_updates upd  
LEFT JOIN pictures upd_pic ON attached_picture_id = upd_pic.picture_id  
LEFT JOIN pictures av ON av.user_id = upd.user_id AND av.type = 'main_avatar_large'  
WHERE    
upd.user_id IN (SELECT IF (p.user1_id == current_user_id, p.user2_id, p.user1_id) AS pal_id FROM pals p WHERE p.user1_id = current_user_id OR p.user2_id = current_user_id)  
OR upd.user_id = current_user_id ORDER BY timestamp DESC