i have following query. when i runs it on sql command line its giving me proper result but when i am trying it with createCommand() its showing somethings else only.
$sql = "SELECT * from project p join (select * from project where id=:PROJECTID)
pp ON p.category=pp.category ORDER BY RAND() limit 4";
$bindValues = array(
':PROJECTID' => $projectId,
);
$kal = Yii::app()->db
->createCommand($sql)
->bindValues($bindValues)
->queryAll();
echo '<pre>', print_r($kal); exit;
There is difference in result between Sql and createCommand.
i have tried running following query through createCommand() and Sql Command Prompt (Phpmyadmin).
$sql = "SELECT * FROM user_fund_project ufp
JOIN reward ON reward.id = ufp.reward_id
JOIN project ON project.id = ufp.project_id
WHERE ufp.id = :UFID";
$funds = $this->fetch($sql,array(':UFID' => $ufid),'queryRow');
//fetch function
public function fetch($sql="", $bindValues = array(),$method = 'queryAll') {
return Yii::app()->db
->createCommand($sql)
->bindValues($bindValues)
->{$method}();
}
Getting following difference :
CreateCommand()
return 58 Fields <?php count($funds); ?>
SQL Prompt
return 63 fields
The fields difference goes the following way
sql returned 3 different id column (1 from user_fund_project, 1 from reward and 1 from project) where createCommand() returned 1 id column, others got overwritten.
sql returned 2 different user_id column(1 from reward, 1 from user_fund_project) where createCommand() returned 1 user_id column, others got overwritten.
sql returned 2 different project_id column(1 from reward, 1 from user_fund_project) where createCommand() returned 1 project_id column, others got overwritten.
sql returned 2 different status column(1 from project, 1 from user_fund_project) where createCommand() returned 1 status column, others got overwritten.
It’s not a bug in createCommand(), it’s a side effect of how PDO returns the result. For each row returned, you get an associative array in the following form:
The column name doesn’t include the table name or alias, so only one column with the same name can be retrieved.
You either need to create a specific column alias for each ambiguous column, or you could try setting the first parameter of queryAll() to false, to return the rows indexed by a numeric ID.