I have been having this problem for some time and cannot be able to solve it. I am unable to retieve the information I want from a database. I am trying to retrieve a user's status update starting with the latest entry first whether or not one has uploaded a status update pic. To be shown in the form of:
avatar pic - status text - update pic or
avatar pic - status text - default update pic (if not image uploaded) or
default avatar pic - status text - default update pic
MY TABLES
"users"
[PK=user_id, FK=user_avatar_id]
user_id username user_avatar_id,(shortened table)
92 kkjay 24
95 njama null
"wid_updates"
[PK=update_id, (FK=user_id, attached_picture_id)]
update_id update_text attached_picture_id user_id timestamp
26 Done dana 24 92 2011-01-05 12:39:27
27 Qzzz. 25 92 2011-01-05 12:42:18
29 Psys guys 26 95 2011-01-05 13:50:54
31 I am fine null 92 2011-01-05 15:41:52
"picture"
[PK=picture_id, FK=user_id]
picture_id picture_url picture_thumb_url user_id
24 ../User_Pictures/9042.png ../User_Pictures/Thumbs/904.png 92
25 ../User_Pictures/81104.jpg ../User_Pictures/Thumbs/814.jpg 92
26 ../User_Pictures/64124.jpg ../User_Pictures/Thumbs/641.jpg 95
NB:Update pics are first saved into pictures table then their picture_id saved into the wid_updates table as attached_pic_id foreign key.
MY PHP CODE
<?php require_once('Connections/connections.php'); ?>
<?php
//query user_avatar_id and user_id
$user_id = $_SESSION['UserSession'];
mysql_select_db($database_connections, $connections);
$query_user_info = "SELECT user_avatar_id FROM users WHERE user_id='$user_id'";
$user_info_result = mysql_query($query_user_info) or die(mysql_error());
$user_info = mysql_fetch_assoc($user_info_result);
$avatar_id = $user_info['user_avatar_id'];
//query avatar thumb from pictures
$query_avatar_thumb = "SELECT picture_thumb_url FROM picture WHERE picture_id='$avatar_id'";
$avatar_thumb_result = mysql_query($query_avatar_thumb) or die(mysql_error());
$avatar_thumb = mysql_fetch_assoc($avatar_thumb_result);
//query update image
$query_wid_updates = "SELECT update_text, picture_thumb_url FROM wid_updates JOIN picture ON wid_updates.user_id = '$user_id'
AND wid_updates.attached_picture_id = picture.picture_id OR wid_updates.attached_picture_id IS NULL ORDER BY wid_updates.update_id DESC";
$wid_updates = mysql_query($query_wid_updates, $connections) or die(mysql_error());
$row_wid_updates = mysql_fetch_assoc($wid_updates);
?>
DISPLAY PORTION
<?php do { ?>
<table width="460" border="0">
<tr>
<td height="50" width="50"><a href="profile.php"><img src="Images/<?php echo $avatar_thumb['picture_thumb_url']; ?>"
height="50" width="50" border="0"/></a></td>
<td width="360" colspan="3" class="overide" id="update_box"><div><?php echo $row_wid_updates['update_text']; ?></div></td>
<td width="50"><a href="pictures.php"><img src="Images/<?php echo $row_wid_updates['picture_thumb_url']; ?>"
height="50" width="50" border="0"/></a></td>
</tr>
<tr>
<td width="50" height="30"></td>
<td width="120"></td>
<td width="120" class="ordinary_text_12_green">Comment</td>
<td width="120"></td>
<td width="50"></td>
</tr>
</table>
<?php } while ($row_wid_updates = mysql_fetch_assoc($wid_updates)); ?>
The results I get are wid_updates 27 and 26 together with the avatar and update images. The null update (31) is shown 3 times above the 27 and 26 results and the 3 updates contain the 3 pics in the picture table. Please help. I've spent most of the day trying and nothing. I appreciate any help.
