- 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...


git-svn-id: http://piwigo.org/svn/trunk@610 68402e56-0260-453c-a942-63ccdbb3a9ee

7 files changed, 169 insertions, 153 deletions

diff --git a/admin/include/functions.php b/admin/include/functions.php
index 63f59471b..2b28f6784 100644
--- a/admin/include/functions.php
+++ b/admin/include/functions.php
@@ -486,8 +486,8 @@ SELECT id, representative_picture_id
   WHERE representative_picture_id IS NOT NULL
     AND id IN ('.implode(',', $cat_ids).')
 ;';
-    $result = pwg_query( $query );
-    while ( $row = mysql_fetch_array( $result ) )
+    $result = pwg_query($query);
+    while ($row = mysql_fetch_array($result))
     {
       $query = '
 SELECT image_id
@@ -495,15 +495,37 @@ SELECT image_id
   WHERE category_id = '.$row['id'].'
     AND image_id = '.$row['representative_picture_id'].'
 ;';
-      $result = pwg_query( $query );
-      if (mysql_num_rows($result) == 0)
+      $sub_result = pwg_query($query);
+      if (mysql_num_rows($sub_result) == 0)
       {
+        // set a new representative element for this category
         $query = '
+SELECT image_id
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE category_id = '.$row['id'].'
+  ORDER BY RAND()
+  LIMIT 0,1
+;';
+        $sub_sub_result = pwg_query($query);
+        if (mysql_num_rows($sub_sub_result) > 0)
+        {
+          list($representative) = mysql_fetch_array(pwg_query($query));
+          $query = '
+UPDATE '.CATEGORIES_TABLE.'
+  SET representative_picture_id = '.$representative.'
+  WHERE id = '.$row['id'].'
+;';
+          pwg_query($query);
+        }
+        else
+        {
+          $query = '
 UPDATE '.CATEGORIES_TABLE.'
   SET representative_picture_id = NULL
   WHERE id = '.$row['id'].'
 ;';
-        pwg_query( $query );
+          pwg_query($query);
+        }
       }
     }
   }
diff --git a/admin/remote_site.php b/admin/remote_site.php
index 082658ab0..19db458b4 100644
--- a/admin/remote_site.php
+++ b/admin/remote_site.php
@@ -432,6 +432,21 @@ INSERT INTO '.IMAGE_CATEGORY_TABLE.'
     $query.= '
 ;';
     pwg_query($query);
+    // set a new representative element for this category
+    $query = '
+SELECT image_id
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE category_id = '.$category_id.'
+  ORDER BY RAND()
+  LIMIT 0,1
+;';
+    list($representative) = mysql_fetch_array(pwg_query($query));
+    $query = '
+UPDATE '.CATEGORIES_TABLE.'
+  SET representative_picture_id = '.$representative.'
+  WHERE id = '.$category_id.'
+;';
+    pwg_query($query);
   }
 }
 // +-----------------------------------------------------------------------+
diff --git a/admin/update.php b/admin/update.php
index 817a67aae..10813349e 100644
--- a/admin/update.php
+++ b/admin/update.php
@@ -514,6 +514,22 @@ INSERT INTO '.IMAGE_CATEGORY_TABLE.'
   '.implode(',', $ids).'
 ;';
     pwg_query($query);
+
+    // set a new representative element for this category
+    $query = '
+SELECT image_id
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE category_id = '.$category_id.'
+  ORDER BY RAND()
+  LIMIT 0,1
+;';
+    list($representative) = mysql_fetch_array(pwg_query($query));
+    $query = '
+UPDATE '.CATEGORIES_TABLE.'
+  SET representative_picture_id = '.$representative.'
+  WHERE id = '.$category_id.'
+;';
+    pwg_query($query);
   }
   return $output;
 }
diff --git a/include/category_recent_cats.inc.php b/include/category_recent_cats.inc.php
index 3da82c1f8..ed193e380 100644
--- a/include/category_recent_cats.inc.php
+++ b/include/category_recent_cats.inc.php
@@ -35,8 +35,9 @@
 // recently. The calculated table field categories.date_last will be
 // easier to use
 $query = '
