Which it is.
Which it also is.
(EDIT: Changed the query’s spacing so you can see the query’s not doing anything sneaky.)
Which it is.
Which it also is.
(EDIT: Changed the query’s spacing so you can see the query’s not doing anything sneaky.)
Oh my bog you have it beautifully. But for some reason when i try on mine those values are switch and carlas 1 doesnt show either
I tried your other one and it said incompleted tasks, completed tasks and late tasks was undefined
Yeah, in my last query, i’ve changed the final column names to “incomplete”, “complete”, and “late”, so you’d have to change your PHP code to look for those instead.
waaaaaait in your other one u dont have the IfNull(T.incomplete_tasks,0)as incomplete part. Thats why.
The image above uses this: (for ease of copy-pasting)
SELECT employee.empfname, employee.emplname,
IfNull(T.incompleted_tasks,0) AS incomplete,
IfNull(T.completed_tasks,0) AS complete,
IfNull(T.late_tasks,0) AS late
FROM employee
LEFT JOIN (
SELECT employee.eid,
SUM(CASE WHEN tasks.stats = 0 AND tasks.pf = 1 THEN 1 ELSE 0 END) AS incompleted_tasks,
SUM(CASE WHEN tasks.stats = 1 AND tasks.pf = 0 THEN 1 ELSE 0 END) AS completed_tasks,
SUM(CASE WHEN tasks.stats = 1 AND tasks.pf = 1 THEN 1 ELSE 0 END) AS late_tasks
FROM employee
LEFT JOIN tasks ON tasks.eemail = employee.eid
WHERE tasks.datepost BETWEEN "2022-09-01" AND "2022-09-30"
GROUP BY employee.eid
) AS T
ON employee.eid = T.eid;
You’ll need to replace the date strings, obviously, but i’m running this query in a fiddle, rather than in PHP, so i needed to put actual strings in there for testing.
This works well when i use the direct dates but the moment i add the ones to check for previous dates it goes back to that mess up. Is this wrong?
$StDate = new DateTime("first day of last month");
$EnDate = new DateTime("last day of last month");
$StartDate = $StDate->format('Y-m-d');
$EndDate = $EnDate->format('Y-m-d');
I call it in like this
WHERE tasks.datepost BETWEEN $StartDate AND $EndDate GROUP BY employee.eid
If you echo out $StartDate and $EndDate , do they look correct? and if so, are you absolutely sure your data is as you say it is in post 13?
This is what it echos 2022-09-012022-09-30 and the dates in my system shows like this 2022-09-01. So that should be right i think
oh wait. You’re sticking these in without quotes because you’re not doing parameter passing…
Try this as a temporary fix, and spend some time when you can looking at Prepared Statements. They’ll be a lot of help to you in the future.
WHERE tasks.datepost BETWEEN \"$StartDate\" AND \"$EndDate\" GROUP BY employee.eid
IT FINNNAALLLLYYYY WORKKED. Maaaaaaan i was on this jazz for weeks. THANK YOU
Only because it works didn’t mean it is correct. The query only works because of a bug in the old MySQL version and it will give you a random empfname which might lead to errors hard to find in the future. Also this query will not work in any other SQL dialect.
And, we are here to help people getting better. So we should not teach them wrong things
instead of
GROUP BY tasks.eemail
i would write
GROUP BY employee.empfname
, employee.emplname
because (1) sometimes eemail
might be NULL, and (2) i insist on my GROUP BY and SELECT clauses having at least a nodding acquantance with each other
Apologies for possibly sounding dumb here but what do u mean by nod?
He’s saying that even if you ignore the bug/feature/whatever in older MySQL versions that allow grouping by fields you’re not selecting, they should be related to one another. Which is perfectly fair and true, but i’ll be honest and say my group-by was the least of my concerns with that query when i wrote it
expertly summarized, thanks, m, that is indeed what i had in mind
to @cheeseburgers123 – “nod” means game recognize game
Thats a peculiar nod but okay. waow
Ive never heard the word cheeseburger said so many times in 1 sentence. It repeats in my head now… Thanks. A lot.
Ah kk
If you group by their last name and first name would that mean that if there were two people with the same first name and last name the results in the table would be wonky?
Also eemail is actually the foreign key in the tasks table so it holds the users ID not their email. I wasnt sane when i named my columns
well, sure it would
Yes. If there are 2 John Smiths, they will get lumped together if you group by John Smith.
If this is a concern for you, group by email and select MAX(fname), MAX(lname) or something (apply a group function that wont change the result of the group; a group gathered by email should have all the same fname and lname, so the MAX of that set of values will just be the fname and lname).