In the code below there should be combined multiple WHERE’s with the AND oparator.
But while testing appears only one Where.
Is it possible to add really much wheres in WHERE’s with CDbCommand or is it knocked out by such easy things???
If so, I will use the commandBuilder, create my sql old style and use only tha cool bindParam tool.
I simply want to do like:
WHERE col1 LIKE '%...%'
AND col2 LIKE '%...%'
AND col3 LIKE '%...%'
AND col4 LIKE '%...%'
AND col5 LIKE '%...%'
AND col6 LIKE '%...%'
....with possibilty for open end...
Is’nt that possible??
// WHERE id=1 or id=2
where('id=1 or id=2')
// WHERE id=:id1 or id=:id2
where('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2))
// WHERE id=1 OR id=2
where(array('or', 'id=1', 'id=2'))
// WHERE id=1 AND (type=2 OR type=3)
where(array('and', 'id=1', array('or', 'type=2', 'type=3')))
// WHERE `id` IN (1, 2)
where(array('in', 'id', array(1, 2))
// WHERE `id` NOT IN (1, 2)
where(array('not in', 'id', array(1,2)))
// WHERE `name` LIKE '%Qiang%'
where(array('like', 'name', '%Qiang%'))
// WHERE `name` LIKE '%Qiang' AND `name` LIKE '%Xue'
where(array('like', 'name', array('%Qiang', '%Xue')))
// WHERE `name` LIKE '%Qiang' OR `name` LIKE '%Xue'
where(array('or like', 'name', array('%Qiang', '%Xue')))
// WHERE `name` NOT LIKE '%Qiang%'
where(array('not like', 'name', '%Qiang%'))
// WHERE `name` NOT LIKE '%Qiang%' OR `name` NOT LIKE '%Xue%'
where(array('or not like', 'name', array('%Qiang%', '%Xue%')))
public function actionList(){
$user = new OrsUser();
$searchPattern = $user->getUsernamePattern();
$searchDefaults = array(
'username' => Yii::t('global', 'phrase.username'),
'forename' => Yii::t('global', 'phrase.forename'),
'surname' => Yii::t('global', 'phrase.surname'),
'email' => Yii::t('global', 'phrase.email'),
'phone' => Yii::t('global', 'phrase.phone'),
'male' => Yii::t('global', 'phrase.male'),
'company' => Yii::t('global', 'phrase.company'),
'age' => Yii::t('global', 'phrase.age'),
'language' => Yii::t('global', 'phrase.language'),
);
// collect and prepare search fields
$params = array();
foreach($searchDefaults as $key => $value){
$get = getGet($key, $value);
if($get != $value && $get != '' && preg_match('/[\w|\d]+/', $get)){
//$params["$key like :$key"] = array(":$key" => '%'.$value.'%');
array_push($params, array('like', $key, '%'.$get.'%'));
$searchDefaults[$key] = $get;
}else{
$searchDefaults[$key] = $value;
}
}
// create and execute db-command
$cmd = Yii::app()->db->createCommand()
->select('count(*)')
->from($user->tableName());
foreach($params as $value)
$cmd->where($value);
// select count and create pagination
$pagination = new Paginate();
$pagination->calculate($cmd->queryScalar(), '//user/list', $_GET);
// do the real select with limit
$cmd = Yii::app()->db->createCommand()
->select('*')
->from($user->tableName());
foreach($params as $value)
$cmd->where($value);
$cmd->limit($pagination->limit, $pagination->offset);
$t = $cmd->getText();
$userList = $cmd->queryAll();
// prepare view data
$viewParams = array(
'user' => $user,
'list' => $userList,
'pagination' => $pagination->html,
'columns' => $searchDefaults,
);
$this->render('list', $viewParams);
}