php - MySQL SSH console runs subquery correctly but mysqli returns zero for subquery column

Question

I'm hesitant to post the entire query but perhaps it's necessary with my variable assignments. In short, the tenth column is a subquery and should return a count of occurrences for something in another table.

SQLFiddle: http://sqlfiddle.com/#!9/3718e/2

User ID 366 is a test production account. This is normally a dynamically-binded ? prepared variable. User ID 366 should return two rows. The nine columns aren't important for this question, but the 10th (the subquery) should have a count of 1 and 0 in the first and second rows, respectively. This works on an SSH connection to MySQL. However, PHP's mysqli and even PhpMyAdmin return 0 and 0 respectively. I have sandboxed the PHP so the error is lying in the SQL I believe.

Sample correct output from SSH (9 columns before ignored):

+--------+ | guests | +--------+ | 0 | | 1 | +--------+

mysqli will return just zeros. Other columns are unaffected and SSH/mysqli both agree and return correctly. If more context is needed, let me know.

Thank you.

---

SELECT tickets.id, @formal_id:=schedule.id as 'formal_id', schedule.name, schedule.date, schedule.colour, @li_price:=schedule.livers_in_price as 'li', @lo_price:=schedule.livers_out_price as 'lo', @gu_price:=schedule.guests_price as 'gu', tickets.for_sale as 'for_sale', @guest:=( SELECT COUNT(*) FROM billing WHERE guest = 1 AND user_id = @user_id AND formal_id = @formal_id ) AS 'guests' FROM tickets JOIN user ON tickets.user_id = user.user_id JOIN schedule ON tickets.formal_id = schedule.id JOIN billing ON tickets.formal_id = billing.formal_id WHERE user.user_id = 366 AND schedule.date > NOW() GROUP BY tickets.id;

标签： phpmysqlmysqli

Answer 1

without specifying the table source for a variable when joins are in play you can get unexpected results. Using table.column syntax will save you a lot of grief.

In this case AND user_id = tickets.user_id

but I cant explain the inconsistent results between your testing and mine.