PHP MySQL Result Set

Question

I am definitely stumped on how to arrange a result set.

I have a table which currently contains strictly dates.

DATES   
ID  Dates
1   2015-05-26
2   2015-05-27
3   2015-05-28
4   2015-05-29
5   2015-05-30

each of those dates are also the name of the table for that day which keeps track which user/agent was present for training.

2015-05-26
ID  Attendance
101 Present
201 N/A
301 Present
401 Present
501 Present

The end goal for this is to create the following table:

enter image description here

Now here is what I have accomplished for the table header.

$data_date = true;

$list = "SELECT * FROM list ORDER BY id DESC LIMIT 5";
$result = mysql_query($list);
while ($row = mysql_fetch_assoc($latest)){
    $dates[] = $row['date']; 
}
foreach($dates as $x){
    $data_date .= "<th>$x</th>";
}

By simply echoing out the $data_date I am able to display the headers like the following.

<table>
<tr>
<th>AGT</th>
<?php echo $data_date; ?>
</tr>
<tr>
</tr>
</table>

enter image description here

So far so good. The final step of taking the array and creating new row for each and querying each day from the dates array has me at a loss.

I've been trying for over an hour and cant seem to wrap my head around the array needed to query a new row for each agent.

Any pointers or help would definitely be appreciated.

You should fix your data structure so you have one table with all the dates. If you need to, you should have an index on the column or partition the data by it. For what you need to do, having to resort to dynamic SQL is a bad idea. — Gordon Linoff
– Gordon Linoff, Commented May 30, 2015 at 15:11
Its is a train wreck lol, Unable to change the current structure which is why this query is being a pain.. — renfley
– renfley, Commented May 30, 2015 at 15:28

Drew · Accepted Answer · 2015-05-30 15:32:28Z

1

perhaps this is a start. date-math routines can grab 5 day attendance results

consider the following

create table agent
(
agentid int NOT NULL PRIMARY KEY,   -- use AUTO_INCREMENT, whatever
agentname varchar(60)
);

insert agent (agentid,agentname) values (109,'Guy Smiley');
insert agent (agentid,agentname) values (110,'Susie Chapstick');
-- select * from agent

create table class_session
(   -- a session is say Monday to Friday, captures a start date
sessionid int not null PRIMARY KEY, -- the week session #
startdate datetime not null,
location varchar(40) -- etc etc
);

insert class_session (sessionid,startdate,location) values (1001,'10/1/2014','Ritz London');

create table session_signup
(   -- the list of agents who should be present
sessionid int not null, -- the week session #
agentid int not null
);


create table agent_present
(
sessionid int not null, -- the week session #
agentid int NOT NULL,
thedate datetime not null,
thestatus varchar(40) -- present, present but sleeping, etc
);

answered May 30, 2015 at 15:32

Drew

25k10 gold badges47 silver badges81 bronze badges

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

1 Comment

renfley Over a year ago

Thanks Drew, I think you gave me a quick fix to my issue, Ill create a new table, loop through the array and insert the ATD Atendance and date based on the name of the table, Will try this out and will report back.. :)

Collectives™ on Stack Overflow

PHP MySQL Result Set

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related