PHP/SQL fetch multiple values on SUM SQL statement

Question

This is a bit of a complicated issue to explain but here goes: I have an SQL statement:

SELECT 
SUM(time.timein),  
time.reasonforabsence
WHERE 
staff.id = time.staff_id 
AND 
staff.department_id = department.id  
AND 
(staff_name LIKE '%$staffsearch%') 
GROUP BY 
staff.id 
ORDER BY 
time.dateadded
ASC;

From this statement I need to pull the values time.reasonforabsence but as this is text and the statement is grouped, I cannot seem to do this. Does anyone know if there is a way for me to pull them possibly into a PHP array.

The time.reasonforabsence has multiple possible values.

Sorry for the vagueness I am writing this in a rush. Let me know if there is anymore info needed and I will add it tomorrow.

Can you post information about what your table structure looks like and what is the current and desired output of your query ? — Maximus2012
– Maximus2012, Commented Jul 30, 2015 at 16:32

JSantos · Accepted Answer · 2015-07-30 16:35:17Z

1

What you can do is return an aggregated string

SELECT 
SUM(time.timein),  
GROUP_CONCAT(time.reasonforabsence)
...

You can optionally use DISTINCT if you don't want repeated reasons. On php you'll have split them

answered Jul 30, 2015 at 16:35

JSantos

1,70822 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Anthony Broadbent Over a year ago

Thanks! That worked but I have another question but this may be better on another thread. Now I have the values "Holiday Full Day,Sick Full Day,Sick AM,Sick PM,Holiday Full Day,Holiday AM,Holiday PM,Absent Full Day,Absent AM". How can I break them up in PHP. I tried: ($rowcsv['reasonforabsence'] is the string), $reasonforabsence = $rowcsv['reasonforabsence']; $reasonsforabsence = str_replace('"','',$reasonsforabsence ); but I just get the same output. Im trying to output this to csv but since the string is in quotes I am just getting the string in 1 cell.

JSantos Over a year ago

Try explode( ',', $rowcsv['reasonforabsence'] ) and you'll get an array with the results

JSantos Over a year ago

And remove that $reasonsforabsence = str_replace('"','',$reasonsforabsence );. That ain't doing anything

Anthony Broadbent Over a year ago

For this array to be formatted correctly I would need to $reasonforabsence = implode('", "', $reasonforabsence); but this still doesnt work. I get

"Holiday Full Day"",""Sick Full Day"",""Sick AM"",""Sick PM"",""Holiday Full Day"",""Holiday AM"",""Holiday PM"",""Absent Full Day"",""Absent AM"

I would have thought that the new quote would have created each value in a new cell but its just not working. Frustrated or what...

JSantos Over a year ago

Maybe you should open a new question. I'm kind of lost on what exactly is your problem

Collectives™ on Stack Overflow

PHP/SQL fetch multiple values on SUM SQL statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related