-SELECT id AS category_id
-  FROM '.CATEGORIES_TABLE.'
+SELECT c.id AS category_id,uppercats,representative_picture_id,path,file,tn_ext
+  FROM '.CATEGORIES_TABLE.' AS c INNER JOIN '.IMAGES_TABLE.' AS i
+    ON i.id = c.representative_picture_id
   WHERE date_last > SUBDATE(CURRENT_DATE
                             ,INTERVAL '.$user['recent_period'].' DAY)';
 if ( $user['forbidden_categories'] != '' )
@@ -62,22 +63,9 @@ if (mysql_num_rows($result) > 0)
 // the name to display
 while ( $row = mysql_fetch_array( $result ) )
 {
-  $cat_infos = get_cat_info( $row['category_id'] );
-  $name = get_cat_display_name($cat_infos['name'],'<br />','',false);
-  
-  $query = '
-SELECT path,file,tn_ext
-  FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
-  WHERE category_id = '.$row['category_id'].'
-    AND date_available > SUBDATE(CURRENT_DATE
-                                 ,INTERVAL '.$user['recent_period'].' DAY)
-    AND id = image_id
-  ORDER BY RAND()
-  LIMIT 0,1
-;';
-  $subrow = mysql_fetch_array(pwg_query($query));
+  $name = get_cat_display_name_cache($row['uppercats'], '<br />', '', false);
 
-  $thumbnail_src = get_thumbnail_src($subrow['path'], @$subrow['tn_ext']);
+  $thumbnail_src = get_thumbnail_src($row['path'], @$row['tn_ext']);
   
   $url_link = PHPWG_ROOT_PATH.'category.php?cat='.$row['category_id'];
   
@@ -85,12 +73,12 @@ SELECT path,file,tn_ext
     'thumbnails.line.thumbnail',
     array(
       'IMAGE'                   => $thumbnail_src,
-      'IMAGE_ALT'               => $subrow['file'],
+      'IMAGE_ALT'               => $row['file'],
       'IMAGE_TITLE'             => $lang['hint_category'],
       'IMAGE_NAME'              => '['.$name.']',
       'IMAGE_STYLE'             => 'thumb_category',
         
-      'U_IMG_LINK'              => add_session_id( $url_link )
+      'U_IMG_LINK'              => add_session_id($url_link)
       )
     );
   $template->assign_block_vars('thumbnails.line.thumbnail.bullet',array());
diff --git a/include/category_subcats.inc.php b/include/category_subcats.inc.php
index 6fe0eece7..7e6d080d8 100644
--- a/include/category_subcats.inc.php
+++ b/include/category_subcats.inc.php
@@ -31,18 +31,30 @@
  * 
  */
 
-$subcats = array();
-if (isset($page['cat']))
+$query = '
+SELECT id, name, date_last
+  FROM '.CATEGORIES_TABLE.'
+  WHERE id_uppercat ';
+if (!isset($page['cat']) or !is_numeric($page['cat']))
 {
-  $subcats = get_non_empty_subcat_ids($page['cat']);
+  $query.= 'is NULL';
 }
 else
 {
-  $subcats = get_non_empty_subcat_ids('');
+  $query.= '= '.$page['cat'];
+}
+// we must not show pictures of a forbidden category
+if ($user['forbidden_categories'] != '')
+{
+  $query.= ' AND id NOT IN ('.$user['forbidden_categories'].')';
 }
+$query.= '
+  ORDER BY rank
+;';
+$result = pwg_query($query);
 
 // template thumbnail initialization
-if (count($subcats) > 0)
+if (mysql_num_rows($result) > 0)
 {
   $template->assign_block_vars('thumbnails', array());
   // first line
@@ -51,58 +63,38 @@ if (count($subcats) > 0)
   $row_number = 0;
 }
 
