+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: SQL query issue again (/showthread.php?tid=77907)
SQL query issue again - richb201 - 11-03-2020
$sql = "SELECT item FROM survey_results WHERE user_email = ? AND taxyear = ? AND item_type='AC' "; //only look at Activities
$query2 = $this->db->query($sql, array($employee_email, $taxyear));
This query seems to work fine when I use [email protected] and 2017.
But when I use [email protected] and 2017 it doesn't find anything. Could there be a problem with using an "@" in the string? If true, why would @yahii.com work? I originally had the taxyear as an integer. I changed that to a varchar. taxyear varchar(11) utf8mb4_bin. Could it be that my '2017' is only four chars, not 11?
RE: SQL query issue again - InsiteFX - 11-03-2020
No it is a perfectly legal character, emails are used all the time in auth systems.
I would check your database table and make sure the values are correct and in there.
For taxyear why not use a date field?
RE: SQL query issue again - richb201 - 11-05-2020
(11-03-2020, 01:27 PM)InsiteFX Wrote: No it is a perfectly legal character, emails are used all the time in auth systems.Taxyear is 4 digits like "2017". It really should be something like 201706 which means the last day of june of 2017. I should probably fix that. It is not really a regular "date".
I would check your database table and make sure the values are correct and in there.
For taxyear why not use a date field?
RE: SQL query issue again - InsiteFX - 11-05-2020
Varchar is a variable length so if you have a varchar(11) and only put a date in it it will
only use the 4 character spaces of the 11.
RE: SQL query issue again - richb201 - 11-07-2020
Well, I modified the code to build a few temporary tables and then do the calculations from that. This is what I have
SELECT srat.employee_email, srat.taxyear, (SUM(kt.qualified)/COUNT(*))*100 as percent FROM survey_results_activities_temp srat CROSS JOIN survey_results sr CROSS JOIN key_temp kt WHERE srat.taxyear=sr.taxyear AND srat.employee_email=sr.user_email AND sr.item=kt.activity AND sr.item_type='AC'
This results in
employee_email taxyear percent
[email protected] 2016 66.6667
which is correct. But I really want a listing for every employee email in survey_results_activities_temp, not just one guy. I am not expert in SQL but I think what I need is an OUTER LEFT JOIN since survey_results_activities_temp has the employee_email address of many employees, while the other fields are NULL. Problem is when i replace CROSS with LEFT, I get an error from mysql. The question is how to use Left Joins in multitable joins?
RE: SQL query issue again - InsiteFX - 11-08-2020
SEE:
How to LEFT JOIN Multiple Tables in SQL
I' m sure you can find some thing on that web site.