SQL query result to string

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?

Do you need the query string in JSON format? Or do you just want it as plain string? — Zander Rootman
– Zander Rootman, Commented May 28, 2014 at 8:48

rzelek · Accepted Answer · 2014-05-28 10:10:24Z

3

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

answered May 28, 2014 at 10:10

rzelek

4,0331 gold badge36 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chander Over a year ago

Could also work with a CArrayDataProvider built with the results of the query, although CSqlDataProvider is indeed the most straight-forward solution of both

Arth Over a year ago

Your use of queryScalar() is incorrect, check docs

Ajitha Ms · Accepted Answer · 2014-05-28 09:13:48Z

0

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;
        }

    }

answered May 28, 2014 at 9:13

Ajitha Ms

5555 silver badges19 bronze badges

1 Comment

mRiddle Over a year ago

I need to return it into the view instead of echo , and it returns an array rather than string.

Arth · Accepted Answer · 2014-05-28 14:27:32Z

0

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

answered May 28, 2014 at 14:27

Arth

13.1k5 gold badges43 silver badges72 bronze badges

3 Comments

mRiddle Over a year ago

I see. looks more complicated that I first thought. I tried using your code , but I get the white screen of death :)

mRiddle Over a year ago

Nope , completely blank.

Arth Over a year ago

There may be something wrong with the db config.. you should have an application.log file on your webserver with a new error in it too. It'll be pretty hard to dev and debug without access to the stack-traces.

Collectives™ on Stack Overflow

SQL query result to string

3 Answers 3

2 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related