Query works in workbench but not in php

Question

I'm trying to assign a variable a value based on a subquery in the where statement. The problem is that it doesn't work in php but in the workbench the query runs fine. I don't get any errors in php and it returns the correct pay ids but the variable field returns empty.

SELECT pay_id, @available AS amount_available
FROM tblpayments payments
WHERE customer_id = 9
AND (
    @available := (pay_amount - (
        SELECT if(sum(applied_amount) IS NULL, 0, sum(applied_amount))
        FROM tblxref_pmt_chg xref WHERE xref_pay_id = payments.pay_id
    ))
) > 0

PROPROTIP: COALESCE(SUM(applied_amount), 0) (SQL standard, whereas IFNULL() is MySQL specific). — eggyal
– eggyal, Commented Dec 18, 2013 at 16:05
@eggyal: Forgot about COALESCE. It's actually got another cool feature too. It accepts infinite parameters and will return the 1st non-NULL one! :-D — gen_Eric
– gen_Eric, Commented Dec 18, 2013 at 16:07
Do you really need to use variables (@available) here? Maybe you could convert this into a JOIN? — gen_Eric
– gen_Eric, Commented Dec 18, 2013 at 16:09

eggyal · Accepted Answer · 2013-12-18 16:47:22Z

3

Why not try using a JOIN instead of a subquery/variable?

Something like this:

SELECT pay_id, (pay_amount - COALESCE(SUM(applied_amount), 0)) AS amount_available
FROM tblpayments payments
LEFT JOIN tblxref_pmt_chg xref ON xref_pay_id = payments.pay_id
WHERE customer_id = 9
GROUP BY payments.pay_id
HAVING amount_available > 0

edited Dec 18, 2013 at 16:47

eggyal

127k18 gold badges220 silver badges244 bronze badges

answered Dec 18, 2013 at 16:11

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

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

5 Comments

gen_Eric Over a year ago

@eggyal: I guess I need more coffee... LEFT JOIN + COALESCE is probably the way to go. Thanks for pointing out my mistakes :)

Ralph Over a year ago

Thanks @RocketHazmat your join idea works perfectly.

rsanchez Over a year ago

@eggyal COALESCE wasn't superfluous now that it's a LEFT JOIN. And even before, nothing indicates that applied_amount can't be null.

gen_Eric Over a year ago

You're welcome! :-) Also, thanks to @eggyal for fixing it up :D

eggyal Over a year ago

@rsanchez: Whoa. My bad. Eats humble pie

Collectives™ on Stack Overflow

Query works in workbench but not in php

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related