Changeset 6596

Timestamp:

Jun 24, 2010, 8:36:24 PM (14 years ago)

Author:

nikrou

Message:

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.

Location:

trunk

Files:

• comments.php (modified) (5 diffs)
• include/functions_comment.inc.php (modified) (1 diff)

trunk/comments.php

@@ -360 +360 @@
 
 $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
@@ -375 +374 @@
   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'])
@@ -389 +394 @@
   array_push($comments, $row);
   array_push($element_ids, $row['image_id']);
-  array_push($category_ids, $row['category_id']);
 }
 
@@ -409 +413 @@
   // 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)
@@ -432 +439 @@
     $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'],

trunk/include/functions_comment.inc.php

@@ -134 +134 @@
   if ($comment_action!='reject' and $conf['anti-flood_time']>0 )
   { // anti-flood system
-    $reference_date = time() - $conf['anti-flood_time'];
+    $reference_date = date('c', time() - $conf['anti-flood_time']);
     $query = '
 SELECT id FROM '.COMMENTS_TABLE.'
-  WHERE date > FROM_UNIXTIME('.$reference_date.')
+  WHERE date > \''.$reference_date.'\'
     AND author_id = '.$comm['author_id'];
     if ( pwg_db_num_rows( pwg_query( $query ) ) > 0 )