PHP adding user comments to image gallery

Question

Im really struggling with adding comments to a user generated php gallery i've made. I have a database table for the images: userimage (id, imagepath, userid, description) and table image_comment with a foreign key column (comment_id, comment, auther, image_id, comment_date). My approach has been to make a function with a sql query and call that in my gallery's while loop - hoping to extract the right comments to the matching image in the loop.
FUNCTION:

function getImageComments($imageId){
$query = "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";
mysql_query($query);}

VARIABLE AND FUNCTION CALL IN THE WHILE LOOP:

while($gallery_data=mysql_fetch_assoc($gallery_result))
{
$gallery_out .= "<div class=\"pic-container\"><img src=\"".$gallery_data['path']."\">";

$imageId = $gallery_data['id'];
$gallery_out .= getImageComments($imageId);

This does absolutely nothing for me, and Im simply stocked here, no fantasy to see how to continue..

have u tried executing the query from PHPMyAdmin? maybe it has some error and that's why it does nothing. — Naryl, Mar 01 '13 at 10:37
the gallery works. its sql and query are executed in between :) — Mac Luc, Mar 01 '13 at 10:37
If you do this for every image in the gallery, it's a bad idea to spread that over N amount of queries. Consider getting all the comments `WHERE image_ID IN ( — AmazingDreams, Mar 01 '13 at 10:50

score 1 · Accepted Answer · answered Mar 01 '13 at 10:41

1

function getImageComments($imageId){
   $query = "SELECT userimage.id, image_id, comment, id, comment_date
   FROM userimage, image_comment
   WHERE image_comment.image_id=userimage.id
   AND userimage.id='$imageId';
   ORDER BY comment_id DESC";
   $result=mysql_query($query);
   //do something with this query result, since you are doing $gallery_out .= getImageComments($imageId);
   $return_value="";
   while($row=mysql_fetch_array($result)){
       $return_value.="<div>$row[2]</div>";
   }  
   return $return_value;
}

Also if you are developing new code, please consider using mysqli_ functions or PDO!

answered Mar 01 '13 at 10:41

Naryl

1,878
1
10
12

Thanks Naryl. I get a warning: mysql_fetch_array() expects parameter 1 to be resource.. Cant see why.. – Mac Luc Mar 01 '13 at 10:48
normally when that happens it's because the query has failed! – Naryl Mar 01 '13 at 10:53
If you are using 2 tables in the query I would recommend you to add `tablename.` before each field, with the corresponding tablename – Naryl Mar 01 '13 at 10:55
ah - i think its the 2 semicolons in the query? – Mac Luc Mar 01 '13 at 11:07

AmazingDreams · Answer 2 · 2013-03-01T11:10:57.243

Even though the code is a whole lot different then yours. I do something similar to your gallery, using only a total of TWO queries instead of 1 + 1 for each post.

Part of $user->GetPosts(); (I left out the queries)

$posts = []; 
$post_IDs = [];
while($row = $result->fetch_assoc())
{
    if(!in_array($row['object_ID'], $post_IDs))
        $post_IDs[] = $row['object_ID'];

    $posts[] = new Post($row, $_SESSION['uid']);
}

if(count($post_IDs) == 0)
    return $posts;

$result = $this->getComments($post_IDs);

while($row = $result->fetch_assoc())
{
    $comment = new Comment($row, $_SESSION['uid']);
    foreach($posts as $post)
        if($post->object_ID == $comment->object_commented_ID)
            $post->comments[] = $comment;
}

return $posts;

Query in $this->getComments($post_IDs); Here is the query:

$object_IDs = implode(",", $object_IDs);

$query = 
    "SELECT 
        comments.object_ID, comments.object_commented_ID, comments.entity_ID, 
        comments.datetime, comments.text, 
        objects.likes, SUM(IF(likes.user_ID=?, 1, 0)) AS allowLike, 
    users.first_name, users.last_name, userinfo.image_ID FROM comments 
    LEFT JOIN users ON users.entity_ID=comments.entity_ID 
    LEFT JOIN userinfo ON users.entity_ID=userinfo.user_ID 
    LEFT JOIN likes ON likes.object_ID=comments.object_ID 
    LEFT JOIN objects ON objects.ID=comments.object_ID
    WHERE comments.object_commented_ID IN(". $object_IDs .") 
    GROUP BY comments.object_ID ORDER BY comments.datetime ASC LIMIT 0,100";

Then in a seperate foreach loop I call

$post->Show(); //Which in turn calls foreach $comment->Show();

uh - thanks a lot, hope i can use it. I have to spend some time understanding it :) — Mac Luc, Mar 01 '13 at 11:04
Yeah don't be scared by the huge query, my DB structure is build in such a way that it would allow infinite types of objects (images, posts, food, cars, trucks, whatever you can name) and infinite types of 'users' (pages, users, etc.) So I have some extra tables to allow this which have to be `LEFT JOINED`. OH and I have a seperate table userinfo, which contains the image. And likes, lol — AmazingDreams, Mar 01 '13 at 11:08

PHP adding user comments to image gallery

2 Answers2