php - How to get records having null as value in specific columns? -
i using flourishlib in of projects.
let's suppose have table called categories(id, name, parent_id), parent_id foreign key categories table.
i using frecordset::build() select record sets. select records not have parent. in mysql be
select * categories parent_id null however, using frecordset::build. have not seen possibilities in documentation, nor examples, also, did not see possibility looking @ method. there possibility run frecordset::build filtering in query shown above?
for reference, method use looks this:
/** * creates frecordset specifying class create plus conditions , order rules * * conditions array can contain `key => value` entries in of * following formats: * * {{{ * 'column=' => value, // column = value * 'column!' => value // column <> value * 'column!=' => value // column <> value * 'column<>' => value // column <> value * 'column~' => value // column '%value%' * 'column^~' => value // column 'value%' * 'column$~' => value // column '%value' * 'column!~' => value // column not '%value%' * 'column<' => value // column < value * 'column<=' => value // column <= value * 'column>' => value // column > value * 'column>=' => value // column >= value * 'column=:' => 'other_column' // column = other_column * 'column!:' => 'other_column' // column <> other_column * 'column!=:' => 'other_column' // column <> other_column * 'column<>:' => 'other_column' // column <> other_column * 'column<:' => 'other_column' // column < other_column * 'column<=:' => 'other_column' // column <= other_column * 'column>:' => 'other_column' // column > other_column * 'column>=:' => 'other_column' // column >= other_column * 'column=' => array(value, value2, ... ) // column in (value, value2, ... ) * 'column!' => array(value, value2, ... ) // column not in (value, value2, ... ) * 'column!=' => array(value, value2, ... ) // column not in (value, value2, ... ) * 'column<>' => array(value, value2, ... ) // column not in (value, value2, ... ) * 'column~' => array(value, value2, ... ) // (column '%value%' or column '%value2%' or column ... ) * 'column^~' => array(value, value2, ... ) // (column 'value%' or column 'value2%' or column ... ) * 'column$~' => array(value, value2, ... ) // (column '%value' or column '%value2' or column ... ) * 'column&~' => array(value, value2, ... ) // (column '%value%' , column '%value2%' , column ... ) * 'column!~' => array(value, value2, ... ) // (column not '%value%' , column not '%value2%' , column ... ) * 'column!|column2<|column3=' => array(value, value2, value3) // (column <> '%value%' or column2 < '%value2%' or column3 = '%value3%') * 'column|column2><' => array(value, value2) // when value === null: ((column2 null , column = value) or (column2 not null , column <= value , column2 >= value)) * // when value !== null: ((column <= value , column2 >= value) or (column >= value , column <= value2)) * 'column|column2|column3~' => value // (column '%value%' or column2 '%value%' or column3 '%value%') * 'column|column2|column3~' => array(value, value2, ... ) // ((column '%value%' or column2 '%value%' or column3 '%value%') , (column '%value2%' or column2 '%value2%' or column3 '%value2%') , ... ) * }}} * * when creating condition in form `column|column2|column3~`, if * value condition single string contains spaces, * string parsed search terms. search term parsing * handle quoted phrases , normal words , strip punctuation , * stop words (such "the" , "a"). * * order bys array can contain `key => value` entries in of * following formats: * * {{{ * 'column' => 'asc' // 'first_name' => 'asc' * 'column' => 'desc' // 'last_name' => 'desc' * 'expression' => 'asc' // "case first_name when 'smith' 1 else 2 end" => 'asc' * 'expression' => 'desc' // "case first_name when 'smith' 1 else 2 end" => 'desc' * }}} * * column in both conditions , order bys can in of * formats: * * {{{ * 'column' // e.g. 'first_name' * 'current_table.column' // e.g. 'users.first_name' * 'related_table.column' // e.g. 'user_groups.name' * 'related_table{route}.column' // e.g. 'user_groups{user_group_id}.name' * 'related_table=>once_removed_related_table.column' // e.g. 'user_groups=>permissions.level' * 'related_table{route}=>once_removed_related_table.column' // e.g. 'user_groups{user_group_id}=>permissions.level' * 'related_table=>once_removed_related_table{route}.column' // e.g. 'user_groups=>permissions{read}.level' * 'related_table{route}=>once_removed_related_table{route}.column' // e.g. 'user_groups{user_group_id}=>permissions{read}.level' * 'column||other_column' // e.g. 'first_name||last_name' - concatenates column values * }}} * * in addition using plain column names conditions, * possible pass aggregate function wrapped around column in place * of column name, comparison types. //note * column comparisons, function may placed on either column or both.// * * {{{ * 'function(column)=' => value, // function(column) = value * 'function(column)!' => value // function(column) <> value * 'function(column)!= => value // function(column) <> value * 'function(column)<>' => value // function(column) <> value * 'function(column)~' => value // function(column) '%value%' * 'function(column)^~' => value // function(column) 'value%' * 'function(column)$~' => value // function(column) '%value' * 'function(column)!~' => value // function(column) not '%value%' * 'function(column)<' => value // function(column) < value * 'function(column)<=' => value // function(column) <= value * 'function(column)>' => value // function(column) > value * 'function(column)>=' => value // function(column) >= value * 'function(column)=:' => 'other_column' // function(column) = other_column * 'function(column)!:' => 'other_column' // function(column) <> other_column * 'function(column)!=:' => 'other_column' // function(column) <> other_column * 'function(column)<>:' => 'other_column' // function(column) <> other_column * 'function(column)<:' => 'other_column' // function(column) < other_column * 'function(column)<=:' => 'other_column' // function(column) <= other_column * 'function(column)>:' => 'other_column' // function(column) > other_column * 'function(column)>=:' => 'other_column' // function(column) >= other_column * 'function(column)=' => array(value, value2, ... ) // function(column) in (value, value2, ... ) * 'function(column)!' => array(value, value2, ... ) // function(column) not in (value, value2, ... ) * 'function(column)!=' => array(value, value2, ... ) // function(column) not in (value, value2, ... ) * 'function(column)<>' => array(value, value2, ... ) // function(column) not in (value, value2, ... ) * }}} * * aggregate functions `avg()`, `count()`, `max()`, `min()` , * `sum()` supported across database types. * * below example of using conditions , order bys. please note * values should **not** escaped database, should * normal php values. * * {{{ * #!php * return frecordset::build( * 'user', * array( * 'first_name=' => 'john', * 'status!' => 'inactive', * 'groups.group_id=' => 2 * ), * array( * 'last_name' => 'asc', * 'date_joined' => 'desc' * ) * ); * }}} * * @param string $class class create frecordset of * @param array $where_conditions `column => value` comparisons `where` clause * @param array $order_bys `column => direction` values use `order by` clause * @param integer $limit number of records fetch * @param integer $page page offset use when limiting records * @return frecordset set of factiverecord objects */ static public function build($class, $where_conditions=array(), $order_bys=array(), $limit=null, $page=null) { factiverecord::validateclass($class); factiverecord::forceconfigure($class); $db = formdatabase::retrieve($class, 'read'); $schema = formschema::retrieve($class); $table = form::tablize($class); $params = array($db->escape("select %r.* :from_clause", $table)); if ($where_conditions) { $having_conditions = formdatabase::splithavingconditions($where_conditions); } else { $having_conditions = null; } if ($where_conditions) { $params[0] .= ' '; $params = formdatabase::addwhereclause($db, $schema, $params, $table, $where_conditions); } $params[0] .= ' :group_by_clause '; if ($having_conditions) { $params[0] .= ' having '; $params = formdatabase::addhavingclause($db, $schema, $params, $table, $having_conditions); } // if no ordering specified, order primary key if (!$order_bys) { $order_bys = array(); foreach ($schema->getkeys($table, 'primary') $pk_column) { $order_bys[$table . '.' . $pk_column] = 'asc'; } } $params[0] .= ' order '; $params = formdatabase::addorderbyclause($db, $schema, $params, $table, $order_bys); $params = formdatabase::injectfromandgroupbyclauses($db, $schema, $params, $table); // add limit clause , create query non-limited total $non_limited_count_sql = null; if ($limit !== null) { $pk_columns = array(); foreach ($schema->getkeys($table, 'primary') $pk_column) { $pk_columns[] = $table . '.' . $pk_column; } $non_limited_count_sql = str_replace( $db->escape('select %r.*', $table), $db->escape('select %r', $pk_columns), $params[0] ); $non_limited_count_sql = preg_replace('#\s+order by.*$#', '', $non_limited_count_sql); $non_limited_count_sql = $db->escape('select count(*) (' . $non_limited_count_sql . ') subquery', array_slice($params, 1)); $params[0] .= ' limit ' . $limit; if ($page !== null) { if (!is_numeric($page) || $page < 1) { $page = 1; } $params[0] .= ' offset ' . (($page-1) * $limit); } } else { $page = 1; } return new frecordset($class, call_user_func_array($db->translatedquery, $params), $non_limited_count_sql, $limit, $page); } thank much.
ok, long story short:
select null = null results in
null so null not null.
frecordset::build("category", array("parent_id=" => null)) works, is, yields records not have parent_id.
of course, have been trying this, however, have tried in wrong way.
i have method generates frecordset::build calls not implement same thing class usages on pages. using ajax post parameters server. clause post this:
{"parent_id=": null} and $_post["parent_id"] not null, empty string. wow.
so, solution tell server-side set value null if empty string, works in case, parent_id number, wonder how solve problem if same controls should used on string values filtering empty string valid value. had different problem stated in question, question might useful strange people myself think can post null values client-side server-side.
Comments
Post a Comment