SQL query issue again |
$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?
proof that an old dog can learn new tricks
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? What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )
(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".
proof that an old dog can learn new tricks
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. What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )
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?
proof that an old dog can learn new tricks
SEE:
How to LEFT JOIN Multiple Tables in SQL I' m sure you can find some thing on that web site. What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )
|
Welcome Guest, Not a member yet? Register Sign In |