trouble with SQL bindings

I have two versions of the same sql statement; one using bindings and one not using bindings. I am trying to get the results for each  employee named in srat.employee. The one with the binding  returns 116 rows. The one without the binding returns 4 rows. The second one without the bindings is correct, the answer should be four. I'd like to be able to run the query that returns 4 rows for each employee_email. How can I create a loop where I can run the query with bindings for each strat.employee_email?


//run with bindings
    $sql = "SELECT  srat.employee_email,srat.taxyear, sr.item, kt.qualified, kt.unqualified
          FROM survey_results_activities_temp srat, survey_results sr, key_temp kt
          WHERE sr.user_email=? AND sr.taxyear=? AND sr.item_type='AC' AND sr.item=kt.activity";
        $query6=$this->db->query($sql, array($email,$taxyear));

//run without bindings
        $sql = "SELECT  srat.employee_email,srat.taxyear, sr.item, kt.qualified, kt.unqualified
          FROM survey_results_activities_temp srat, survey_results sr, key_temp kt
          WHERE sr.user_email=srat.employee_email AND sr.taxyear=srat.taxyear AND sr.item_type='AC' AND sr.item=kt.activity";
        $query6 = $this->db->query($sql);

//here I total the qualified and unqualified for each employee

proof that an old dog can learn new tricks

Code:

WHERE sr.user_email=? AND sr.taxyear=? AND sr.user_email=srat.employee_email AND sr.taxyear=srat.taxyear AND sr.item_type='AC' AND sr.item=kt.activity

Just a guess..

OK. I tried that. Now I am getting 24 rows as the result. Some of those rows are showing employees having activities that don't actually exist in sr. The AC filter didn't work at all.

The real question is why did the one without the bindings work and the with the bindings not work. I think CI has a way to see what the last SQL statement was. Perhaps the two created SQL statements are not the same? 

I used last_query and see this:
SELECT  srat.employee_email,srat.taxyear, sr.item, kt.qualified, kt.unqualified         
   FROM survey_results_activities_temp srat, survey_results sr, key_temp kt
            WHERE sr.user_email='[email protected]' AND sr.taxyear='2016' AND sr.item_type='AC' AND sr.item=kt.activity AND sr.taxyear=srat.taxyear

SELECT  srat.employee_email,srat.taxyear, sr.item, kt.qualified, kt.unqualified
            FROM survey_results_activities_temp srat, survey_results sr, key_temp kt
            WHERE sr.user_email=srat.employee_email AND sr.taxyear=srat.taxyear AND sr.item_type='AC' AND sr.item=kt.activity

The first one doesn't work. The second one works, but only returns the first employee's data. That would be fine since I could just run the query once for each employee. But how do I get it to run on the second employee (and the third, etc)? 

proof that an old dog can learn new tricks

Your issue isn't exactly with query bindings or CI, you'd have the same issues if you ran the queries directly on the DB. Maybe try this..

PHP Code:

SELECT srat.employee_email, srat.taxyear, sr.item, kt.qualified, kt.unqualified
FROM survey_results_activities_temp AS srat
LEFT OUTER JOIN survey_results AS sr ON sr.user_email = srat.employee_email AND sr.taxyear = srat.taxyear
LEFT OUTER JOIN key_temp AS kt ON kt.activity = sr.item
WHERE srat.employee_email = ? AND srat.taxyear = ?
GROUP BY srat.employee_email 


Thanks. I think that worked.

proof that an old dog can learn new tricks