From f95f48a226118dd541130e2cec8e18fd13bff128 Mon Sep 17 00:00:00 2001
From: rvelices <rv-github@modusoptimus.com>
Date: Fri, 25 Apr 2008 23:39:06 +0000
Subject: - merge r2308 and r2309 from trunk to branch-1_7 - minor mysql query
 optimizations - less mysql queries on the picture page (under some
 circumstances)

git-svn-id: http://piwigo.org/svn/branches/branch-1_7@2310 68402e56-0260-453c-a942-63ccdbb3a9ee
---
 include/functions.inc.php     | 25 ++++++-------------------
 include/functions_tag.inc.php | 13 +++++++++++--
 include/picture_rate.inc.php  | 12 ++++++++++--
 picture.php                   | 38 ++++++++++++++++++++++++++++++--------
 4 files changed, 57 insertions(+), 31 deletions(-)

diff --git a/include/functions.inc.php b/include/functions.inc.php
index d1756a82f..9b8dea1a3 100644
--- a/include/functions.inc.php
+++ b/include/functions.inc.php
@@ -533,19 +533,6 @@ function pwg_log($image_id = null, $image_type = null)
     $tags_string = implode(',', $tag_ids);
   }
 
-  // here we ask the database the current date and time, and we extract
-  // {year, month, day} from the current date. We could do this during the
-  // insert query with a CURDATE(), CURTIME(), DATE_FORMAT(CURDATE(), '%Y')
-  // ... but I (plg) think it would cost more than a double query and a PHP
-  // extraction.
-  $query = '
-SELECT CURDATE(), CURTIME()
-;';
-  list($curdate, $curtime) = mysql_fetch_row(pwg_query($query));
-
-  list($curyear, $curmonth, $curday) = explode('-', $curdate);
-  list($curhour) = explode(':', $curtime);
-
   $query = '
 INSERT INTO '.HISTORY_TABLE.'
   (
@@ -565,12 +552,12 @@ INSERT INTO '.HISTORY_TABLE.'
   )
   VALUES
   (
-    \''.$curdate.'\',
-    \''.$curtime.'\',
-    '.$curyear.',
-    '.$curmonth.',
-    '.$curday.',
-    '.$curhour.',
+    CURDATE(),
+    CURTIME(),
+    YEAR( CURDATE() ),
+    MONTH( CURDATE() ),
+    DAYOFMONTH( CURDATE() ),
+    HOUR( CURTIME() ),
     '.$user['id'].',
     \''.$_SERVER['REMOTE_ADDR'].'\',
     '.(isset($page['section']) ? "'".$page['section']."'" : 'NULL').',
diff --git a/include/functions_tag.inc.php b/include/functions_tag.inc.php
index c6dc01db6..281517779 100644
--- a/include/functions_tag.inc.php
+++ b/include/functions_tag.inc.php
@@ -53,8 +53,12 @@ SELECT id, name, url_name, count(*) counter
           'visible_categories' => 'category_id',
           'visible_images' => 'image_id'
         ),
-      'WHERE'
-    );
+      '
+  WHERE'
+    ).'
+  GROUP BY tag_id
+  ORDER BY NULL';
+  $tag_counters = simple_hash_from_query($query, 'tag_id', 'counter');
 
   if (!empty($where_tag_img))
   {
@@ -261,6 +265,11 @@ SELECT id, name, url_name, count(*) counter
   ORDER BY counter DESC
   LIMIT 0,'.$max_tags;
   }
+  else
+  {
+    $query .= '
+  ORDER BY NULL';
+  }
 
   $result = pwg_query($query);
   $tags = array();
diff --git a/include/picture_rate.inc.php b/include/picture_rate.inc.php
index 331cb41b0..f2410945b 100644
--- a/include/picture_rate.inc.php
+++ b/include/picture_rate.inc.php
@@ -32,14 +32,22 @@
 
 if ($conf['rate'])
 {
-  $query = '
+  if ( NULL != $picture['current']['average_rate'] )
+  {
+    $query = '
 SELECT COUNT(rate) AS count
      , ROUND(AVG(rate),2) AS average
      , ROUND(STD(rate),2) AS STD
   FROM '.RATE_TABLE.'
   WHERE element_id = '.$picture['current']['id'].'
 ;';
-  $row = mysql_fetch_array(pwg_query($query));
+    $row = mysql_fetch_array(pwg_query($query));
+  }
+  else
+  { // avg rate null -> no rate -> no need to query db
+    $row = array( 'count'=>0, 'average'=>NULL, 'std'=>NULL );
+  }
+
   if ($row['count'] == 0)
   {
     $value = l10n('no_rate');
diff --git a/picture.php b/picture.php
index c933f07df..0e64d2946 100644
--- a/picture.php
+++ b/picture.php
@@ -770,18 +770,40 @@ else
 
 $template->assign_vars($infos);
 
+
 // related categories
-foreach ($related_categories as $category)
-{
+if ( count($related_categories)==1 and
+    isset($page['category']) and
+    $related_categories[0]['category_id']==$page['category']['id'] )
+{ // no need to go to db, we have all the info
   $template->assign_block_vars(
-    'category',
-    array(
-      'LINE' => count($related_categories) > 3
-        ? get_cat_display_name_cache($category['uppercats'])
-        : get_cat_display_name_from_id($category['category_id'])
-      )
+      'category',
+      array('LINE'=>get_cat_display_name( $page['category']['upper_names'] ))
     );
 }
+else
+{ // use only 1 sql query to get names for all related categories
+  $ids = array();
+  foreach ($related_categories as $category)
+  {// add all uppercats to $ids
+    $ids = array_merge($ids, explode(',', $category['uppercats']) );
+  }
+  $ids = array_unique($ids);
+  $query = '
+SELECT id, name, permalink
+  FROM '.CATEGORIES_TABLE.'
+  WHERE id IN ('.implode(',',$ids).')';
+  $cat_map = hash_from_query($query, 'id');
+  foreach ($related_categories as $category)
+  {
+    $cats = array();
+    foreach ( explode(',', $category['uppercats']) as $id )
+    {
+      $cats[] = $cat_map[$id];
+    }
+    $template->assign_block_vars('category', array('LINE'=>get_cat_display_name($cats) ) );
+  }
+}
 
 //slideshow end
 if (isset($_GET['slideshow']))
-- 
cgit v1.2.3