aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorplegall <plg@piwigo.org>2004-11-20 17:23:42 +0000
committerplegall <plg@piwigo.org>2004-11-20 17:23:42 +0000
commit13cd251e63232e88069759e8396b0c35b991088b (patch)
treeabf65955235f108908dc95e37678e2404ff87115
parentf39c3af29b7d44fa11b0670bb8ad6f7e9b7c53d0 (diff)
- 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
-rw-r--r--admin/include/functions.php32
-rw-r--r--admin/remote_site.php15
-rw-r--r--admin/update.php16
-rw-r--r--include/category_recent_cats.inc.php26
-rw-r--r--include/category_subcats.inc.php76
-rw-r--r--include/functions_category.inc.php87
-rw-r--r--include/functions_html.inc.php70
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