I have a table in a database with start and stop times for each day of the week for some staff in Mysql in the following way:
Agent Day Start End agent1 Mon 17:00 23:00 agent1 Tue 16:00 23:00 agent1 Wed 18:00 23:00
I need this to the displayed in the following way:
Agent Mon Tue Wed
start stop start stop start stop
agent1 17:00 23:00 16:00 23:00 18:00 23:00
Is there a way to do this through a query? I was thinking of doing multiple queries in one query but I don't quite get it.
You can sue a lot of case
select agent,
case day when 'Mon' then start end as start_mon,
case day when 'Mon' then `end` end as end_mon,
case day when 'Tue' then start end as start_tue,
case day when 'Tue' then `end` end as end_the,
case day when 'Wed' then start end as start_wed,
case day when 'Wed' then `end` end as end_wed
from your_table
for gettin the value on the same row of agent you can use a temp table and group bt
select agent, max(start_mon), max(end_mon), max(start_tue), max(end_tue), max(start_wed), max(end_web)
from (
select agent,
case day when 'Mon' then start end as start_mon,
case day when 'Mon' then `end` end as end_mon,
case day when 'Tue' then start end as start_tue,
case day when 'Tue' then `end` end as end_the,
case day when 'Wed' then start end as start_wed,
case day when 'Wed' then `end` end as end_wed
from your_table ) as t
group by agent;
You only need one query where you get all the information in the right order. So first so order by agent and then by day.
As you cannot order the days alphabetically, you need for example a CASE statement to get the right order:
SELECT
*
FROM `your_table`
ORDER BY
`agent`,
CASE
WHEN `Day` = 'Mon' THEN 1
WHEN `Day` = 'Tue' THEN 2
...
WHEN `Day` = 'Sun' THEN 7
END
Storing the day number instead of the text would make this a bit more efficient of course.
Then when you get all your rows, you keep track of the current agent and as soon as the agent changes, you add a new row.
Consider the solution using group_concat function which will get all days for a certain agent with all needed respective time ranges:
SELECT Agent, group_concat(Day,' ', Start, '-', End separator '/') AS dayData
FROM `schedules`
GROUP BY Agent
This will get resultset which will look like as follows:
Agent | dayData
-----------------------------
agent1 | Mon 17:00-23:00/Tue 16:00-23:00/Wed 18:00-23:00
agent2 | .... ..
When the resultset is fetched on PHP side, you can easily process each row with explode('/', $row['dayData']) (getting an array of days with time ranges). Then, explode each item with explode(" ", $day)... and so on. After that, you can create a "beautiful" output using html <table>