Changeset 796 for trunk/comments.php


Ignore:
Timestamp:
Jun 21, 2005, 11:08:11 PM (19 years ago)
Author:
plg
Message:
  • comments page rewritten : comments are displayed one by one, with filters and display options available. The list of comments is paginated.
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/comments.php

    r721 r796  
    3535}
    3636
    37 if (isset($_GET['last_days']))
    38 {
    39   define('MAX_DAYS', $_GET['last_days']);
    40 }
    41 else
    42 {
    43   define('MAX_DAYS', 0);
    44 }
    45 $array_cat_names = array();
     37$sort_order = array(
     38  'descending' => 'DESC',
     39  'ascending' => 'ASC'
     40  );
     41
     42// sort_by : database fields proposed for sorting comments list
     43$sort_by = array(
     44  'date' => 'comment date',
     45  'image_id' => 'image'
     46  );
     47
     48// items_number : list of number of items to display per page
     49$items_number = array(5,10,20,50,'all');
     50
     51// since when display comments ?
     52//
     53$since_options = array(
     54  1 => array('label' => l10n('today'),
     55             'clause' => 'date > SUBDATE(CURDATE(), INTERVAL 1 DAY)'),
     56  2 => array('label' => sprintf(l10n('last %d days'), 7),
     57             'clause' => 'date > SUBDATE(CURDATE(), INTERVAL 7 DAY)'),
     58  3 => array('label' => sprintf(l10n('last %d days'), 30),
     59             'clause' => 'date > SUBDATE(CURDATE(), INTERVAL 30 DAY)'),
     60  4 => array('label' => l10n('the beginning'),
     61             'clause' => '1=1') // stupid but generic
     62  );
     63
     64$page['since'] = isset($_GET['since']) ? $_GET['since'] : 1;
     65
     66// on which field sorting
     67//
     68$page['sort_by'] = 'date';
     69// if the form was submitted, it overloads default behaviour
     70if (isset($_GET['sort_by']))
     71{
     72  $page['sort_by'] = $_GET['sort_by'];
     73}
     74
     75// order to sort
     76//
     77$page['sort_order'] = $sort_order['descending'];
     78// if the form was submitted, it overloads default behaviour
     79if (isset($_GET['sort_order']))
     80{
     81  $page['sort_order'] = $sort_order[$_GET['sort_order']];
     82}
     83
     84// number of items to display
     85//
     86$page['items_number'] = 5;
     87if (isset($_GET['items_number']))
     88{
     89  $page['items_number'] = $_GET['items_number'];
     90}
     91
     92// which category to filter on ?
     93$page['cat_clause'] = '1=1';
     94if (isset($_GET['cat']) and 0 != $_GET['cat'])
     95{
     96  $page['cat_clause'] =
     97    'category_id IN ('.implode(',', get_subcat_ids(array($_GET['cat']))).')';
     98}
     99
     100// search a particular author
     101$page['author_clause'] = '1=1';
     102if (isset($_GET['author']) and !empty($_GET['author']))
     103{
     104  if (function_exists('mysql_real_escape_string'))
     105  {
     106    $author = mysql_real_escape_string($_GET['author']);
     107  }
     108  else
     109  {
     110    $author = mysql_escape_string($_GET['author']);
     111  }
     112
     113  $page['author_clause'] = 'author = \''.$author.'\'';
     114}
     115
     116// search a substring among comments content
     117$page['keyword_clause'] = '1=1';
     118if (isset($_GET['keyword']) and !empty($_GET['keyword']))
     119{
     120  if (function_exists('mysql_real_escape_string'))
     121  {
     122    $keyword = mysql_real_escape_string($_GET['keyword']);
     123  }
     124  else
     125  {
     126    $keyword = mysql_escape_string($_GET['keyword']);
     127  }
     128  $page['keyword_clause'] =
     129    '('.
     130    implode(' AND ',
     131            array_map(
     132              create_function(
     133                '$s',
     134                'return "content LIKE \'%$s%\'";'
     135                ),
     136              preg_split('/[\s,;]+/', $keyword)
     137              )
     138      ).
     139    ')';
     140}
     141
    46142// +-----------------------------------------------------------------------+
    47143// |                         comments management                           |
    … …  
    71167if (!defined('IN_ADMIN'))
    72168{
    73   $title= $lang['title_comments'];
     169  $title= l10n('title_comments');
    74170  include(PHPWG_ROOT_PATH.'include/page_header.php');
    75171}
    … …  
    79175  array(
    80176    'L_COMMENT_TITLE' => $title,
    81     'L_COMMENT_STATS' => $lang['stats_last_days'],
    82     'L_COMMENT_RETURN' => $lang['home'],
    83         'L_COMMENT_RETURN_HINT' => $lang['home_hint'],
    84     'L_DELETE' =>$lang['delete'],
    85     'L_VALIDATE'=>$lang['submit'],
     177
     178    'F_ACTION'=>PHPWG_ROOT_PATH.'comments.php',
     179    'F_KEYWORD'=>@$_GET['keyword'],
     180    'F_AUTHOR'=>@$_GET['author'],
    86181   
    87182    'U_HOME' => add_session_id(PHPWG_ROOT_PATH.'category.php')
    … …  
    89184  );
    90185
    91 foreach ($conf['last_days'] as $option)
    92 {
    93   $url = $_SERVER['PHP_SELF'].'?last_days='.($option - 1);
    94   if (defined('IN_ADMIN'))
    95   {
    96     $url.= '&page=comments';
    97   }
     186// +-----------------------------------------------------------------------+
     187// |                          form construction                            |
     188// +-----------------------------------------------------------------------+
     189
     190// Search in a particular category
     191$blockname = 'category';
     192
     193$template->assign_block_vars(
     194  $blockname,
     195  array('SELECTED' => '',
     196        'VALUE'=> 0,
     197        'OPTION' => '------------'
     198    ));
     199
     200$query = '
     201SELECT id,name,uppercats,global_rank
     202  FROM '.CATEGORIES_TABLE;
     203if ($user['forbidden_categories'] != '')
     204{
     205  $query.= '
     206    WHERE id NOT IN ('.$user['forbidden_categories'].')';
     207}
     208$query.= '
     209;';
     210display_select_cat_wrapper($query, array(@$_GET['cat']), $blockname, true);
     211
     212// Filter on recent comments...
     213$blockname = 'since_option';
     214
     215foreach ($since_options as $id => $option)
     216{
     217  $selected = ($id == $page['since']) ? 'selected="selected"' : '';
     218 
    98219  $template->assign_block_vars(
    99     'last_day_option',
    100     array(
    101       'OPTION'=>$option,
    102       'T_STYLE'=>(($option == MAX_DAYS + 1)?'text-decoration:underline;':''),
    103       'U_OPTION'=>add_session_id($url)
    104       )
    105     );
    106 }
     220    $blockname,
     221    array('SELECTED' => $selected,
     222          'VALUE'=> $id,
     223          'CONTENT' => $option['label']
     224      ));
     225}
     226
     227// Sort by
     228$blockname = 'sort_by_option';
     229
     230foreach ($sort_by as $key => $value)
     231{
     232  $selected = ($key == $page['sort_by']) ? 'selected="selected"' : '';
     233
     234  $template->assign_block_vars(
     235    $blockname,
     236    array('SELECTED' => $selected,
     237          'VALUE'=> $key,
     238          'CONTENT' => l10n($value)
     239      ));
     240}
     241
     242// Sorting order
     243$blockname = 'sort_order_option';
     244
     245foreach (array_keys($sort_order) as $option)
     246{
     247  $selected = ($option == $page['sort_order']) ? 'selected="selected"' : '';
     248
     249  $template->assign_block_vars(
     250    $blockname,
     251    array('SELECTED' => $selected,
     252          'VALUE'=> $option,
     253          'CONTENT' => l10n($option)
     254      ));
     255}
     256
     257// Number of items
     258$blockname = 'items_number_option';
     259
     260foreach ($items_number as $option)
     261{
     262  $selected = ($option == $page['items_number']) ? 'selected="selected"' : '';
     263
     264  $template->assign_block_vars(
     265    $blockname,
     266    array('SELECTED' => $selected,
     267          'VALUE'=> $option,
     268          'CONTENT' => is_numeric($option) ? $option : l10n($option)
     269      ));
     270}
     271
     272// +-----------------------------------------------------------------------+
     273// |                            navigation bar                             |
     274// +-----------------------------------------------------------------------+
     275
     276if (isset($_GET['start']) and is_numeric($_GET['start']))
     277{
     278  $start = $_GET['start'];
     279}
     280else
     281{
     282  $start = 0;
     283}
     284
     285$query = '
     286SELECT COUNT(DISTINCT(id))
     287  FROM '.IMAGE_CATEGORY_TABLE.' AS ic
     288    INNER JOIN '.COMMENTS_TABLE.' AS com
     289    ON ic.image_id = com.image_id
     290  WHERE '.$since_options[$page['since']]['clause'].'
     291    AND '.$page['cat_clause'].'
     292    AND '.$page['author_clause'].'
     293    AND '.$page['keyword_clause'];
     294if ($user['forbidden_categories'] != '')
     295{
     296  $query.= '
     297    AND category_id NOT IN ('.$user['forbidden_categories'].')';
     298}
     299$query.= '
     300;';
     301list($counter) = mysql_fetch_row(pwg_query($query));
     302
     303$url = PHPWG_ROOT_PATH.'comments.php?t=1'.get_query_string_diff(array('start'));
     304
     305$navbar = create_navigation_bar($url,
     306                                $counter,
     307                                $start,
     308                                $page['items_number'],
     309                                '');
     310
     311$template->assign_vars(array('NAVBAR' => $navbar));
     312
    107313// +-----------------------------------------------------------------------+
    108314// |                        last comments display                          |
    109315// +-----------------------------------------------------------------------+
    110 // 1. retrieving picture ids which have comments recently added
    111 $maxdate = date('Y-m-d', strtotime('-'.MAX_DAYS.' day'));
     316
     317$comments = array();
     318$element_ids = array();
     319$category_ids = array();
    112320
    113321$query = '
    114 SELECT DISTINCT(ic.image_id) AS image_id,ic.category_id, uppercats
    115   FROM '.COMMENTS_TABLE.' AS c, '.IMAGE_CATEGORY_TABLE.' AS ic
    116     , '.CATEGORIES_TABLE.' AS cat
    117   WHERE c.image_id = ic.image_id
    118     AND ic.category_id = cat.id
    119     AND date >= \''.$maxdate.'\'';
    120 if ($user['status'] != 'admin')
    121 {
    122   $query.= "
    123     AND validated = 'true'";
    124   // we must not show pictures of a forbidden category
    125   if ($user['forbidden_categories'] != '')
    126   {
    127     $query.= '
     322SELECT com.id AS comment_id
     323     , com.image_id
     324     , ic.category_id
     325     , com.author
     326     , com.date
     327     , com.content
     328     , com.id AS comment_id
     329  FROM '.IMAGE_CATEGORY_TABLE.' AS ic
     330    INNER JOIN '.COMMENTS_TABLE.' AS com
     331    ON ic.image_id = com.image_id
     332  WHERE '.$since_options[$page['since']]['clause'].'
     333    AND '.$page['cat_clause'].'
     334    AND '.$page['author_clause'].'
     335    AND '.$page['keyword_clause'];
     336if ($user['forbidden_categories'] != '')
     337{
     338  $query.= '
    128339    AND category_id NOT IN ('.$user['forbidden_categories'].')';
    129   }
    130340}
    131341$query.= '
    132   GROUP BY ic.image_id
    133   ORDER BY ic.image_id DESC
     342  GROUP BY comment_id
     343  ORDER BY '.$page['sort_by'].' '.$page['sort_order'];
     344if ('all' != $page['items_number'])
     345{
     346  $query.= '
     347  LIMIT '.$start.','.$page['items_number'];
     348}
     349$query.= '
    134350;';
    135351$result = pwg_query($query);
    136 if ($user['status'] == 'admin')
    137 {
    138   $template->assign_block_vars('validation', array());
    139 }
    140352while ($row = mysql_fetch_array($result))
    141353{
    142   $category_id = $row['category_id'];
     354  array_push($comments, $row);
     355  array_push($element_ids, $row['image_id']);
     356  array_push($category_ids, $row['category_id']);
     357}
     358
     359if (count($comments) > 0)
     360{
     361  // retrieving element informations
     362  $elements = array();
     363  $query = '
     364SELECT id, name, file, path, tn_ext
     365  FROM '.IMAGES_TABLE.'
     366  WHERE id IN ('.implode(',', $element_ids).')
     367;';
     368  $result = pwg_query($query);
     369  while ($row = mysql_fetch_array($result))
     370  {
     371    $elements[$row['id']] = $row;
     372  }
     373
     374  // retrieving category informations
     375  $categories = array();
     376  $query = '
     377SELECT id, uppercats
     378  FROM '.CATEGORIES_TABLE.'
     379  WHERE id IN ('.implode(',', $category_ids).')
     380;';
     381  $result = pwg_query($query);
     382  while ($row = mysql_fetch_array($result))
     383  {
     384    $categories[$row['id']] = $row;
     385  }
     386
     387  foreach ($comments as $comment)
     388  {
     389    // name of the picture
     390    $name = get_cat_display_name_cache(
     391      $categories[$comment['category_id']]['uppercats'], '', false);
     392    $name.= $conf['level_separator'];
     393    if (!empty($elements[$comment['image_id']]['name']))
     394    {
     395      $name.= $elements[$comment['image_id']]['name'];
     396    }
     397    else
     398    {
     399      $name.= get_name_from_file($elements[$comment['image_id']]['file']);
     400    }
     401   
     402    // source of the thumbnail picture
     403    $thumbnail_src = get_thumbnail_src(
     404      $elements[$comment['image_id']]['path'],
     405      @$elements[$comment['image_id']]['tn_ext']
     406      );
    143407 
    144   // for each picture, getting informations for displaying thumbnail and
    145   // link to the full size picture
    146   $query = '
    147 SELECT name,file,storage_category_id as cat_id,tn_ext,path
    148   FROM '.IMAGES_TABLE.'
    149   WHERE id = '.$row['image_id'].'
    150 ;';
    151   $subresult = pwg_query($query);
    152   $subrow = mysql_fetch_array($subresult);
    153 
    154   // name of the picture
    155   $name = get_cat_display_name_cache($row['uppercats'], '', false);
    156   $name.= $conf['level_separator'];
    157   if (!empty($subrow['name']))
    158   {
    159     $name.= $subrow['name'];
    160   }
    161   else
    162   {
    163     $name.= str_replace('_',' ',get_filename_wo_extension($subrow['file']));
    164   }
    165 
    166   // source of the thumbnail picture
    167   $thumbnail_src = get_thumbnail_src($subrow['path'], @$subrow['tn_ext']);
    168   // link to the full size picture
    169   $url = PHPWG_ROOT_PATH.'picture.php?cat='.$category_id;
    170   $url.= '&image_id='.$row['image_id'];
     408    // link to the full size picture
     409    $url = PHPWG_ROOT_PATH.'picture.php?cat='.$comment['category_id'];
     410    $url.= '&image_id='.$comment['image_id'];
    171411   
    172   $template->assign_block_vars(
    173     'picture',
    174     array(
    175       'TITLE_IMG'=>$name,
    176       'I_THUMB'=>$thumbnail_src,
    177       'U_THUMB'=>add_session_id($url)
    178       ));
     412    $template->assign_block_vars(
     413      'picture',
     414      array(
     415        'TITLE_IMG'=>$name,
     416        'I_THUMB'=>$thumbnail_src,
     417        'U_THUMB'=>add_session_id($url)
     418        ));
    179419   
    180   // for each picture, retrieving all comments
    181   $query = '
    182 SELECT *
    183   FROM '.COMMENTS_TABLE.'
    184   WHERE image_id = '.$row['image_id'].'
    185     AND date >= \''.$maxdate.'\'';
    186   if ($user['status'] != 'admin')
    187   {
    188     $query.= '
    189     AND validated = \'true\'';
    190   }
    191   $query.= '
    192   ORDER BY date DESC
    193 ;';
    194   $handleresult = pwg_query($query);
    195   while ($subrow = mysql_fetch_array($handleresult))
    196   {
    197     $author = $subrow['author'];
    198     if (empty($subrow['author']))
     420    $author = $comment['author'];
     421    if (empty($comment['author']))
    199422    {
    200       $author = $lang['guest'];
     423      $author = l10n('guest');
    201424    }
    202 
     425   
    203426    $template->assign_block_vars(
    204427      'picture.comment',
    205428      array(
    206         'COMMENT_AUTHOR'=>$author,
    207         'COMMENT_DATE'=>format_date($subrow['date'],'mysql_datetime',true),
    208         'COMMENT'=>parse_comment_content($subrow['content']),
     429        'COMMENT_AUTHOR' => $author,
     430        'COMMENT_DATE'=>format_date($comment['date'],'mysql_datetime',true),
     431        'COMMENT'=>parse_comment_content($comment['content']),
    209432        ));
    210    
    211     if ($user['status'] == 'admin')
    212     {
    213       $template->assign_block_vars(
    214         'picture.comment.validation',
    215         array(
    216           'ID'=> $subrow['id'],
    217           'CHECKED'=>($subrow['validated']=='false')?'checked="checked"': ''
    218           ));
    219     }
    220433  }
    221434}
Note: See TracChangeset for help on using the changeset viewer.