• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQL query issue again

#1
$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
Reply

#2
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 )
Reply

#3
(11-03-2020, 01:27 PM)InsiteFX Wrote: 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?
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
Reply

#4
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 )
Reply

#5
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
Reply

#6
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 )
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.