Changeset 491 for trunk

Timestamp:

Aug 21, 2004, 3:20:28 PM (20 years ago)

Author:

z0rglub

Message:

• deletion functions updated : no needs to call delete_elements if no element must be deleted

• improvement of function update_category : the function is not recursive anymore, but still works on all sub-categories thanks to a SQL query on uppercats database field. Less queries : for updating N categories, only (1+N) SQL queries compared to (3*N) in the previous revision.

• function my_error added (debug purpose)

File:

• trunk/admin/include/functions.php (modified) (9 diffs)

trunk/admin/include/functions.php

@@ -26 +26 @@
 // +-----------------------------------------------------------------------+
 
+include(PHPWG_ROOT_PATH.'admin/include/functions_metadata.php');
+
 $tab_ext_create_TN = array ( 'jpg', 'png', 'JPG', 'PNG' );
 
@@ -203 +205 @@
     array_push($element_ids, $row['id']);
   }
-  delete_elements($element_ids);
+  if (count($element_ids) > 0)
+  {
+    delete_elements($element_ids);
+  }
 
   // destruction of the links between images and this category
@@ -236 +241 @@
     array_push($subcat_ids, $row['id']);
   }
-  delete_categories($subcat_ids);
+  if (count($subcat_ids) > 0)
+  {
+    delete_categories($subcat_ids);
+  }
 
   // destruction of the category
@@ -254 +262 @@
 function delete_elements($ids)
 {
+  global $count_deleted;
+  
   // destruction of the comments on the image
   $query = '
 DELETE FROM '.COMMENTS_TABLE.'
-  WHERE image_id IN ('.implode(',', $ids).')
-;';
-  echo '<pre>'.$query.'</pre>';
+  WHERE image_id IN (
+'.wordwrap(implode(', ', $ids), 80, "\n").')
+;';
   mysql_query($query);
 
@@ -265 +275 @@
   $query = '
 DELETE FROM '.IMAGE_CATEGORY_TABLE.'
-  WHERE image_id IN ('.implode(',', $ids).')
+  WHERE image_id IN (
+'.wordwrap(implode(', ', $ids), 80, "\n").')
 ;';
   mysql_query($query);
@@ -272 +283 @@
   $query = '
 DELETE FROM '.FAVORITES_TABLE.'
-  WHERE image_id IN ('.implode(',', $ids).')
+  WHERE image_id IN (
+'.wordwrap(implode(', ', $ids), 80, "\n").')
 ;';
   mysql_query($query);
@@ -279 +291 @@
   $query = '
 DELETE FROM '.IMAGES_TABLE.'
-  WHERE id IN ('.implode(',', $ids).')
+  WHERE id IN (
+'.wordwrap(implode(', ', $ids), 80, "\n").')
 ;';
   mysql_query($query);
+
+  $count_deleted+= count($ids);
 }
 
@@ -402 +417 @@
 }
 
