SQL for PhP Yii

Question

I am new to php yii. I tried to get and print out the insurance from the location.User can insert the date and location to show the result. I use this SQL to get the database from 2 tables. One for the basic information (t1) and the other is the insurance information(t2). However, the SQL does not work. The SQL comment is as follows:

public static function getInsuranceRecordsByLocation($datefrom, $dateto, $location, $table1, $table2)   //for rp4
    {   

        $rs = Yii::app()->db->createCommand()
            ->select("t1.ID, t2.INSURED_PERIOD, t1.ENGLISH_TITLE, t1.CURRENT_LOCATION, t2.VALUE_EST")
            ->from($table1 t1, $table2 t2)
            ->where('t2.INSURED_PERIOD>=:dfrom AND  t2.INSURED_PERIOD<=:dto' ,  array(
               ':dfrom'=>CommonFunc::datebinding($datefrom),
               ':dto'=>CommonFunc::datebinding($dateto)
             )
         )
           ->order('t1.ID,t2.INSURED_PERIOD')
           ->queryAll();
return $rs; 
}

The other function is shown as follows: After user inserting the information, the value would pass to insurance function and these value pass to above function to show the insurance result:

   public function insurance($v_dfrom,$v_dto, $location) 
{   
        $v_date = date('Y-m-d ');
        $v_daterage = ($v_dfrom . ' to ' . $v_dto);         
        $TotalSum = 0;

        $rsInsurance = InsuranceLibrary::getInsuranceRecordsByLocation($v_dfrom, $v_dto, $location, Archives::tableName(), Archives_Insurance::tableName());    


    //Then these varable will be printed on the web.

       }

Can someone help on how to modify the SQL. Thanks a lot

Answer 1

you had syntax problems, I don't know about the variables, but this should work:

$rs = Yii::app()->db->createCommand()
    ->select("t1.ID, t2.INSURED_PERIOD, t1.ENGLISH_TITLE, t1.CURRENT_LOCATION, t2.VALUE_EST")
    ->from("$table1 t1")
    ->join("$table2 t2" , 't1.field = t2.field2')
    ->where('t2.INSURED_PERIOD>=:dfrom AND  t2.INSURED_PERIOD<=:dto' ,  array(
        ':dfrom'=>CommonFunc::datebinding($datefrom),
        ':dto'=>CommonFunc::datebinding($dateto)
        )
    )
    ->order('t1.ID,t2.INSURED_PERIOD')
    ->queryAll();

return $rs;