Help Createcommand For Searching From Date To Date

Yii 1.1 General Discussion
1

well, I’m trying to search data from date to date with datepicker. I have this code in my view (searching form):


<?php

$form = $this->beginWidget('CActiveForm', array(

    'id' => 'report-form',

    'enableAjaxValidation' => true,

    'action'=>Yii::app()->createUrl('penjualan/carireport')

        ));

?>


    <div class="row">

        <b>Periode :</b>

        <?php

        $this->widget('zii.widgets.jui.CJuiDatePicker', array(

            'name' => 'dari_tgl',

            'model' => $model,

            'attribute' => 'dari_tgl',

            'options' => array(

                'showAnim' => 'fold',

                'dateFormat' => 'dd-M-yy'

            ),

            'htmlOptions' => array(

                'readonly' => 'readonly',

            ),

        ));

        ?>


        s/d


        <?php

        $this->widget('zii.widgets.jui.CJuiDatePicker', array(

            'name' => 'sampai_tgl',

            'model' => $model,

            'attribute' => 'sampai_tgl',

            'options' => array(

                'showAnim' => 'fold',

                'dateFormat' => 'dd-M-yy'

            ),

            'htmlOptions' => array(

                'readonly' => 'readonly',

            ),

        ));

        ?>


        <?php echo CHtml::submitButton('Submit', array('id' => 'submit')); ?>

        </br>

        <?php $this->endWidget(); ?>

    </div>

and this is my controller:


public function actionCarireport() {


        $_POST['Penjualan']['dari_tgl'] = date('Y-m-d', strtotime($_POST['Penjualan']['dari_tgl']));

        $_POST['Penjualan']['sampai_tgl'] = date('Y-m-d', strtotime($_POST['Penjualan']['sampai_tgl']));


        $command = Yii::app()->db->createCommand('SELECT id_penjualan FROM penjualan WHERE tgl_penjualan >= :dari_tgl AND tgl_penjualan <= :sampai_tgl');

        $command->bindValues(array(':dari_tgl' => $_POST['Penjualan']['dari_tgl'], ':sampai_tgl' => $_POST['Penjualan']['sampai_tgl']));


        $report = Penjualan::model()->findByAttributes(array('id_penjualan' => $command->queryScalar()));

        echo $report->total;

    }

when I tried to run this searching application, it’s work but it’s only show 1 result which is the last inserted one, instead of showing the whole results that match with the date.

anyone can tell me what’s wrong with my codes and how to solve it? thanks in advance :)

2

I think you’re overcomplicating it. Try something like this:




public function actionCarireport()

{

    $dari_tgl = date('Y-m-d', strtotime($_POST['Penjualan']['dari_tgl']));

    $sampai_tgl = date('Y-m-d', strtotime($_POST['Penjualan']['sampai_tgl']));


    $criteria = new CDbCriteria;

    $criteria->addCondition('tgl_penjualan >= :dari_tgl');

    $criteria->addCondition('tgl_penjualan <= :sampai_tgl');

    $criteria->params = array(':dari_tgl'=>$dari_tgl, ':sampai_tgl'=>$sampai_tgl);


    $report = Penjualan::model()->find($criteria);


    echo $report->total;

}
3

Just noticed that you were looking to get multiple results. In which case, change the last part of my code to:




$reports = Penjualan::model()->findAll($criteria);
4

thanks :) well, I think it’s just the same with mine. Because it give me the same result. Anyway, I found the problem already, and I think this is the most stupid question I ever asked here ;D

the only thing I need is, change this :


$report = Penjualan::model()->find($criteria);


    echo $report->total;

into this one:


$reports = Penjualan::model()->findAll($criteria);


        foreach ($reports as $report) {

                            echo $report->total;

                        }

the first code give me only 1 result because it’s find not findAll. and my new code, it’s show me the whole results that match with the inserted date now. :D sorry for my stupidity

5

oops, while I typing for ur answer before, you already post another one. big thanks :)

6

The improvement is that you’re only running a single database query rather than two. Yours was initially running a query to get a list of ID’s that matched the conditions, then running a query to get the records with those ID’s.

Using the CDbCriteria object is also far more readable in this case, so should be much more maintainable.

7

thanks for the information :)

8

hi Keith,

I just updated this searching. and this is my new controller :


    public function actionReport() {

        $model = new ReportForm();


        if (isset($_POST['ReportForm'])) {

            $model->attributes = $_POST['ReportForm'];

            if ($model->validate()) {

                $model->dari_tgl = date('Y-m-d', strtotime($model->dari_tgl));

                $model->sampai_tgl = date('Y-m-d', strtotime($model->sampai_tgl));

                $criteria = new CDbCriteria();

                $criteria->condition='tgl_penjualan >= '.$model->dari_tgl.' AND tgl_penjualan <= '.$model->sampai_tgl.'';


                $penjualans = Penjualan::model()->findAll($criteria);

                foreach ($penjualans as $penjualan) {

                    echo 'ID Penjualan : ' . $penjualan->id_penjualan . '<br/>';

                    echo 'ID Customer : ' . $penjualan->id_customer . '<br/>';

                }

                $penjualan_details = PenjualanDetail::model()->findAllByAttributes(array('id_penjualan'=>$penjualans->id_penjualan));

                foreach ($penjualan_details as $penjualan_detail) {

                    echo 'Laba : ' . ($penjualan_detail->harga - $penjualan_detail->hpp) . '<br/><br/>';

                }


                Yii::app()->end();

            }

        }


        $this->render('report', array(

            'model' => $model,

        ));

    }

I’ve check it already that this code able to pick the data . but, it won’t give any result and only give me a blank white page instead. can you help me with this? thanks in advance :)

9

Start by using parameters instead of concatenating the condition. Look at my example above.