-// update_category updates calculated informations about a category :
-// date_last and nb_images. It also verifies that the representative picture
-// is really linked to the category.
-function update_category( $id = 'all' )
-{
-  if ( $id == 'all' )
-  {
-    $query = 'SELECT id FROM '.CATEGORIES_TABLE.';';
+/**
+ * updates calculated informations about a category : date_last and
+ * nb_images. It also verifies that the representative picture is really
+ * linked to the category. Recursive.
+ *
+ * @param mixed category id
+ * @returns void
+ */
+function update_category($id = 'all')
+{
+  $cat_ids = array();
+  
+  $query = '
+SELECT category_id, COUNT(image_id) AS count, max(date_available) AS date_last
+  FROM '.IMAGES_TABLE.'
+    INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id';
+  if (is_numeric($id))
+  {
+    $query.= '
+  WHERE uppercats REGEXP \'(^|,)'.$id.'(,|$)\'';
+  }
+  $query.= '
+  GROUP BY category_id
+;';
+  $result = mysql_query( $query );
+  while ( $row = mysql_fetch_array( $result ) )
+  {
+    array_push($cat_ids, $row['category_id']);
+    $query = '
+UPDATE '.CATEGORIES_TABLE.'
+  SET date_last = \''.$row['date_last'].'\'
+    , nb_images = '.$row['count'].'
+  WHERE id = '.$row['category_id'].'
+;';
+    mysql_query($query);
+  }
+
+  if (count($cat_ids) > 0)
+  {
+    $query = '
+SELECT id, representative_picture_id
+  FROM '.CATEGORIES_TABLE.'
+  WHERE representative_picture_id IS NOT NULL
+    AND id IN ('.implode(',', $cat_ids).')
+;';
     $result = mysql_query( $query );
     while ( $row = mysql_fetch_array( $result ) )
     {
-      // recursive call
-      update_category( $row['id'] );
-    }
-  }
-  else if ( is_numeric( $id ) )
-  {
-    // updating the number of pictures
-    $query = 'SELECT COUNT(*) as nb_images';
-    $query.= ' FROM '.PREFIX_TABLE.'image_category';
-    $query.= ' WHERE category_id = '.$id;
-    $query.= ';';
-    list( $nb_images ) = mysql_fetch_array( mysql_query( $query ) );
-    // updating the date_last
-    $query = 'SELECT MAX(date_available) AS date_available';
-    $query.= ' FROM '.PREFIX_TABLE.'images';
-    $query.= ' INNER JOIN '.PREFIX_TABLE.'image_category ON id = image_id';
-    $query.= ' WHERE category_id = '.$id;
-    $query.= ';';
-    list( $date_available ) = mysql_fetch_array( mysql_query( $query ) );
-    
-    $query = 'UPDATE '.CATEGORIES_TABLE;
-    $query.= " SET date_last = '".$date_available."'";
-    $query.= ', nb_images = '.$nb_images;
-    $query.= ' WHERE id = '.$id;
-    $query.= ';';
-    mysql_query( $query );
-
-    // updating the representative_picture_id : if the representative
-    // picture of the category is not any more linked to the category, we
-    // have to set representative_picture_id to NULL
-    $query = 'SELECT representative_picture_id';
-    $query.= ' FROM '.CATEGORIES_TABLE;
-    $query.= ' WHERE id = '.$id;
-    $row = mysql_fetch_array( mysql_query( $query ) );
-    // if the category has no representative picture (ie
-    // representative_picture_id == NULL) we don't update anything
-    if ( isset( $row['representative_picture_id'] ) )
-    {
-      $query = 'SELECT image_id';
-      $query.= ' FROM '.PREFIX_TABLE.'image_category';
-      $query.= ' WHERE category_id = '.$id;
-      $query.= ' AND image_id = '.$row['representative_picture_id'];
-      $query.= ';';
+      $query = '
+SELECT image_id
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE category_id = '.$row['id'].'
+    AND image_id = '.$row['representative_picture_id'].'
+;';
       $result = mysql_query( $query );
-      if ( mysql_num_rows( $result ) == 0 )
+      if (mysql_num_rows($result) == 0)
       {
-        $query = 'UPDATE '.CATEGORIES_TABLE;
-        $query.= ' SET representative_picture_id = NULL';
-        $query.= ' WHERE id = '.$id;
-        $query.= ';';
+        $query = '
+UPDATE '.CATEGORIES_TABLE.'
+  SET representative_picture_id = NULL
+  WHERE id = '.$row['id'].'
+;';
         mysql_query( $query );
       }
@@ -882 +898 @@
   return $sub_dirs;
 }
+
+// my_error returns (or send to standard output) the message concerning the
+// error occured for the last mysql query.
+function my_error($header, $echo = true)
+{
+  $error = $header.'<span style="font-weight:bold;">N°= '.mysql_errno();
+  $error.= ' -->> '.mysql_error()."</span><br /><br />\n";
+  if ($echo)
+  {
+    echo $error;
+  }
+  else
+  {
+    return $error;
+  }
+}
 ?>