[RESOLVED] Displaying descending order status updates with avatar image and update image

kkjay

Hi Guys,
Aplologies for the length but I wanted to be clear.
I'm trying to make a website where people can update their status's (like facebook). Just learning PHP. I have added a functionality whereby one can update their status(text only) or can update their status(text and upload a photo for that status). I have been able to do this but after updating, the difficulty is showing one's previous updates in the form of:

avatar pic - update text - update image(if image)

avatar pic - update text - default image(if update image not uploaded)

default avatar pic - update text - default image(if update image not uploaded)

with the latest updates first and then the rest in descending order.

If all updates for a user have update pics(ie. wid_updates.attached_picture_is not null, then I do not have a problem. If a user does not put up a pic update, then all hell breaks loose when returning previous updates.

MY TABLES:

"users"
[PK=user_id
user_id      username  (shortened table)
92              kkjay          

95              njama

"wid_updates" 
[PK=update_id, (FK=user_id, attached_picture_id)]       

update_id    update_text 	attached_picture_id 	user_id    

26 	            Done dana   	24 	                                 92 	
27 	            Qzzz. 	                25 	                                 92 	
29 	            Psys guys       	26 	                                 95 	
31                I am fine 		null                                    92

"picture"
[PK=picture_id, FK=user_id]
picture_id    picture_url 	            picture_thumb_url          	   user_id  avatar
24                ../Pictures/90.png     ../Pictures/Thumbs/90.png 	   92 	  1
25                ../Pictures/811.jpg    ../Pictures/Thumbs/811.jpg    92 	  0
26                ../Pictures/641.jpg    ../Pictures/Thumbs/641.jpg    95           0

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 query:

<?php
$user_id = $_SESSION['UserSession'];
//query user avatar
mysql_select_db($database_connections, $connections);
$query_avatar_thumb = "SELECT picture_thumb_url FROM picture WHERE user_id='$user_id' AND avatar='1'";
$avatar_thumb_result = mysql_query($query_avatar_thumb) or die(mysql_error());	
$avatar_thumb = mysql_fetch_assoc($avatar_thumb_result);

//query username
$query_user_info = "SELECT username FROM users WHERE user_id='$user_id'";
$user_info = mysql_query($query_user_info, $connections) or die(mysql_error());
$row_user_info = mysql_fetch_assoc($user_info);

//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);
?>

The avatar field is set to 1 if it is the profile picture of a user and set to null when it is not. When I upload an update with no update picture, I get a display of that update 3 times with all the 3 pics in the picture table.

Another question, when doing a search because I intend to make it a social network web app, how will I make a query that will return a thumb for a particular user that is being searched for based on the table.

Thanks in advance.

djjjozsi

no result yet?

kkjay

@, no result. Doesn't work no matter how much I keep trying new things

djjjozsi

"picture"
"wid_updates"

in your form you have to use the picture_id as index name.
In the processing page now you have the $_FILES array.

<input type="file" name="filename[$picture_id]">

Where the file name is empty, you can use foreach() to go through the file hasn't changed. If yes, you can update the picture table, and delete the previous image.

In the text field the method is the same, but you can use wid_updates's primary key to pass the text for the image.

kkjay

Thanks djjjozsi,

I am able to update and delete my statuses and images. The problem is displaying the results.

ie. the last update to put up should be shown first with the rest in a descending order. I get multiple returns on text updates that do not have an attached picture.

The problem is occuring in the PHP join query:

//query update image above

djjjozsi

Ok, sorry for misunderstanding you.

In this query if you have tested in phpmyadmin you should see the results, if this is more then one record, you should use while cycle to retrieve it.

$query_wid_updates = "SELECT * 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());

while($r = mysql_fetch_assoc($wid_updates))
{
echo "<pre>";
print_r($r);
echo "</pre>";
}

Make sure you have selected all the relevant fields you need in the rest of your program.

kkjay

Thanks again for the reply.

I am using the do while statement to display the updates as below.

<?php do { ?>                                      

<table width="460" border="0">                     

    <tr>
         <td height="50" width="50"><a href="profile.php"><img      src="User_Pictures/<?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="User_Pictures/<?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)); ?>

Lets say I have 3 updates with ID updates 1,2 and 3 with 3 being the last entered.
Lets say that updates 1 and 3 have images and 2 does not.
Lets say we have 3 images in the pictures table.
With the above my code you get the following:

wid_update 3 ......text.....updateimage (correct image)
wid_update 2 ......text.....updateimage (the 1st image that was entered in the pictures table which is incorrect)
wid_update 2 ......text.....updateimage (the 2nd image that was entered in the pictures table which is incorrect)
wid_update 2 ......text.....updateimage (the 3rd image that was entered in the pictures table which is incorrect)
wid_update 1 ......text.....updateimage (correct image)

My do while statement workd correct if all updates have update pictures. I believe I am going wrong here:

//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);

But I dont know how to fix it.

kkjay

I got some help and solved the problem. I changed the query to:


//query update image
$query_wid_updates = "SELECT update_text, picture_thumb_url FROM wid_updates LEFT JOIN picture ON wid_updates.user_id = '$user_id' 
AND wid_updates.attached_picture_id = picture.picture_id 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);

djjjozsi

before the second script where retrieve the images you have a line like this?

$row_wid_updates = mysql_fetch_assoc($wid_updates);

Its a problem, because you echo then the results from the second record.

I've tested your program, then i can see 2 images for the userid: 92 , and
at user '95' i can see a picture.

kkjay

You are right. I had not noticed it till now. I was echoing then the results from the second record. Thanks for pointing that out.

kkjay

Now, I get an extra blank update box with only a profile pic at the top.

$user_id = $_SESSION['UserSession'];
//query user avatar
mysql_select_db($database_connections, $connections);
$query_avatar_thumb = "SELECT picture_thumb_url FROM picture WHERE user_id='$user_id' AND avatar='1'";
$avatar_thumb_result = mysql_query($query_avatar_thumb) or die(mysql_error());	
$avatar_thumb = mysql_fetch_assoc($avatar_thumb_result);

Im guessing the line, $avatar_thumb = mysql_fetch_assoc($avatar_thumb_result); is the problem.

But if I take it out, things get uglier. Any solution?

djjjozsi

you should use some LIMIT, you expect only one avatar.

SELECT picture_thumb_url FROM picture WHERE user_id='$user_id' AND avatar='1' LIMIT 1

It's always a good practice to get the number of lines mysql_num_rows() , and handle that case if the current user don't want an avatar image.

kkjay

Thanks for all your help. Im learning a lot. I plan on using a default avatar image if a user doesnt have one. Still trying to get the 1st blank update to go