Issue With Json_Encode $Command->Queryrow()

Hi,

When I json_encode result set from $command->queryRow() it stringy all the fields and its hard to distinguish data type of the fields. Specially boolean and int values are stringed.

Here is sample code snippet




$connection = Yii::app()->db;

        $command = $connection->createCommand();

        $command->select('*)

         ->from('LISTING l');

         ->where(array('and', 'l.id=:id'), array(':id' => 1));

$result = $command->queryRow();


return json_encode($result,true);




result set is like this, not the one I expected




{

   "id" : "1",

   "transmission" : "0",

   "doors" : "4",

   "title" : "New BMW"

}



In the above code,

id is integer

transmission is boolean and

doors is integer.

Somehow they are strings :-(.

In Yii $command->queryRow() case, I know that it returns object, however json_encode second argument TRUE makes it convert object to associate array though, there is discrepancy in representing the correct format of the data.

I also tried to CJSON::encode() method, no luck, it seems same as json_encode.

This is what I suppose is the correct representation




{

 "id" : 1,

 "transmission" : 0,

 "doors" : 4,

 "title" : "New BMW"

}



how do I achieve the above format, provided no manual conversion is applied for each field before json_encode :-).

All query* methods of CDbCommand return arrays. Only find* method of CActiveRecord return objects.

CDbCommand is just a thin wrapper over PDO, which is not type aware at all. Although the query results in database are typed, they are returned as strings to PHP becase the conversion sometimes is not simple so no assumptions are made.

You will need to iterate over each column in each row and cast values manually.

Thanks @nineinchnick,

That’s quite bad if you use YII to implement REST API. We are exposing wrong JSON format that other platforms might have difficulty understanding such as .NET and Java. Specially for boolean and integer values that are treated as string, other languages would have hard time understanding.

On the other side, if we were to loop through each field and manually cast it, I assume it would be slow and time consuming. :-(. Hoping would there be solution out there :-).

If in doubt, benchmark. I won’t agree with the speed issue, you have to do it one way or another.

Whatever your use case is, just format the output. There should be an appropriate layer for that in your design anyway.

Hey @nineinchnick,

Thanks for your input, those were worthy suggestions from you.

Yes your right, there should be a design in place to format the output. I’m going to work on that :-).

Thanks for your comments again, appreciate that.