SQL查询结果为字符串

Question

I'm using SQL in Yii framework. I need to show the person's latest active week (it's number and date).So I wrote following code:

public function latestWeek() 
{           
    $datalogin=//the login is working fine
    $sql ="SELECT w.number,MAX(w.start_date)
    FROM tbl_person_week t, tbl_week w
    WHERE t.person_id=$this->id AND t.week_id=w.id"; 

    $query = mysqli_query($datalogin, $sql);
    return $query;
}

Now , I checked this query on the server and it works fine (almost) but first thing: I need to convert it into string , because yii's CgridView can't read it , and I couldn't find a working solution for this.

Second: on the server , it gave me the max date indeed , but not it's correct number , but the first number available. How can I fix this as well?

Answer 1

Queries like that should never be used in objective framework. If yu want to execute your own query, you should do it this way:

$sql = "your sql code";
$array = Yii::app()->db->createCommand($sql)->queryAll();

As result you will get multidimensional array with selected columns and rows

If you want to use it in grid view, you should do it this way:

$count = Yii::app()->db->createCommand($sql)->queryScalar();

$dataProvider = new CSqlDataProvider($sql, array('totalItemCount'=>$count));

$this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'grid-id',
     'dataProvider'=> $dataProvider,
));

You can also use connection other than Yii::app()->db. Check CDbConnection class in docs.

edit: if you wanna use queries like mysql_fetch_assoc, check out also queryRow() method instead of queryAll()