Feature 1255: modification in sql queries

    - manage random function
    - manage regex syntax
    - manage quote (single instead of double)
    - manage interval

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

5 files changed, 11 insertions, 17 deletions

diff --git a/admin/cat_modify.php b/admin/cat_modify.php
index 06a001253..5dc39954c 100644
--- a/admin/cat_modify.php
+++ b/admin/cat_modify.php
@@ -67,7 +67,7 @@ if (isset($_POST['submit']))
     array(
       'id' => $_GET['cat_id'],
       'name' => @$_POST['name'],
-      'commentable' => $_POST['commentable'],
+      'commentable' => isset($_POST['commentable'])?$_POST['commentable']:'false',
       'uploadable' =>
         isset($_POST['uploadable']) ? $_POST['uploadable'] : 'false',
       'comment' =>
diff --git a/admin/element_set.php b/admin/element_set.php
index 1c84854d5..ce9ce63da 100644
--- a/admin/element_set.php
+++ b/admin/element_set.php
@@ -196,7 +196,7 @@ elseif ('recent'== $_GET['cat'])
   {
     $query = 'SELECT id
   FROM '.IMAGES_TABLE.'
-  WHERE date_available BETWEEN DATE_SUB("'.$row['date'].'", INTERVAL 1 DAY) AND "'.$row['date'].'"';
+  WHERE date_available BETWEEN '.pwg_db_get_recent_period_expression(1, $row['date']).' AND \''.$row['date'].'\'';
     $page['cat_elements_id'] = array_from_query($query, 'id');
   }
 }
diff --git a/admin/include/functions_metadata.php b/admin/include/functions_metadata.php
index f1e9f2549..a4d5588d4 100644
--- a/admin/include/functions_metadata.php
+++ b/admin/include/functions_metadata.php
@@ -246,7 +246,7 @@ SELECT id
     if ($recursive)
     {
       $query.= '
-    AND uppercats REGEXP \'(^|,)'.$category_id.'(,|$)\'
+    AND uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$category_id.'(,|$)\'
 ';
     }
     else
diff --git a/admin/menubar.php b/admin/menubar.php
index f6ebeab98..a0bb8ee03 100644
--- a/admin/menubar.php
+++ b/admin/menubar.php
@@ -69,8 +69,8 @@ if ( isset($_POST['reset']) and !is_adviser())
   $mb_conf = array();
   $query = '
 UPDATE '.CONFIG_TABLE.'
-  SET value=""
-  WHERE param="blk_'.addslashes($menu->get_id()).'"
+  SET value=\'\'
+  WHERE param=\'blk_'.addslashes($menu->get_id()).'\'
   LIMIT 1';
   pwg_query($query);
 }
@@ -143,9 +143,9 @@ if ( isset($_POST['submit']) and !is_adviser() )
   */
   $query = '
 UPDATE '.CONFIG_TABLE.'
-  SET value="'.addslashes(serialize($mb_conf_db)).'"
-  WHERE param="blk_'.addslashes($menu->get_id()).'"
-  LIMIT 1';
+  SET value=\''.addslashes(serialize($mb_conf_db)).'\'
+  WHERE param=\'blk_'.addslashes($menu->get_id()).'\'
+  ';
   pwg_query($query);
 }
 
diff --git a/admin/site_update.php b/admin/site_update.php
index 84c6a8f87..b4980595d 100644
--- a/admin/site_update.php
+++ b/admin/site_update.php
@@ -148,7 +148,7 @@ SELECT id, uppercats, global_rank, status, visible
     if (isset($_POST['subcats-included']) and $_POST['subcats-included'] == 1)
     {
       $query.= '
-    AND uppercats REGEXP \'(^|,)'.$_POST['cat'].'(,|$)\'
+    AND uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$_POST['cat'].'(,|$)\'
 ';
     }
     else
@@ -207,10 +207,7 @@ SELECT id_uppercat, MAX(rank)+1 AS next_rank
   }
 
   // next category id available
-  $query = '
-SELECT IF(MAX(id)+1 IS NULL, 1, MAX(id)+1) AS next_id
-  FROM '.CATEGORIES_TABLE;
-  list($next_id) = pwg_db_fetch_row(pwg_query($query));
+  $next_id = pwg_db_nextval('id', CATEGORIES_TABLE);
 
   // retrieve sub-directories fulldirs from the site reader
   $fs_fulldirs = $site_reader->get_full_directories($basedir);
@@ -399,10 +396,7 @@ SELECT file,storage_category_id
   }
 
   // next element id available
-  $query = '
-SELECT IF(MAX(id)+1 IS NULL, 1, MAX(id)+1) AS next_element_id
-  FROM '.IMAGES_TABLE;
-  list($next_element_id) = pwg_db_fetch_row(pwg_query($query));
+  $next_element_id = pwg_db_nextval('id', IMAGES_TABLE);
 
   $start = get_moment();