1

I'm creating a image gallery where people can add comments to each uploaded image.

I've made a db tabel for all comments including a foreign key to match the image table. My SQL variable looks like this and seems to work fine:

$all_comments_one_image_sql="
    SELECT userimage.id, image_id, comment, id, comment_date
    FROM userimage, image_comment
    WHERE image_comment.image_id=userimage.id
    ORDER BY comment_id DESC
";

But when I'm doing my while function for all my gallery images, where and how would I echo the right comments to each image?

G-Nugget
  • 8,666
  • 1
  • 24
  • 31
Mac Luc
  • 971
  • 5
  • 14
  • 31
  • 1
    Please show us more code. Your "while function for all my gallery images" may be very useful for someone who wants to help. – Kamil Feb 28 '13 at 23:00
  • while($gallery_data=mysql_fetch_assoc($gallery_result)){$gallery_out .= "
    ";}
     – Mac Luc Feb 28 '13 at 23:14

3 Answers3

1

You should be doing a left join like this:

select userimage.id, 
       com.image_id, 
       com.comment, 
       com.id, 
       com.comment_date 
             FROM userimage
                LEFT JOIN image_comment as com ON com.image_id=userimage.id
                    WHERE userimage.id = ?
                    ORDER BY com.id DESC
apoq
  • 1,454
  • 13
  • 14
  • Ah - But what do you mean by userimage.id = ? Where would i execute that query? Im doing the while function on the image gallery, would i put some comment code inside that? – Mac Luc Feb 28 '13 at 23:06
  • If you want to list ALL of your images in your while loop, then you don't need this WHERE statement. But if you want to get all the comments for specific image, then for example, when user clicks site.com/image?id=1200, you would set WHERE userimage.id = 1200 – apoq Feb 28 '13 at 23:09
  • ah. I list all images in while loop - so dont dont mind WHERE? – Mac Luc Feb 28 '13 at 23:16
  • Then you won't need it. Yeah. – apoq Feb 28 '13 at 23:18
  • Thanks! And how would i output it in my while loop? – Mac Luc Feb 28 '13 at 23:21
  • you are the greatest - been struggling with this aaall day! – Mac Luc Feb 28 '13 at 23:25
  • Thanks Kapo. I get "mysql_fetch_assoc() expects parameter 1 to be resource".. Dont get why. – Mac Luc Mar 01 '13 at 00:05
  • There must be an error in my request. I don't know the exact row names on your db, can't help you much with this remotely. You've got to read on MySQL (joins mostly). Cheers – apoq Mar 01 '13 at 00:11
0

You have to check for the right image_id

$all_comments_one_image_sql = "
    SELECT userimage.id, image_id, comment, id, comment_date
    FROM userimage, image_comment
    WHERE image_comment.image_id=userimage.id AND image_id = ?
    ORDER BY comment_id DESC
";

where ? is the id of the image you want to Show comments - if you use pdo, you can just bind the image id and don't have to care about escaping.

If you have a summary page for all images, you can add the results in an assoc array, where the key is your image id

$comments[] = array();
while ($row = $res->fetch()) {
    if (empty($comments[$row->image_id])) {
        $comments[$row->image_id] = array();
    }

    $comments[$row->image_id][] = $row;
}

To show the comments, you can just look, if $comments[$image_id] is an array and show the conent

Philipp
  • 15,377
  • 4
  • 35
  • 52
  • well, the image id is variable? I mean the image id i guess is userimage.id? How would i output it though? – Mac Luc Feb 28 '13 at 23:12
0

First of all create a function for getting image comments:

function getImageComments($imageId){
$query = "SELECT userimage.id, image_id, comment, id, comment_date
    FROM userimage
    LEFT JOIN image_comment
    ON image_comment.image_id=userimage.id 
    WHERE image_id = $imageId
    ORDER BY comment_id DESC
";
mysql_query($query);
return mysql_fetch....
}

I also redo your query for better performance.

So whnever you are loading an image, call the function with the proper image id and get its comments.

MIIB
  • 1,849
  • 10
  • 21