Changeset 610


Ignore:
Timestamp:
Nov 20, 2004, 6:23:42 PM (19 years ago)
Author:
plg
Message:
  • optimization : representative picture becomes mandatory for a non empty category. So, at each insertion in images table for a category, a new representative element is elected (by rand). This must be improved by not reelcting a random picture admin set an element as representative manually.
  • optimization : recent cats page only needs 2 queries instead of 3*N (N categories to display). The bad point is that it shows representative element of recent cat and not a random element among recently added.
  • optimization : empty cats page only needs 1 query per non empty sub category instead of... a lot. For each sub category, PhpWebGallery shows the representative element of a category chosen randomly in sub cats. Thus, get_non_empty_subcat_ids and get_first_non_empty_cat_id becomes obsolete.
  • new function get_cat_display_name_cache to show category names with a caching for all gallery categories names. This function, to the contrary of get_cat_display_name shows names in the correct order...
Location:
trunk
Files:
7 edited

Legend:

Unmodified
Added
Removed

  • trunk/admin/include/functions.php

    r606 r610  
    487487    AND id IN ('.implode(',', $cat_ids).')
    488488;';
    489     $result = pwg_query( $query );
    490     while ( $row = mysql_fetch_array( $result ) )
     489    $result = pwg_query($query);
     490    while ($row = mysql_fetch_array($result))
    491491    {
    492492      $query = '
    … …  
    496496    AND image_id = '.$row['representative_picture_id'].'
    497497;';
    498       $result = pwg_query( $query );
    499       if (mysql_num_rows($result) == 0)
    500       {
     498      $sub_result = pwg_query($query);
     499      if (mysql_num_rows($sub_result) == 0)
     500      {
     501        // set a new representative element for this category
    501502        $query = '
     503SELECT image_id
     504  FROM '.IMAGE_CATEGORY_TABLE.'
     505  WHERE category_id = '.$row['id'].'
     506  ORDER BY RAND()
     507  LIMIT 0,1
     508;';
     509        $sub_sub_result = pwg_query($query);
     510        if (mysql_num_rows($sub_sub_result) > 0)
     511        {
     512          list($representative) = mysql_fetch_array(pwg_query($query));
     513          $query = '
     514UPDATE '.CATEGORIES_TABLE.'
     515  SET representative_picture_id = '.$representative.'
     516  WHERE id = '.$row['id'].'
     517;';
     518          pwg_query($query);
     519        }
     520        else
     521        {
     522          $query = '
    502523UPDATE '.CATEGORIES_TABLE.'
    503524  SET representative_picture_id = NULL
    504525  WHERE id = '.$row['id'].'
    505526;';
    506         pwg_query( $query );
     527          pwg_query($query);
     528        }
    507529      }
    508530    }

  • trunk/admin/remote_site.php

    r606 r610  
    431431    }
    432432    $query.= '
     433;';
     434    pwg_query($query);
     435    // set a new representative element for this category
     436    $query = '
     437SELECT image_id
     438  FROM '.IMAGE_CATEGORY_TABLE.'
     439  WHERE category_id = '.$category_id.'
     440  ORDER BY RAND()
     441  LIMIT 0,1
     442;';
     443    list($representative) = mysql_fetch_array(pwg_query($query));
     444    $query = '
     445UPDATE '.CATEGORIES_TABLE.'
     446  SET representative_picture_id = '.$representative.'
     447  WHERE id = '.$category_id.'
    433448;';
    434449    pwg_query($query);

  • trunk/admin/update.php

    r606 r610  
    513513  (category_id,image_id) VALUES
    514514  '.implode(',', $ids).'
     515;';
     516    pwg_query($query);
     517
     518    // set a new representative element for this category
     519    $query = '
     520SELECT image_id
     521  FROM '.IMAGE_CATEGORY_TABLE.'
     522  WHERE category_id = '.$category_id.'
     523  ORDER BY RAND()
     524  LIMIT 0,1
     525;';
     526    list($representative) = mysql_fetch_array(pwg_query($query));
     527    $query = '
     528UPDATE '.CATEGORIES_TABLE.'
     529  SET representative_picture_id = '.$representative.'
     530  WHERE id = '.$category_id.'
    515531;';
    516532    pwg_query($query);

  • trunk/include/category_recent_cats.inc.php

    r606 r610  
    3636// easier to use
    3737$query = '
    38 SELECT id AS category_id
    39   FROM '.CATEGORIES_TABLE.'
     38SELECT c.id AS category_id,uppercats,representative_picture_id,path,file,tn_ext
     39  FROM '.CATEGORIES_TABLE.' AS c INNER JOIN '.IMAGES_TABLE.' AS i
     40    ON i.id = c.representative_picture_id
    4041  WHERE date_last > SUBDATE(CURRENT_DATE
    4142                            ,INTERVAL '.$user['recent_period'].' DAY)';
    … …  
    6364while ( $row = mysql_fetch_array( $result ) )
    6465{
    65   $cat_infos = get_cat_info( $row['category_id'] );
    66   $name = get_cat_display_name($cat_infos['name'],'<br />','',false);
    67  
    68   $query = '
    69 SELECT path,file,tn_ext
    70   FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
    71   WHERE category_id = '.$row['category_id'].'
    72     AND date_available > SUBDATE(CURRENT_DATE
    73                                  ,INTERVAL '.$user['recent_period'].' DAY)
    74     AND id = image_id
    75   ORDER BY RAND()
    76   LIMIT 0,1
    77 ;';
    78   $subrow = mysql_fetch_array(pwg_query($query));
     66  $name = get_cat_display_name_cache($row['uppercats'], '<br />', '', false);
    7967
    80   $thumbnail_src = get_thumbnail_src($subrow['path'], @$subrow['tn_ext']);
     68  $thumbnail_src = get_thumbnail_src($row['path'], @$row['tn_ext']);
    8169 
    8270  $url_link = PHPWG_ROOT_PATH.'category.php?cat='.$row['category_id'];
    … …  
    8674    array(
    8775      'IMAGE'                   => $thumbnail_src,
    88       'IMAGE_ALT'               => $subrow['file'],
     76      'IMAGE_ALT'               => $row['file'],
    8977      'IMAGE_TITLE'             => $lang['hint_category'],
    9078      'IMAGE_NAME'              => '['.$name.']',
    9179      'IMAGE_STYLE'             => 'thumb_category',
    9280       
    93       'U_IMG_LINK'              => add_session_id( $url_link )
     81      'U_IMG_LINK'              => add_session_id($url_link)
    9482      )
    9583    );

  • trunk/include/category_subcats.inc.php

    r606 r610  
    3232 */
    3333
    34 $subcats = array();
    35 if (isset($page['cat']))
     34$query = '
     35SELECT id, name, date_last
     36  FROM '.CATEGORIES_TABLE.'
     37  WHERE id_uppercat ';
     38if (!isset($page['cat']) or !is_numeric($page['cat']))
    3639{
    37   $subcats = get_non_empty_subcat_ids($page['cat']);
     40  $query.= 'is NULL';
    3841}
    3942else
    4043{
    41   $subcats = get_non_empty_subcat_ids('');
     44  $query.= '= '.$page['cat'];
    4245}
     46// we must not show pictures of a forbidden category
     47if ($user['forbidden_categories'] != '')
     48{
     49  $query.= ' AND id NOT IN ('.$user['forbidden_categories'].')';
     50}
     51$query.= '
     52  ORDER BY rank
     53;';
     54$result = pwg_query($query);
    4355
    4456// template thumbnail initialization
    45 if (count($subcats) > 0)
     57if (mysql_num_rows($result) > 0)
    4658{
    4759  $template->assign_block_vars('thumbnails', array());
    … …  
    5264}
    5365
    54 foreach ($subcats as $subcat_id => $non_empty_id)
     66while ($row = mysql_fetch_array($result))
    5567{
    56   $name = $page['plain_structure'][$subcat_id]['name'];
     68  $query = '
     69SELECT path, tn_ext
     70  FROM '.CATEGORIES_TABLE.' AS c INNER JOIN '.IMAGES_TABLE.' AS i
     71    ON i.id = c.representative_picture_id
     72  WHERE uppercats REGEXP \'(^|,)'.$row['id'].'(,|$)\'
     73  ORDER BY RAND()
     74  LIMIT 0,1
     75;';
     76  $element_result = pwg_query($query);
     77  if (mysql_num_rows($element_result) == 0)
     78  {
     79    continue;
     80  }
     81  $element_row = mysql_fetch_array($element_result);
    5782
    58   // searching the representative picture of the category
    59   $query = '
    60 SELECT representative_picture_id
    61   FROM '.CATEGORIES_TABLE.'
    62   WHERE id = '.$non_empty_id.'
    63 ;';
    64   $row = mysql_fetch_array(pwg_query($query));
    65    
    66   $query = '
    67 SELECT file,path,tn_ext
    68   FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
    69   WHERE category_id = '.$non_empty_id.'
    70     AND id = image_id';
    71   // if the category has a representative picture, this is its thumbnail
    72   // that will be displayed !
    73   if (isset($row['representative_picture_id']))
    74   {   
    75     $query.= '
    76     AND id = '.$row['representative_picture_id'];
    77   }
    78   else
    79   {
    80     $query.= '
    81   ORDER BY RAND()
    82   LIMIT 0,1';
    83   }
    84   $query.= '
    85 ;';
    86   $image_result = pwg_query($query);
    87   $image_row    = mysql_fetch_array($image_result);
    88 
    89   $thumbnail_link = get_thumbnail_src($image_row['path'],
    90                                       @$image_row['tn_ext']);
     83  $thumbnail_link = get_thumbnail_src($element_row['path'],
     84                                      @$element_row['tn_ext']);
    9185
    9286  $thumbnail_title = $lang['hint_category'];
    9387
    94   $url_link = PHPWG_ROOT_PATH.'category.php?cat='.$subcat_id;
    95 
    96   $date = $page['plain_structure'][$subcat_id]['date_last'];
     88  $url_link = PHPWG_ROOT_PATH.'category.php?cat='.$row['id'];
    9789
    9890  $template->assign_block_vars(
    … …  
    10092    array(
    10193      'IMAGE'                 => $thumbnail_link,
    102       'IMAGE_ALT'             => $image_row['file'],
     94      'IMAGE_ALT'             => $row['name'],
    10395      'IMAGE_TITLE'           => $thumbnail_title,
    104       'IMAGE_NAME'            => '['.$name.']',
    105       'IMAGE_TS'              => get_icon($date),
     96      'IMAGE_NAME'            => '['.$row['name'].']',
     97      'IMAGE_TS'              => get_icon(@$row['date_last']),
    10698      'IMAGE_STYLE'           => 'thumb_category',
    10799       

  • trunk/include/functions_category.inc.php

    r605 r610  
    829829}
    830830
    831 // get_non_empty_subcat_ids returns an array with sub-categories id
    832 // associated with their first non empty category id.
    833 //
    834 //                          example :
    835 //
    836 // - catname [cat_id]
    837 // - cat1 [1] -> given uppercat
    838 //   - cat1.1 [12] (empty)
    839 //     - cat1.1.1 [5] (empty)
    840 //     - cat1.1.2 [6]
    841 //   - cat1.2 [3]
    842 //   - cat1.3 [4]
    843 //
    844 // get_non_empty_sub_cat_ids will return :
    845 //   $ids[12] = 6;
    846 //   $ids[3]  = 3;
    847 //   $ids[4]  = 4;
    848 function get_non_empty_subcat_ids( $id_uppercat )
    849 {
    850   global $user;
    851 
    852   $ids = array();
    853 
    854   $query = 'SELECT id,nb_images';
    855   $query.= ' FROM '.CATEGORIES_TABLE;
    856   $query.= ' WHERE id_uppercat ';
    857   if ( !is_numeric( $id_uppercat ) ) $query.= 'is NULL';
    858   else                               $query.= '= '.$id_uppercat;
    859   // we must not show pictures of a forbidden category
    860   if ( $user['forbidden_categories'] != '' )
    861   {
    862     $query.= ' AND id NOT IN ('.$user['forbidden_categories'].')';
    863   }
    864   $query.= ' ORDER BY rank';
    865   $query.= ';';
    866 
    867   $result = pwg_query( $query );
    868   while ( $row = mysql_fetch_array( $result ) )
    869   {
    870     // only categories with findable picture in any of its subcats is
    871     // represented.
    872     if ( ( $row['nb_images'] != 0 and $non_empty_cat = $row['id'] )
    873          or $non_empty_cat = get_first_non_empty_cat_id( $row['id'] ) )
    874     {
    875       $ids[$row['id']] = $non_empty_cat;
    876     }
    877   }
    878   return $ids;
    879 }
    880 
    881 // get_first_non_empty_cat_id returns the id of the first non empty
    882 // sub-category to the given uppercat. If no picture is found in any
    883 // subcategory, false is returned.
    884 function get_first_non_empty_cat_id( $id_uppercat )
    885 {
    886   global $user;
    887 
    888   $query = 'SELECT id,nb_images';
    889   $query.= ' FROM '.CATEGORIES_TABLE;
    890   $query.= ' WHERE id_uppercat = '.$id_uppercat;
    891   // we must not show pictures of a forbidden category
    892   if ( $user['forbidden_categories'] != '' )
    893   {
    894     $query.= ' AND id NOT IN ('.$user['forbidden_categories'].')';
    895   }
    896   $query.= ' ORDER BY RAND()';
    897   $query.= ';';
    898   $result = pwg_query( $query );
    899   while ( $row = mysql_fetch_array( $result ) )
    900   {
    901     if ( $row['nb_images'] > 0 )
    902     {
    903       return $row['id'];
    904     }
    905   }
    906   $result = pwg_query( $query );
    907   while ( $row = mysql_fetch_array( $result ) )
    908   {
    909     // recursive call
    910     if ( $subcat = get_first_non_empty_cat_id( $row['id'] ) )
    911     {
    912       return $subcat;
    913     }
    914   }
    915   return false;
    916 }
    917 
    918831function display_select_categories($categories,
    919832                                   $indent,

  • trunk/include/functions_html.inc.php

    r593 r610  
    198198
    199199/**
     200 * returns the list of categories as a HTML string, with cache of names
     201 *
     202 * categories string returned contains categories as given in the input
     203 * array $cat_informations. $uppercats is the list of category ids to
     204 * display in the right order. If url input parameter is empty, returns only
     205 * the categories name without links.
     206 *
     207 * @param string uppercats
     208 * @param string separator
     209 * @param string url
     210 * @param boolean replace_space
     211 * @return string
     212 */
     213function get_cat_display_name_cache($uppercats,
     214                                    $separator,
     215                                    $url = 'category.php?cat=',
     216                                    $replace_space = true)
     217{
     218  global $cat_names;
     219
     220  if (!isset($cat_names))
     221  {
     222    $query = '
     223SELECT id,name
     224  FROM '.CATEGORIES_TABLE.'
     225;';
     226    $result = pwg_query($query);
     227    while ($row = mysql_fetch_array($result))
     228    {
     229      $cat_names[$row['id']] = $row['name'];
     230    }
     231  }
     232 
     233  $output = '';
     234  $is_first = true;
     235  foreach (explode(',', $uppercats) as $category_id)
     236  {
     237    $name = $cat_names[$category_id];
     238   
     239    if ($is_first)
     240    {
     241      $is_first = false;
     242    }
     243    else
     244    {
     245      $output.= $separator;
     246    }
     247
     248    if ($url == '')
     249    {
     250      $output.= $name;
     251    }
     252    else
     253    {
     254      $output.= '
     255<a class=""
     256   href="'.add_session_id(PHPWG_ROOT_PATH.$url.$category_id).'">'.$name.'</a>';
     257    }
     258  }
     259  if ($replace_space)
     260  {
     261    return replace_space($output);
     262  }
     263  else
     264  {
     265    return $output;
     266  }
     267}
     268
     269/**
    200270 * returns the HTML code for a category item in the menu (for category.php)
    201271 *
Note: See TracChangeset for help on using the changeset viewer.