Yii, querying gives array to string error

Question

$paymentIDs = Yii::app()->db->createCommand('SELECT payment_id FROM client_package WHERE package_id = :pid AND payment_id IS NOT NULL')->bindValue(':pid', $pid)->queryAll();

$total = Yii::app()->db->createCommand('SELECT count(*) FROM payment WHERE id IN ('.implode(",", $paymentIDs).') AND date BETWEEN \':mS\' AND \':mE\'')->bindValue(':mS', $monthStart)->bindValue(':mE', $monthEnd)->queryScalar();

The above is my code for a query. As you can see, I queried for payment_id(s) according to a certain criteria. And I now have a set of numbers.

When I execute the second query, it fails, throwing me an error

Array to String conversion

I have also tried this:

$total = Yii::app()->db->createCommand('SELECT count(*) FROM payment WHERE id IN ('.implode(",", $paymentIDs['payment_id']).') AND date BETWEEN \':mS\' AND \':mE\'')->bindValue(':mS', $monthStart)->bindValue(':mE', $monthEnd)->queryScalar();

Now, this gives me an error saying:

Undefined index: payment_id

Can someone please point out my mistake? Your help would be much appreciated.

Thanks.

Don't put \' around parameters. Also what values does parameters contains? var_dump($pid, $monthStart, $monthEnd, $paymentIDs);. Also consider using chinned version of Query Builder ...->select()->where()... or CActiveRecord and CDbCriteria — Justinas
– Justinas, Commented May 14, 2015 at 8:15
I have tested, they do return a valid/desired result. Basically the parameters are all working fine. And, why not put \' around the date? isn't it suppose to be like BETWEEN '2015-05-01' AND '2015-06-01'? what $monthStart and $monthEnd give are: 2015-05-01 and 2015-06-01, yes, without quotes. — NewbieCoder
– NewbieCoder, Commented May 14, 2015 at 8:19
Because Yii adds ' automatically to variables. If every parameter is of type string, than you have error somewhere else, try to figure out in what file and line error is thrown. (In error page $line and $file) — Justinas
– Justinas, Commented May 14, 2015 at 8:21
I don't think you understand my question correctly. What I have tested again is, in the 2nd query, in the implode, I took whatever there is in $paymentIDs (after print_r), I inserted the numbers into the implode, it's working fine, for example, ... IN (2,7,8,13,14,17).. this would work fine. BUT not if I were to do ... IN(implode(",", $paymentIDs))... — NewbieCoder
– NewbieCoder, Commented May 14, 2015 at 8:29

ifcanduela · Accepted Answer · 2015-05-14 08:48:52Z

1

The result of the first query, $paymentIDs, is an array of arrays (or plain objects, I'm not sure). You can try inspecting it in your IDE or simply use var_dump to see its structure. You can use the listData method of the CHtml class to get closer to what you want, like this:

$ids = CHtml::listData($paymentIDs, 'payment_id', 'payment_id');

That will flatten your array to an array of strings. Then...

implode(",", $ids)

...will give you the result you want.

answered May 14, 2015 at 8:48

ifcanduela

662 bronze badges

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

Comments

Habibul Morsalin · Accepted Answer · 2015-05-14 08:58:29Z

1

You can solve this using single Query like this :

 $total = Yii::app()->db->createCommand('SELECT count(*) FROM payment WHERE id IN (SELECT payment_id FROM client_package WHERE package_id = :pid AND payment_id IS NOT NULL) AND date BETWEEN \':mS\' AND \':mE\'')->bindValue(':pid', $pid)->bindValue(':mS', $monthStart)->bindValue(':mE', $monthEnd)->queryScalar();

Thanks You :)

answered May 14, 2015 at 8:58

Habibul Morsalin

1801 silver badge11 bronze badges

Comments

BaCaRoZzo · Accepted Answer · 2015-05-14 20:17:49Z

1

Your first query returns array of objects (not array of payment_ids). To get array of values of some column you can use ->queryColumn(). So the first query must be:

$paymentIDs = Yii::app()->db->createCommand('SELECT payment_id FROM client_package WHERE package_id = :pid AND payment_id IS NOT NULL')->bindValue(':pid', $pid)->queryColumn()

If variables $monthStart and $monthEnd have a string type (or a type that can be converted to string), the code must work fine.

In second query you can use bindValues instead of double bindValue.

edited May 14, 2015 at 20:17

BaCaRoZzo

7,7328 gold badges54 silver badges88 bronze badges

answered May 14, 2015 at 19:15

Taron Saribekyan

1,3908 silver badges13 bronze badges

Collectives™ on Stack Overflow

Yii, querying gives array to string error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related