Changeset 2310

Timestamp:

Apr 26, 2008, 1:39:06 AM (16 years ago)

Author:

rvelices

Message:

• merge r2308 and r2309 from trunk to branch-1_7
• minor mysql query optimizations
• less mysql queries on the picture page (under some circumstances)

Location:

branches/branch-1_7

Files:

• include/functions.inc.php (modified) (2 diffs)
• include/functions_tag.inc.php (modified) (2 diffs)
• include/picture_rate.inc.php (modified) (2 diffs)
• picture.php (modified) (1 diff)

branches/branch-1_7/include/functions.inc.php

@@ -534 +534 @@
   }
 
-  // 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.'
@@ -566 +553 @@
   VALUES
   (
-    \''.$curdate.'\',
-    \''.$curtime.'\',
-    '.$curyear.',
-    '.$curmonth.',
-    '.$curday.',
-    '.$curhour.',
+    CURDATE(),
+    CURTIME(),
+    YEAR( CURDATE() ),
+    MONTH( CURDATE() ),
+    DAYOFMONTH( CURDATE() ),
+    HOUR( CURTIME() ),
     '.$user['id'].',
     \''.$_SERVER['REMOTE_ADDR'].'\',

branches/branch-1_7/include/functions_tag.inc.php

@@ -54 +54 @@
           '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 +265 @@
   ORDER BY counter DESC
   LIMIT 0,'.$max_tags;
+  }
+  else
+  {
+    $query .= '
+  ORDER BY NULL';
   }
 

branches/branch-1_7/include/picture_rate.inc.php

@@ -33 +33 @@
 if ($conf['rate'])
 {
-  $query = '
+  if ( NULL != $picture['current']['average_rate'] )
+  {
+    $query = '
 SELECT COUNT(rate) AS count
      , ROUND(AVG(rate),2) AS average
@@ -40 +42 @@
   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)
   {

branches/branch-1_7/picture.php

@@ -771 +771 @@
 $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) ) );
+  }
 }