Changeset 4367


Ignore:
Timestamp:
Nov 25, 2009, 8:02:57 PM (11 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.