Changeset 4367


Ignore:
Timestamp:
11/25/09 20:02:57 (10 years ago)
Author:
nikrou
Message:

Feature 1255: modification in sql queries

  • manage random function
  • manage regex syntax
  • manage quote (single instead of double)
  • manage interval
Location:
trunk
Files:
20 edited

Legend:

Unmodified
Added
Removed
  • trunk/admin/cat_modify.php

    r4325 r4367  
    6868      'id' => $_GET['cat_id'], 
    6969      'name' => @$_POST['name'], 
    70       'commentable' => $_POST['commentable'], 
     70      'commentable' => isset($_POST['commentable'])?$_POST['commentable']:'false', 
    7171      'uploadable' => 
    7272        isset($_POST['uploadable']) ? $_POST['uploadable'] : 'false', 
  • trunk/admin/element_set.php

    r4325 r4367  
    197197    $query = 'SELECT id 
    198198  FROM '.IMAGES_TABLE.' 
    199   WHERE date_available BETWEEN DATE_SUB("'.$row['date'].'", INTERVAL 1 DAY) AND "'.$row['date'].'"'; 
     199  WHERE date_available BETWEEN '.pwg_db_get_recent_period_expression(1, $row['date']).' AND \''.$row['date'].'\''; 
    200200    $page['cat_elements_id'] = array_from_query($query, 'id'); 
    201201  } 
  • trunk/admin/include/functions_metadata.php

    r4325 r4367  
    247247    { 
    248248      $query.= ' 
    249     AND uppercats REGEXP \'(^|,)'.$category_id.'(,|$)\' 
     249    AND uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$category_id.'(,|$)\' 
    250250'; 
    251251    } 
  • trunk/admin/menubar.php

    r3711 r4367  
    7070  $query = ' 
    7171UPDATE '.CONFIG_TABLE.' 
    72   SET value="" 
    73   WHERE param="blk_'.addslashes($menu->get_id()).'" 
     72  SET value=\'\' 
     73  WHERE param=\'blk_'.addslashes($menu->get_id()).'\' 
    7474  LIMIT 1'; 
    7575  pwg_query($query); 
     
    144144  $query = ' 
    145145UPDATE '.CONFIG_TABLE.' 
    146   SET value="'.addslashes(serialize($mb_conf_db)).'" 
    147   WHERE param="blk_'.addslashes($menu->get_id()).'" 
    148   LIMIT 1'; 
     146  SET value=\''.addslashes(serialize($mb_conf_db)).'\' 
     147  WHERE param=\'blk_'.addslashes($menu->get_id()).'\' 
     148  '; 
    149149  pwg_query($query); 
    150150} 
  • trunk/admin/site_update.php

    r4325 r4367  
    149149    { 
    150150      $query.= ' 
    151     AND uppercats REGEXP \'(^|,)'.$_POST['cat'].'(,|$)\' 
     151    AND uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$_POST['cat'].'(,|$)\' 
    152152'; 
    153153    } 
     
    208208 
    209209  // next category id available 
    210   $query = ' 
    211 SELECT IF(MAX(id)+1 IS NULL, 1, MAX(id)+1) AS next_id 
    212   FROM '.CATEGORIES_TABLE; 
    213   list($next_id) = pwg_db_fetch_row(pwg_query($query)); 
     210  $next_id = pwg_db_nextval('id', CATEGORIES_TABLE); 
    214211 
    215212  // retrieve sub-directories fulldirs from the site reader 
     
    400397 
    401398  // next element id available 
    402   $query = ' 
    403 SELECT IF(MAX(id)+1 IS NULL, 1, MAX(id)+1) AS next_element_id 
    404   FROM '.IMAGES_TABLE; 
    405   list($next_element_id) = pwg_db_fetch_row(pwg_query($query)); 
     399  $next_element_id = pwg_db_nextval('id', IMAGES_TABLE); 
    406400 
    407401  $start = get_moment(); 
  • trunk/comments.php

    r4334 r4367  
    5252$since_options = array( 
    5353  1 => array('label' => l10n('today'), 
    54              'clause' => 'date > SUBDATE(CURDATE(), INTERVAL 1 DAY)'), 
     54             'clause' => 'date > '.pwg_db_get_recent_period_expression(1)), 
    5555  2 => array('label' => sprintf(l10n('last %d days'), 7), 
    56              'clause' => 'date > SUBDATE(CURDATE(), INTERVAL 7 DAY)'), 
     56             'clause' => 'date > '.pwg_db_get_recent_period_expression(7)), 
    5757  3 => array('label' => sprintf(l10n('last %d days'), 30), 
    58              'clause' => 'date > SUBDATE(CURDATE(), INTERVAL 30 DAY)'), 
     58             'clause' => 'date > '.pwg_db_get_recent_period_expression(30)), 
    5959  4 => array('label' => l10n('the beginning'), 
    6060             'clause' => '1=1') // stupid but generic 
     
    139139if ( !is_admin() ) 
    140140{ 
    141   $page['where_clauses'][] = 'validated="true"'; 
     141  $page['where_clauses'][] = 'validated=\'true\''; 
    142142} 
    143143 
  • trunk/feed.php

    r4325 r4367  
    157157    $query = ' 
    158158UPDATE '.USER_FEED_TABLE.' 
    159   SET last_check = DATE_ADD(\''.$dbnow.'\', INTERVAL -15 DAY ) 
     159  SET last_check = '.pwg_db_get_recent_period_expression(-15, $dbnow).' 
    160160  WHERE id = \''.$feed_id.'\' 
    161161;'; 
  • trunk/include/calendar_base.class.php

    r3282 r4367  
    269269    if ( empty($page['chronology_date']) ) 
    270270      return; 
    271     $query = 'SELECT CONCAT_WS("-"'; 
     271    $query = 'SELECT CONCAT_WS(\'-\''; 
    272272    for ($i=0; $i<count($page['chronology_date']); $i++) 
    273273    { 
  • trunk/include/calendar_monthly.class.php

    r4334 r4367  
    348348    $query.= $this->get_date_where(); 
    349349    $query.= ' 
    350   ORDER BY RAND() 
     350  ORDER BY '.DB_RANDOM_FUNCTION.'() 
    351351  LIMIT 1'; 
    352352    unset ( $page['chronology_date'][CDAY] ); 
  • trunk/include/category_cats.inc.php

    r4334 r4367  
    3636  FROM '.CATEGORIES_TABLE.' c INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.' 
    3737  ON id = cat_id and user_id = '.$user['id'].' 
    38   WHERE date_last >= SUBDATE( 
    39     CURRENT_DATE,INTERVAL '.$user['recent_period'].' DAY 
    40   ) 
     38  WHERE date_last >= '.pwg_db_get_recent_period_expression($user['recent_period']).' 
    4139'.get_sql_condition_FandF 
    4240  ( 
     
    105103      "\n  AND" 
    106104    ).' 
    107   ORDER BY RAND() 
     105  ORDER BY '.DB_RANDOM_FUNCTION.'() 
    108106  LIMIT 1 
    109107;'; 
     
    133131      "\n  AND" 
    134132    ).' 
    135     ORDER BY RAND() 
     133    ORDER BY '.DB_RANDOM_FUNCTION.'() 
    136134    LIMIT 1 
    137135  ;'; 
  • trunk/include/dblayer/functions_mysql.inc.php

    r4335 r4367  
    2222// +-----------------------------------------------------------------------+ 
    2323 
     24define('DB_ENGINE', 'MySQL'); 
     25 
     26define('DB_REGEX_OPERATOR', 'REGEXP'); 
     27define('DB_RANDOM_FUNCTION', 'RAND'); 
     28 
    2429/** 
    2530 * 
     
    106111 
    107112  return $result; 
     113} 
     114 
     115function pwg_db_nextval($column, $table) 
     116{ 
     117  $query = ' 
     118SELECT IF(MAX('.$column.')+1 IS NULL, 1, MAX('.$column.')+1) 
     119  FROM '.$table; 
     120  list($next) = pwg_db_fetch_row(pwg_query($query)); 
     121 
     122  return $next; 
    108123} 
    109124 
     
    444459} 
    445460 
     461function pwg_db_get_recent_period_expression($period, $date='CURRENT_DATE') 
     462{ 
     463  if ($date!='CURRENT_DATE') 
     464  { 
     465    $date = '\''.$date.'\''; 
     466  } 
     467 
     468  return 'SUBDATE('.$date.',INTERVAL '.$period.' DAY)'; 
     469} 
     470 
     471function pwg_db_get_recent_period($period, $date='CURRENT_DATE') 
     472{ 
     473  $query = ' 
     474SELECT '.pwg_db_get_recent_period_expression($period); 
     475  list($d) = pwg_db_fetch_row(pwg_query($query)); 
     476 
     477  return $d; 
     478} 
     479 
     480/** 
     481 * returns an array containing the possible values of an enum field 
     482 * 
     483 * @param string tablename 
     484 * @param string fieldname 
     485 */ 
     486function get_enums($table, $field) 
     487{ 
     488  // retrieving the properties of the table. Each line represents a field : 
     489  // columns are 'Field', 'Type' 
     490  $result = pwg_query('desc '.$table); 
     491  while ($row = pwg_db_fetch_assoc($result)) 
     492  { 
     493    // we are only interested in the the field given in parameter for the 
     494    // function 
     495    if ($row['Field'] == $field) 
     496    { 
     497      // retrieving possible values of the enum field 
     498      // enum('blue','green','black') 
     499      $options = explode(',', substr($row['Type'], 5, -1)); 
     500      foreach ($options as $i => $option) 
     501      { 
     502        $options[$i] = str_replace("'", '',$option); 
     503      } 
     504    } 
     505  } 
     506  pwg_db_free_result($result); 
     507  return $options; 
     508} 
     509 
     510// get_boolean transforms a string to a boolean value. If the string is 
     511// "false" (case insensitive), then the boolean value false is returned. In 
     512// any other case, true is returned. 
     513function get_boolean( $string ) 
     514{ 
     515  $boolean = true; 
     516  if ( 'false' == strtolower($string) ) 
     517  { 
     518    $boolean = false; 
     519  } 
     520  return $boolean; 
     521} 
     522 
     523/** 
     524 * returns boolean string 'true' or 'false' if the given var is boolean 
     525 * 
     526 * @param mixed $var 
     527 * @return mixed 
     528 */ 
     529function boolean_to_string($var) 
     530{ 
     531  if (is_bool($var)) 
     532  { 
     533    return $var ? 'true' : 'false'; 
     534  } 
     535  else 
     536  { 
     537    return $var; 
     538  } 
     539} 
     540 
    446541// my_error returns (or send to standard output) the message concerning the 
    447542// error occured for the last mysql query. 
  • trunk/include/filter.inc.php

    r3282 r4367  
    9797    } 
    9898  $query.= ' 
    99     date_available >= SUBDATE( 
    100       CURRENT_DATE,INTERVAL '.$filter['recent_period'].' DAY)'; 
     99    date_available >= '.pwg_db_get_recent_period_expression($filter['recent_period']); 
    101100 
    102101    $filter['visible_images'] = implode(',', array_from_query($query, 'image_id')); 
  • trunk/include/functions.inc.php

    r4325 r4367  
    3333 
    3434//----------------------------------------------------------- generic functions 
    35  
    36 /** 
    37  * returns an array containing the possible values of an enum field 
    38  * 
    39  * @param string tablename 
    40  * @param string fieldname 
    41  */ 
    42 function get_enums($table, $field) 
    43 { 
    44   // retrieving the properties of the table. Each line represents a field : 
    45   // columns are 'Field', 'Type' 
    46   $result = pwg_query('desc '.$table); 
    47   while ($row = pwg_db_fetch_assoc($result)) 
    48   { 
    49     // we are only interested in the the field given in parameter for the 
    50     // function 
    51     if ($row['Field'] == $field) 
    52     { 
    53       // retrieving possible values of the enum field 
    54       // enum('blue','green','black') 
    55       $options = explode(',', substr($row['Type'], 5, -1)); 
    56       foreach ($options as $i => $option) 
    57       { 
    58         $options[$i] = str_replace("'", '',$option); 
    59       } 
    60     } 
    61   } 
    62   pwg_db_free_result($result); 
    63   return $options; 
    64 } 
    65  
    66 // get_boolean transforms a string to a boolean value. If the string is 
    67 // "false" (case insensitive), then the boolean value false is returned. In 
    68 // any other case, true is returned. 
    69 function get_boolean( $string ) 
    70 { 
    71   $boolean = true; 
    72   if ( 'false' == strtolower($string) ) 
    73   { 
    74     $boolean = false; 
    75   } 
    76   return $boolean; 
    77 } 
    78  
    79 /** 
    80  * returns boolean string 'true' or 'false' if the given var is boolean 
    81  * 
    82  * @param mixed $var 
    83  * @return mixed 
    84  */ 
    85 function boolean_to_string($var) 
    86 { 
    87   if (is_bool($var)) 
    88   { 
    89     return $var ? 'true' : 'false'; 
    90   } 
    91   else 
    92   { 
    93     return $var; 
    94   } 
    95 } 
    9635 
    9736// The function get_moment returns a float value coresponding to the number 
     
    541480  VALUES 
    542481  ( 
    543     CURDATE(), 
    544     CURTIME(), 
     482    CURRENT_DATE, 
     483    CURRENT_TIME, 
    545484    '.$user['id'].', 
    546485    \''.$_SERVER['REMOTE_ADDR'].'\', 
     
    14981437  { 
    14991438    // Use MySql date in order to standardize all recent "actions/queries" 
    1500     list($cache['get_icon']['sql_recent_date']) = 
    1501       pwg_db_fetch_row(pwg_query('select SUBDATE( 
    1502       CURRENT_DATE,INTERVAL '.$user['recent_period'].' DAY)')); 
     1439    $cache['get_icon']['sql_recent_date'] = pwg_db_get_recent_period($user['recent_period']); 
    15031440  } 
    15041441 
  • trunk/include/functions_category.inc.php

    r4325 r4367  
    378378    OR '; 
    379379    } 
    380     $query.= 'uppercats REGEXP \'(^|,)'.$category_id.'(,|$)\''; 
     380    $query.= 'uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$category_id.'(,|$)\''; 
    381381  } 
    382382  $query.= ' 
  • trunk/include/functions_notification.inc.php

    r4334 r4367  
    464464    AND date_available="'.$dates[$i]['date_available'].'" 
    465465    AND tn_ext IS NOT NULL 
    466   ORDER BY RAND(NOW()) 
     466  ORDER BY '.DB_RANDOM_FUNCTION.'()) 
    467467  LIMIT '.$max_elements.' 
    468468;'; 
  • trunk/include/functions_plugins.inc.php

    r4325 r4367  
    225225  if (!empty($state)) 
    226226  { 
    227     $clauses[] = 'state="'.$state.'"'; 
     227    $clauses[] = 'state=\''.$state.'\''; 
    228228  } 
    229229  if (!empty($id)) 
  • trunk/include/functions_user.inc.php

    r4325 r4367  
    386386  ('.$userdata['id'].',\''.boolean_to_string($userdata['need_update']).'\',' 
    387387  .$userdata['cache_update_time'].',\'' 
    388   .$userdata['forbidden_categories'].'\','.$userdata['nb_total_images'].',"' 
    389   .$userdata['image_access_type'].'","'.$userdata['image_access_list'].'")'; 
     388  .$userdata['forbidden_categories'].'\','.$userdata['nb_total_images'].',\'' 
     389  .$userdata['image_access_type'].'\',\''.$userdata['image_access_list'].'\')'; 
    390390      pwg_query($query); 
    391391    } 
     
    633633  if ( isset($filter_days) ) 
    634634  { 
    635     $query .= ' AND i.date_available > SUBDATE(CURRENT_DATE,INTERVAL '.$filter_days.' DAY)'; 
     635    $query .= ' AND i.date_available > '.pwg_db_get_recent_period_expression($filter_days); 
    636636  } 
    637637 
     
    10401040       '.$conf['user_fields']['password'].' AS password 
    10411041  FROM '.USERS_TABLE.' 
    1042   WHERE '.$conf['user_fields']['username'].' = \''.mysql_real_escape_string($username).'\' 
     1042  WHERE '.$conf['user_fields']['username'].' = \''.pwg_db_real_escape_string($username).'\' 
    10431043;'; 
    10441044  $row = pwg_db_fetch_assoc(pwg_query($query)); 
  • trunk/include/section_init.inc.php

    r4334 r4367  
    450450    INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id 
    451451  WHERE 
    452     date_available >= SUBDATE( 
    453       CURRENT_DATE,INTERVAL '.$user['recent_period'].' DAY) 
     452    date_available >= '.pwg_db_get_recent_period_expression($user['recent_period']).' 
    454453    '.$forbidden.' 
    455454  '.$conf['order_by'].' 
  • trunk/include/ws_functions.inc.php

    r4348 r4367  
    122122        $matches[1][$i] = 'date_available'; break; 
    123123      case 'rand': case 'random': 
    124         $matches[1][$i] = 'RAND()'; break; 
     124        $matches[1][$i] = DB_RANDOM_FUNCTION.'()'; break; 
    125125    } 
    126126    $sortable_fields = array('id', 'file', 'name', 'hit', 'average_rate', 
    127       'date_creation', 'date_available', 'RAND()' ); 
     127      'date_creation', 'date_available', DB_RANDOM_FUNCTION.'()' ); 
    128128    if ( in_array($matches[1][$i], $sortable_fields) ) 
    129129    { 
    130130      if (!empty($ret)) 
    131131        $ret .= ', '; 
    132       if ($matches[1][$i] != 'RAND()' ) 
     132      if ($matches[1][$i] != DB_RANDOM_FUNCTION.'()' ) 
    133133      { 
    134134        $ret .= $tbl_name; 
     
    231231    if ($params['recursive']) 
    232232    { 
    233       $where_clauses[] = 'uppercats REGEXP \'(^|,)'.$cat_id.'(,|$)\''; 
     233      $where_clauses[] = 'uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$cat_id.'(,|$)\''; 
    234234    } 
    235235    else 
     
    378378  else if ($params['cat_id']>0) 
    379379  { 
    380     $where[] = 'uppercats REGEXP \'(^|,)'. 
     380    $where[] = 'uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'. 
    381381      (int)($params['cat_id']) 
    382382      .'(,|$)\''; 
  • trunk/random.php

    r4334 r4367  
    5252    'WHERE' 
    5353  ).' 
    54   ORDER BY RAND(NOW()) 
     54  ORDER BY '.DB_RANDOM_FUNCTION.'() 
    5555  LIMIT '.min(50, $conf['top_number'],$user['nb_image_page']).' 
    5656;'; 
Note: See TracChangeset for help on using the changeset viewer.