Cdbcriteria With Addbetweencondition

Yii 1.1 General Discussion
1

In my database I store a value. Then in the search I have two fields and I need to find all the records where that value is between the two fields.

The code for the form is


 <div class="row">

                <div class="checkBoxMed">

                    <?php echo $form->checkbox($model,'percentRecycledContent', array('id'=>'checkBoxMed3', 'name' => 'percentRecycledContent_check')); ?>

                    <label for="checkBoxMed3"></label>

                </div>

                <?php echo $form->labelEx($model,'percentRecycledContent', array('label'=>'Recycled content between', 'style'=>"width:165px;")); ?>

                <div class="percentWrapper">

                    <?php echo $form->textField($model, 'percentRecycledContent[start]', array('class'=>'percentInput')); ?>

                    <label for="and">and</label>


                    <?php echo $form->textField($model, 'percentRecycledContent[end]', array('class'=>'percentInput')); ?>

                    <span>%</span>

                    

                </div>

            </div>

The code search in the model is


 public function search() {

		$criteria = new CDbCriteria;

                $criteria->with = array( 'company', 'category' );

                

		$criteria->compare('company.name', $this->company_name, true);

		$criteria->compare('t.Name', $this->Name, true);

		$criteria->compare('category.name', $this->category_name, true);

		$criteria->addBetweenCondition('percentRecycledContent', $this->percent_start, $this->percent_finish, true);

		$criteria->compare('Description', $this->Name, true);

		$criteria->compare('otherInformation', $this->Name, true);

		$criteria->compare('Materials', $this->Name, true);


		return new CActiveDataProvider($this, array(

			'criteria' => $criteria,

                        'sort'=>array(

                            'attributes'=>array(

                                'company_name'=>array(

                                    'asc'=>'company.name',

                                    'desc'=>'company.name DESC',

                                ),

                                'category_name'=>array(

                                    'asc'=>'category.name',

                                    'desc'=>'category.name DESC',

                                ),

                                'Name',

                                'percentRecycledContent',

                            ),

                        ),

                        'pagination'=>array(

                                'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),

                        ),

		));

	}

I also have this in the model


        public $company_name;

        public $category_name;

        public $percent_start;

        public $percent_finish;

I get an SQL error, the focus is - percentRecycledContent BETWEEN NULL AND NULL

How can I get this working?

Thank you

2

Could you post the message error ?

3

No error message for that search but there is also a name search which does return the SQL error below.


SELECT COUNT(DISTINCT `t`.`id`) FROM `product` `t` LEFT OUTER JOIN `company` `company` ON (`t`.`companyId`=`company`.`id`) LEFT OUTER JOIN `productcategory` `category` ON (`category`.`id`=`t`.`categoryId`) WHERE (((((t.Name LIKE :ycp0) 1 (percentRecycledContent BETWEEN :ycp1 AND :ycp2)) AND (Description LIKE :ycp3)) AND (otherInformation LIKE :ycp4)) AND (Materials LIKE :ycp5)). Bound with :ycp0='%jason%', :ycp1=NULL, :ycp2=NULL, :ycp3='%jason%', :ycp4='%jason%', :ycp5='%jason%'

But if you look at the statement: "(percentRecycledContent BETWEEN :ycp1 AND :ycp2)" with ":ycp1=NULL, :ycp2=NULL," these should be 10 and 90?

To solve the error I need to take out the 1 after WHERE "(((((t.Name LIKE :ycp0)". Any ideas how? as I cannot see how it gets there

4

according to

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#addBetweenCondition-detail

the method addBetweenCondition accept


(string $column, string $valueStart, string $valueEnd, string $operator='AND')

so, check this one


$criteria->addBetweenCondition('percentRecycledContent', $this->percent_start, $this->percent_finish, 'AND');