How to search in multiple columns on yii using $criteria->compare() or $criteria->addCondition()

Question

How to search in multiple columns on yii using $criteria->compare() or $criteria->addCondition()

I have a table named product and inside this table I have multiple columns for example: "productId, name, description..." I need my user to be able to search in multiple columns like $criteria->compare('name' AND 'id' And '','' ,$search,true); but this never works! only one column works like: $criteria->addCondition("name LIKE '%$search%'"); or $criteria->compare('productId',$search,true);

My search form:

<div class="search-bar col-md-9 col-lg-9 no-hor-padding">
	<form role="form" onSubmit="return dosearch();" class="navbar-form- navbar-left- search-form" style="padding-left: 0;"
		action="<?php echo Yii::app()->createAbsoluteUrl('/'); ?>" method="get">
		<input type="text" maxlength="30" placeholder="<?php echo Yii::t('app','Search products'); ?>" class="search-icon form-control input-search <?php echo !empty(Yii::app()->user->id) ? "" : "sign" ?>" name="search"></input>
	</form>		  
</div>

And my function or controller:

if(!empty($search)) {
    //$criteria->addCondition("name LIKE '%$search%'");
    $criteria->compare('name',$search,true);

    $searchCriteria = clone $criteria;
    $searchproducts = Products::model()->find($searchCriteria);
    if(empty($searchproducts)){
        $catrest = 1;
        $location = 1;
    }
}

Products::model() refer to product table Here is the search function form model class for table "hts_products".:

public function search()
	{
		// @todo Please modify the following code to remove attributes that should not be searched.

		$criteria=new CDbCriteria;

		$criteria->compare('productId',$this->productId);
		$criteria->compare('userId',$this->userId);
		$criteria->compare('name',$this->name,true);
		$criteria->compare('price',$this->price);
		$criteria->compare('quantity',$this->quantity);
		$criteria->compare('sizeOptions',$this->sizeOptions,true);
		$criteria->compare('productCondition',$this->productCondition,true);
		$criteria->compare("from_unixtime(`createdDate`, '%d-%m-%Y')",$this->createdDate,true);
		//if(!empty($this->createdDate))
		//$criteria->condition = "from_unixtime(`createdDate`, '%d-%m-%Y') = '$this->createdDate'";
		//else
		//$criteria->compare('createdDate',$this->createdDate);
		/* $criteria->compare('likeCount',$this->likeCount);
		$criteria->compare('commentCount',$this->commentCount); */
		$criteria->compare('chatAndBuy',$this->chatAndBuy);
		$criteria->compare('exchangeToBuy',$this->exchangeToBuy);
		$criteria->compare('instantBuy',$this->instantBuy);
		$criteria->compare('paypalid',$this->paypalid,true);
		// $criteria->order = 'productId DESC';
		return new CActiveDataProvider($this, array(
		'criteria'=>$criteria,
		'pagination'=>array(
              'pageSize'=>10,
		),
		'sort'=>array(
               'defaultOrder'=>'productId DESC',
		)

		));
	}

Thanks.

Answer 1

For search in multiple columns, with variables and user inputs, compare() could be the best option. Taken from official Yii 1.1.x docs:

compare() method appends to the condition property with a new comparison expression. The comparison is done by comparing a column with the given value using some comparison operator. Note that any surrounding white spaces will be removed from the value before comparison. When the value is empty, no comparison expression will be added to the search condition.

Example. In a model Invoices, before submit, with empty values in your search form:

public function search() {
        $criteria = new CDbCriteria;
        $criteria->compare('invoice_number', $this->invoice_number);
        $criteria->compare('city_id', $this->city_id);
        $criteria->compare('state', $this->state);

        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
        ));
}

It will output something similar to:

SELECT * FROM `invoices` 

After your submit, with all the values filled in your search form:

SELECT * FROM `invoices` 
WHERE ( invoice_number = :ycp1 AND city_id = :ycp2 AND state = :ycp3)

Only with invoice_number filled and submitted from your form. city_id and state are empty:

SELECT * FROM `invoices` 
WHERE ( invoice_number = :ycp1 )

You can look all compare() method documentation to custom it as you want.

I suggest you to use compare() to match exact values like numbers.

For search in multiple columns with LIKE or not exact/approximated values, I suggest you to use addSearchCondition().

Example:

public function search() {
        $criteria = new CDbCriteria;
        $criteria->compare('invoice_number', $this->invoice_number);
        $criteria->compare('city_id', $this->city_id);
        $criteria->compare('state', $this->state);
        $criteria->addSearchCondition('customer_name', $this->customer_name);

        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
        ));
}

It will output something similar to:

SELECT * FROM `invoices` 
WHERE ( invoice_number = :ycp1 AND city_id = :ycp2 AND state = :ycp3 AND customer_name LIKE :ycp4 )

If you need more code examples with compare() and other methods from CDbCriteria you can see this nice article.