CODEWITHSUNDEEP
phpsqlyii
mRiddle
mRiddle

Reputation: 214

SQL query result to string

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?

Upvotes: 0

Views: 701

Answers (3)

Arth
Arth

Reputation: 13110

Assuming from your qu. that you want the week number and start date as one string, you have to concatenate the two columns in the sql.

You also need to specify that the week number is from the row with the maximum start date, which isn't as simple as you might first think.

I don't like injecting the person_id straight into SQL, it isn't awful in this case but is a bad habit to get into security-wise. There are binding methods available in the framework and I agree with Arek, that you should lean on the yii framework as much as possible.

To get the scalar string value, if you are insisting on using your own SQL.. I suggest the following:

$sql='
  SELECT CONCAT('Week ',tw.number,' starting ',tw.start_date)
    FROM tbl_week tw
    JOIN (
      SELECT MAX(twi.start_date) max_start_date
        FROM tbl_week twi
        JOIN tbl_person_week tpwi
          ON tpwi.week_id = twi.id
         AND tpwi.person_id = :person_id
         ) i
      ON tw.start_date = i.max_start_date;
';
$command=Yii::app()->db->createCommand($sql);
$command->bindParam(":person_id", $this->id);
return $command->queryScalar();

Upvotes: 0

rzelek
rzelek

Reputation: 4013

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()

Upvotes: 3

Ajitha Ms
Ajitha Ms

Reputation: 555

Use Mysql_fetch _array 

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);
        while($row = mysqli_fetch_array($query)){
        echo $row;
        }

    }

Upvotes: 0

Related Questions