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;
