SQL to CodeIgniter Array Missing Data Issue

Question

$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 complete results (in image attached). However, printing the array in CodeIgniter, it has no value for one field ,seven_profit, where it is there in the SQL query ran in phpMyAdmin, just the discrepancy in this one field, from sql to php array... I just can’t see why, 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 in attempt to fix it, but no difference.

I know this is complex and looks horrible, any help or just people coming across something similar would be great to know about, thanks.

Sam

---

edit: the query a bit more formatted:

$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
");

Answer 1

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