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 |
Welcome Guest, Not a member yet? Register Sign In |