From e1ecc62b8388a6739acc37077d503e7e570cdbbb Mon Sep 17 00:00:00 2001
From: nikrou <nikrou@piwigo.org>
Date: Thu, 24 Jun 2010 18:36:24 +0000
Subject: 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
---
 comments.php | 39 +++++++++++++++++++++++----------------
 1 file changed, 23 insertions(+), 16 deletions(-)

(limited to 'comments.php')

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'],
         )
-- 
cgit v1.2.3