CODEWITHSUNDEEP
yii
FahadAkram
FahadAkram

Reputation: 465

yii how to select date range in queryall

Here is my query 

$query= Yii::app()->db->createCommand()
                    ->select('*,SUM(amount) AS TotalItemsOrdered')
                    ->from('shoppinglist')
                    ->where('user_type="Admin" ')
                    ->group('cat_id,ing_id,measure_id')
                    ->queryAll();

i have startdate from today and i add 6 days in current date to get last date ie complete week suppose $startdate = "7-3-14"; //month-day-year $enddate = "7-7-14"; how i use this code Date BETWEEN DATE('$DateFrom_order') AND DATE('$DateTo_order') or i use in statement of mysql but how to use or ember on above YII query EDIT HERE IS MY MODEL

<?php

class Shoppinglist extends CActiveRecord {

public static function model($className=__CLASS__)
{
    return parent::model($className);
}

public function tableName()
{
    return 'shoppinglist';
}

/**
 * @return array validation rules for model attributes.
 */
public function rules()
{
    // NOTE: you should only define rules for those attributes that
    // will receive user inputs.
    return array(
        array('ing_id, measure_id, amount, user_id, cat_id, date_added, user_type', 'required'),
        array('ing_id, measure_id, amount, user_id, cat_id', 'numerical', 'integerOnly'=>true),
        array('user_type', 'length', 'max'=>5),
        // The following rule is used by search().
        // Please remove those attributes that should not be searched.
        array('id, ing_id, measure_id, amount, user_id, cat_id, date_added, user_type', 'safe', 'on'=>'search'),
    );
}

/**
 * @return array relational rules.
 */
public function relations()
{
    // NOTE: you may need to adjust the relation name and the related
    // class name for the relations automatically generated below.
    return array(
    );
}

/**
 * @return array customized attribute labels (name=>label)
 */
public function attributeLabels()
{
    return array(
        'id' => 'ID',
        'ing_id' => 'Ing',
        'measure_id' => 'Measure',
        'amount' => 'Amount',
        'user_id' => 'User',
        'cat_id' => 'Cat',
        'date_added' => 'Date Added',
        'user_type' => 'User Type',
    );
}

/**
 * Retrieves a list of models based on the current search/filter conditions.
 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
 */
public function search()
{
    // Warning: Please modify the following code to remove attributes that
    // should not be searched.

    $criteria=new CDbCriteria;

    $criteria->compare('id',$this->id);
    $criteria->compare('ing_id',$this->ing_id);
    $criteria->compare('measure_id',$this->measure_id);
    $criteria->compare('amount',$this->amount);
    $criteria->compare('user_id',$this->user_id);
    $criteria->compare('cat_id',$this->cat_id);
    $criteria->compare('date_added',$this->date_added,true);
    $criteria->compare('user_type',$this->user_type,true);

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

}

Upvotes: 2

Views: 2169

Answers (1)

ricardomm85
ricardomm85

Reputation: 126

If the field Date in the database is DATE (type) then:

$query= Yii::app()->db->createCommand()
            ->select('*,SUM(amount) AS TotalItemsOrdered')
            ->from('shoppinglist')
            ->where("user_type='Admin' AND Date BETWEEN STR_TO_DATE( '$startdate', '%d-%m-%y' ) AND STR_TO_DATE( '$enddate', '%d-%m-%y' )")
            ->group('cat_id,ing_id,measure_id')
            ->queryAll();

Upvotes: 3

Related Questions