Changeset 6602

Timestamp:

Jun 24, 2010, 10:35:46 PM (14 years ago)

Author:

nikrou

Message:

Bug 1735 fixed : merge from trunk
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 (with permissions filter)

Fixed also problem of FROM_UNIXTIME function not POstgreSQL compatible

Location:

branches/2.1

Files:

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

branches/2.1/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
@@ -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
+  '.get_sql_condition_FandF
+    (
+      array
+      (
+        'forbidden_categories' => 'c.id',
+        'visible_categories' => 'c.id'
+       ),
+      'WHERE'
+     ).'
 ;';
-  $categories = hash_from_query($query, 'id');
+  $categories = hash_from_query($query, 'comment_id');
 
   foreach ($comments as $comment)
@@ -432 +448 @@
     $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'],

branches/2.1/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 )