Thread

Hi guys, I have quite a complicated SQL query:

$query = $this->db->query("SELECT t1.numberofbets, t1.profit, t2.seven_profit, t3.28profit, user.user_id, username, password, email, balance, user.date_added, activation_code, activated FROM user LEFT JOIN (SELECT user_id, SUM(amount_won) AS profit, count(tip_id) AS numberofbets FROM tip GROUP BY user_id) as t1 ON user.user_id = t1.user_id LEFT JOIN (SELECT user_id, SUM(amount_won) AS seven_profit FROM tip WHERE date_settled > '$seven_daystime' GROUP BY user_id) as t2 ON user.user_id = t2.user_id LEFT JOIN (SELECT user_id, SUM(amount_won) AS 28profit FROM tip WHERE date_settled > '$twoeight_daystime' GROUP BY user_id) as t3 ON user.user_id = t3.user_id where activated = 1 GROUP BY user.user_id ORDER BY user.date_added DESC");

return $query->result_array();

The query works fine, running it in phpMyAdmin and returns the following results (in image attached). However, the PHP array contains an empty value for seven_profit, the other values are passed through.. I just can’t see, when printing the array, that one field, which should have value of 26, contains nothing? Any ideas? I changed the field name from starting with a number, but no change..

TL:DR;

SQL output (tested in phpMyAdmin) is missing data for a field when put into a PHP array

Anything is appreciated, could be a pain to solve.. thanks guys

Ok the problem was the sql query includes php variables, these variables must just be treated as 0 or something without giving errors, so it displays a value in the seven_profit column

When the query is ran in CodeIgniter it uses the correct value for $seven_daystime variable, and therefore the result is different when the data is put into the array.

/blames tiredness