PHP db_in_clause Examples

Example #1

File: PFAHandler.php Project: mpietruschka/postfixadmin

 /**
  * build_select_query
  *
  * helper function to build the inner part of the select query
  * can be used by read_from_db() and for generating the pagebrowser
  *
  * @param array or string - condition (an array will be AND'ed using db_where_clause, a string will be directly used)
  *                          (if you use a string, make sure it is correctly escaped!)
  *                        - WARNING: will be changed to array only in the future, with an option to include a raw string inside the array
  * @param array searchmode - operators to use (=, <, >) if $condition is an array. Defaults to = if not specified for a field.
  * @return array - contains query parts
  */
 protected function build_select_query($condition, $searchmode)
 {
     $select_cols = array();
     $yes = escape_string(Config::lang('YES'));
     $no = escape_string(Config::lang('NO'));
     if (db_pgsql()) {
         $formatted_date = "TO_DATE(text(###KEY###), '" . escape_string(Config::Lang('dateformat_pgsql')) . "')";
         $base64_decode = "DECODE(###KEY###, 'base64')";
     } elseif (db_sqlite()) {
         $formatted_date = "strftime(###KEY###, '" . escape_string(Config::Lang('dateformat_mysql')) . "')";
         $base64_decode = "base64_decode(###KEY###)";
     } else {
         $formatted_date = "DATE_FORMAT(###KEY###, '" . escape_string(Config::Lang('dateformat_mysql')) . "')";
         $base64_decode = "FROM_BASE64(###KEY###)";
     }
     $colformat = array('ts' => "{$formatted_date} AS ###KEY###, ###KEY### AS _###KEY###", 'bool' => "CASE ###KEY### WHEN '" . db_get_boolean(true) . "' THEN '1'    WHEN '" . db_get_boolean(false) . "' THEN '0'   END as ###KEY###," . "CASE ###KEY### WHEN '" . db_get_boolean(true) . "' THEN '{$yes}' WHEN '" . db_get_boolean(false) . "' THEN '{$no}' END as _###KEY###", 'b64p' => "{$base64_decode} AS ###KEY###");
     # get list of fields to display
     $extrafrom = "";
     foreach ($this->struct as $key => $row) {
         if (($row['display_in_list'] != 0 || $row['display_in_form'] != 0) && $row['not_in_db'] == 0) {
             if ($row['select'] != '') {
                 $key = $row['select'];
             }
             if ($row['extrafrom'] != '') {
                 $extrafrom = $extrafrom . " " . $row['extrafrom'] . "\n";
             }
             if (isset($colformat[$row['type']])) {
                 $select_cols[] = str_replace('###KEY###', $key, $colformat[$row['type']]);
             } else {
                 $select_cols[] = $key;
             }
         }
     }
     $cols = join(',', $select_cols);
     $table = table_by_key($this->db_table);
     $additional_where = '';
     if ($this->domain_field != "") {
         $additional_where .= " AND " . db_in_clause($this->domain_field, $this->allowed_domains);
     }
     # if logged in as user, restrict to the items the user is allowed to see
     if (!$this->is_admin && $this->user_field != '') {
         $additional_where .= " AND " . $this->user_field . " = '" . escape_string($this->username) . "' ";
     }
     if (is_array($condition)) {
         if (isset($condition['_']) && count($this->searchfields) > 0) {
             $simple_search = array();
             foreach ($this->searchfields as $field) {
                 $simple_search[] = "{$field} LIKE '%" . escape_string($condition['_']) . "%'";
             }
             $additional_where .= " AND ( " . join(" OR ", $simple_search) . " ) ";
             unset($condition['_']);
         }
         $where = db_where_clause($condition, $this->struct, $additional_where, $searchmode);
     } else {
         if ($condition == "") {
             $condition = '1=1';
         }
         $where = " WHERE ( {$condition} ) {$additional_where}";
     }
     return array('select_cols' => " SELECT {$cols} ", 'from_where_order' => " FROM {$table} {$extrafrom} {$where} ORDER BY " . $this->order_by);
 }

Example #2

File: list-virtual.php Project: stepkh/postfixadmin

#
# mailboxes
#
$display_mailbox_aliases = Config::bool('alias_control_admin');
# build the sql query
$sql_select = "SELECT {$table_mailbox}.* ";
$sql_from = " FROM {$table_mailbox} ";
$sql_join = "";
$sql_where = " WHERE ";
$sql_order = " ORDER BY {$table_mailbox}.username ";
$sql_limit = " LIMIT {$page_size} OFFSET {$fDisplay}";
if (count($search) == 0 || !isset($search['_'])) {
    $sql_where .= " {$table_mailbox}.domain='{$fDomain}' ";
} else {
    $searchterm = escape_string($search['_']);
    $sql_where .= db_in_clause("{$table_mailbox}.domain", $list_domains) . " ";
    $sql_where .= " AND ( {$table_mailbox}.username LIKE '%{$searchterm}%' OR {$table_mailbox}.name LIKE '%{$searchterm}%' ";
    if ($display_mailbox_aliases) {
        $sql_where .= " OR {$table_alias}.goto LIKE '%{$searchterm}%' ";
    }
    $sql_where .= " ) ";
    # $search is already escaped
}
if ($display_mailbox_aliases) {
    $sql_select .= ", {$table_alias}.goto ";
    $sql_join .= " LEFT JOIN {$table_alias} ON {$table_mailbox}.username={$table_alias}.address ";
}
if (Config::bool('vacation_control_admin')) {
    $table_vacation = table_by_key('vacation');
    $sql_select .= ", {$table_vacation}.active AS v_active ";
    $sql_join .= " LEFT JOIN {$table_vacation} ON {$table_mailbox}.username={$table_vacation}.email ";