Naeem Marzu Naeem Marzu - 1 year ago 267
MySQL Question

yii2 : remove comma from createCommand

i've create a Command in my controller like this :

public function actionTotal($id)
$query1 = new Query;
$query1 ->select('sum(patient_services.price) price, sum(receipts.price) receipts ,')
->from('patient_services ')
->leftJoin(' receipts ON patient_services.patient_id=receipts.patient_id')
->where('patient_services.patient_id=:id', array(':id'=>$id));
$command1 = $query1->createCommand();
$price = $command1->queryAll();
echo Json::encode($price);

when i try it ... the select code have a comma and idon't know how to remove it

SELECT sum(patient_services.price) price, sum(receipts.price) receipts FROM `patient_services` LEFT JOIN ` receipts ON` `patient_services`.`patient_id=receipts`.`patient_id` WHERE patient_services.patient_id=1

when i remove all commas from the sql code and try it in phpmyadmin .. it works fine :(

Answer Source

You have an invalid leftJoin replace it with this:

->leftJoin('receipts', 'patient_services.patient_id = receipts.patient_id')

also it seems you have an extra comma at the end of your select query remove that last comma the select query would look like this:

$query1  ->select('sum(patient_services.price) price, sum(receipts.price) receipts')

Hope this works.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download