Bug 1735 fixed : Comment page is not PostgreSQL compatible

Fixed by adding all fields except category_id in group by clause
category_id is retrieved later in an another query.

Fixed also problem of FROM_UNIXTIME function not POstgreSQL compatible.

git-svn-id: http://piwigo.org/svn/trunk@6596 68402e56-0260-453c-a942-63ccdbb3a9ee

1 files changed, 23 insertions, 16 deletions

diff --git a/comments.php b/comments.php
index 042947067..2aaeb1fbf 100644
--- a/comments.php
+++ b/comments.php
@@ -359,22 +359,27 @@ $element_ids = array();
 $category_ids = array();
 
 $query = '
-SELECT com.id AS comment_id
-     , com.image_id
-     , ic.category_id
-     , com.author
-     , com.author_id
-     , com.date
-     , com.content
-     , com.validated
+SELECT com.id AS comment_id,
+       com.image_id,
+       com.author,
+       com.author_id,
+       com.date,
+       com.content,
+       com.validated
   FROM '.IMAGE_CATEGORY_TABLE.' AS ic
-    INNER JOIN '.COMMENTS_TABLE.' AS com
+    LEFT JOIN '.COMMENTS_TABLE.' AS com
     ON ic.image_id = com.image_id
     LEFT JOIN '.USERS_TABLE.' As u
     ON u.'.$conf['user_fields']['id'].' = com.author_id
   WHERE '.implode('
     AND ', $page['where_clauses']).'
-  GROUP BY comment_id
+  GROUP BY comment_id,
+       com.image_id,
+       com.author,
+       com.author_id,
+       com.date,
+       com.content,
+       com.validated
   ORDER BY '.$page['sort_by'].' '.$page['sort_order'];
 if ('all' != $page['items_number'])
 {
@@ -388,7 +393,6 @@ while ($row = pwg_db_fetch_assoc($result))
 {
   array_push($comments, $row);
   array_push($element_ids, $row['image_id']);
-  array_push($category_ids, $row['category_id']);
 }
 
 if (count($comments) > 0)
@@ -408,11 +412,14 @@ SELECT id, name, file, path, tn_ext
 
   // retrieving category informations
   $query = '
-SELECT id, name, permalink, uppercats
-  FROM '.CATEGORIES_TABLE.'
-  WHERE id IN ('.implode(',', $category_ids).')
+SELECT c.id, name, permalink, uppercats, com.id as comment_id
+  FROM '.CATEGORIES_TABLE.' AS c
+  LEFT JOIN '.IMAGE_CATEGORY_TABLE.' AS ic
+  ON c.id=ic.category_id
+  LEFT JOIN '.COMMENTS_TABLE.' AS com
+  ON ic.image_id=com.image_id
 ;';
-  $categories = hash_from_query($query, 'id');
+  $categories = hash_from_query($query, 'comment_id');
 
   foreach ($comments as $comment)
   {
@@ -431,7 +438,7 @@ SELECT id, name, permalink, uppercats
     // link to the full size picture
     $url = make_picture_url(
       array(
-        'category' => $categories[ $comment['category_id'] ],
+        'category' => $categories[ $comment['comment_id'] ],
         'image_id' => $comment['image_id'],
         'image_file' => $elements[$comment['image_id']]['file'],
         )