-foreach ($subcats as $subcat_id => $non_empty_id) 
+while ($row = mysql_fetch_array($result))
 {
-  $name = $page['plain_structure'][$subcat_id]['name'];
-
-  // searching the representative picture of the category
   $query = '
-SELECT representative_picture_id
-  FROM '.CATEGORIES_TABLE.'
-  WHERE id = '.$non_empty_id.'
+SELECT path, tn_ext
+  FROM '.CATEGORIES_TABLE.' AS c INNER JOIN '.IMAGES_TABLE.' AS i
+    ON i.id = c.representative_picture_id
+  WHERE uppercats REGEXP \'(^|,)'.$row['id'].'(,|$)\'
+  ORDER BY RAND()
+  LIMIT 0,1
 ;';
-  $row = mysql_fetch_array(pwg_query($query));
-    
-  $query = '
-SELECT file,path,tn_ext
-  FROM '.IMAGES_TABLE.', '.IMAGE_CATEGORY_TABLE.'
-  WHERE category_id = '.$non_empty_id.'
-    AND id = image_id';
-  // if the category has a representative picture, this is its thumbnail
-  // that will be displayed !
-  if (isset($row['representative_picture_id']))
-  {   
-    $query.= '
-    AND id = '.$row['representative_picture_id'];
-  }
-  else
+  $element_result = pwg_query($query);
+  if (mysql_num_rows($element_result) == 0)
   {
-    $query.= '
-  ORDER BY RAND()
-  LIMIT 0,1';
+    continue;
   }
-  $query.= '
-;';
-  $image_result = pwg_query($query);
-  $image_row    = mysql_fetch_array($image_result);
+  $element_row = mysql_fetch_array($element_result);
 
-  $thumbnail_link = get_thumbnail_src($image_row['path'],
-                                      @$image_row['tn_ext']);
+  $thumbnail_link = get_thumbnail_src($element_row['path'],
+                                      @$element_row['tn_ext']);
 
   $thumbnail_title = $lang['hint_category'];
 
-  $url_link = PHPWG_ROOT_PATH.'category.php?cat='.$subcat_id;
-
-  $date = $page['plain_structure'][$subcat_id]['date_last'];
+  $url_link = PHPWG_ROOT_PATH.'category.php?cat='.$row['id'];
 
   $template->assign_block_vars(
     'thumbnails.line.thumbnail',
     array(
       'IMAGE'                 => $thumbnail_link,
-      'IMAGE_ALT'             => $image_row['file'],
+      'IMAGE_ALT'             => $row['name'],
       'IMAGE_TITLE'           => $thumbnail_title,
-      'IMAGE_NAME'            => '['.$name.']',
-      'IMAGE_TS'              => get_icon($date),
+      'IMAGE_NAME'            => '['.$row['name'].']',
+      'IMAGE_TS'              => get_icon(@$row['date_last']),
       'IMAGE_STYLE'           => 'thumb_category',
         
       'U_IMG_LINK'            => add_session_id($url_link)
diff --git a/include/functions_category.inc.php b/include/functions_category.inc.php
index 330137d6d..d46ddc104 100644
--- a/include/functions_category.inc.php
+++ b/include/functions_category.inc.php
@@ -828,93 +828,6 @@ SELECT COUNT(1) AS count
   pwg_debug( 'end initialize_category' );
 }
 
-// get_non_empty_subcat_ids returns an array with sub-categories id
-// associated with their first non empty category id.
-//
-//                          example :
-//
-// - catname [cat_id]
-// - cat1 [1] -> given uppercat
-//   - cat1.1 [12] (empty)
-//     - cat1.1.1 [5] (empty)
-//     - cat1.1.2 [6]
-//   - cat1.2 [3]
-//   - cat1.3 [4]
-//
-// get_non_empty_sub_cat_ids will return :
-//   $ids[12] = 6;
-//   $ids[3]  = 3;
-//   $ids[4]  = 4;
-function get_non_empty_subcat_ids( $id_uppercat )
-{
-  global $user;
-
-  $ids = array();
-
-  $query = 'SELECT id,nb_images';
-  $query.= ' FROM '.CATEGORIES_TABLE;
-  $query.= ' WHERE id_uppercat ';
-  if ( !is_numeric( $id_uppercat ) ) $query.= 'is NULL';
-  else                               $query.= '= '.$id_uppercat;
-  // we must not show pictures of a forbidden category
-  if ( $user['forbidden_categories'] != '' )
-  {
-    $query.= ' AND id NOT IN ('.$user['forbidden_categories'].')';
-  }
-  $query.= ' ORDER BY rank';
-  $query.= ';';
-
-  $result = pwg_query( $query );
-  while ( $row = mysql_fetch_array( $result ) )
-  {
-    // only categories with findable picture in any of its subcats is
-    // represented.
-    if ( ( $row['nb_images'] != 0 and $non_empty_cat = $row['id'] )
-         or $non_empty_cat = get_first_non_empty_cat_id( $row['id'] ) )
-    {
-      $ids[$row['id']] = $non_empty_cat;
-    }
-  }
-  return $ids;
-}
-
-// get_first_non_empty_cat_id returns the id of the first non empty
-// sub-category to the given uppercat. If no picture is found in any
-// subcategory, false is returned.
-function get_first_non_empty_cat_id( $id_uppercat )
-{
-  global $user;
-
-  $query = 'SELECT id,nb_images';
-  $query.= ' FROM '.CATEGORIES_TABLE;
-  $query.= ' WHERE id_uppercat = '.$id_uppercat;
-  // we must not show pictures of a forbidden category
-  if ( $user['forbidden_categories'] != '' )
-  {
-    $query.= ' AND id NOT IN ('.$user['forbidden_categories'].')';
-  }
-  $query.= ' ORDER BY RAND()';
-  $query.= ';';
-  $result = pwg_query( $query );
-  while ( $row = mysql_fetch_array( $result ) )
-  {
-    if ( $row['nb_images'] > 0 )
-    {
-      return $row['id'];
-    }
-  }
-  $result = pwg_query( $query );
-  while ( $row = mysql_fetch_array( $result ) )
-  {
-    // recursive call
-    if ( $subcat = get_first_non_empty_cat_id( $row['id'] ) )
-    {
-      return $subcat;
-    }
-  }
-  return false;
-}
-
 function display_select_categories($categories,
                                    $indent,
                                    $selecteds,
diff --git a/include/functions_html.inc.php b/include/functions_html.inc.php
index 30e07e4d1..ccf6c7435 100644
--- a/include/functions_html.inc.php
+++ b/include/functions_html.inc.php
@@ -197,6 +197,76 @@ function get_cat_display_name($cat_informations,
 }
 
 /**
+ * returns the list of categories as a HTML string, with cache of names
+ *
+ * categories string returned contains categories as given in the input
+ * array $cat_informations. $uppercats is the list of category ids to
+ * display in the right order. If url input parameter is empty, returns only
+ * the categories name without links.
+ *
+ * @param string uppercats
+ * @param string separator
+ * @param string url
+ * @param boolean replace_space
+ * @return string
+ */
+function get_cat_display_name_cache($uppercats,
+                                    $separator, 
+                                    $url = 'category.php?cat=',
+                                    $replace_space = true)
+{
+  global $cat_names;
+
+  if (!isset($cat_names))
+  {
+    $query = '
+SELECT id,name
+  FROM '.CATEGORIES_TABLE.'
+;';
+    $result = pwg_query($query);
+    while ($row = mysql_fetch_array($result))
+    {
+      $cat_names[$row['id']] = $row['name'];
+    }
+  }
+  
+  $output = '';
+  $is_first = true;
+  foreach (explode(',', $uppercats) as $category_id)
+  {
+    $name = $cat_names[$category_id];
+    
+    if ($is_first)
+    {
+      $is_first = false;
+    }
+    else
+    {
+      $output.= $separator;
+    }
+
+    if ($url == '')
+    {
+      $output.= $name;
+    }
+    else
+    {
+      $output.= '
+<a class=""
+   href="'.add_session_id(PHPWG_ROOT_PATH.$url.$category_id).'">'.$name.'</a>';
+    }
+  }
+  if ($replace_space)
+  {
+    return replace_space($output);
+  }
+  else
+  {
+    return $output;
+  }
+}
+
+/**
  * returns the HTML code for a category item in the menu (for category.php)
  *
  * HTML code generated uses logical list tags ul and each category is an