Arranging data from mysql in to a table sorted by users and dates

[unistake]

Hi all,

 

I have some data in a table to do with 20 users appointments every day for a month.

 

I am trying to organise the appointments in to a large table so the clients can see all the appointments of every colleague by date (in columns) and by user name (in rows).

 

for example:

 

                             20th May            |          21st May         |        22nd May         |        23rd May

 

Mike Jones          London, Paris    |          Paris, Rome     |      Rome, London    |     London, New York

                            06:15 - 10:15       |        08:20 - 14:00     |      03:30-05:30        |     05:25-09:30

-----------------------------------------------------------------------------------------------------------------------------------------

Bethan Cray            OFF                |       Belfast, Madrid   |      Madrid, Fez         |        Fez, Athens

                            11:15 - 16:35       |        08:45 - 12:10     |      13:55-15:30        |       09:25-13:20

 

 

 

The data is currently stored in the mysql table such as below, and has over 40 users with over 30 rows each in the same format. 

 

SectorDate            Code                    BeginTime            Dep              Arr              EndTime         

2016-05-20         Mike Jones                    06:15               London         Paris                 10:15

2016-05-21         Mike Jones                    08:20                Paris             Rome               14:00

2016-05-20         Bethan Cray                  11:15                 OFF                                     16:35

 

 

Please can someone show how best to arrange the mysql database to give the output above!

 

Thanks

[ginerjm]

Could be difficult to present since you could have a lot of columns depending upon how many 'dates' are stored/active at any given time. Do you want to show all the days that are currently scheduled or just a window of time?

 

Have you written you query yet? Have you asked yourself these question?

[unistake]

I just need to show about 30 days in advance. So there will not be too a huge amount of rows to extract data from. A few hundred..

 

I have tried writing the script but I know it is far past my experience so far. Without even considering code, I cant think logically how this would work so I can attempt the right way to do it with php!

[mac_gyver]

you would query for the data you want BETWEEN a range of dates - WHERE SectorDate BETWEEN 'some start date' AND 'some end date'. you will reuse these start and end dates when you display the results, you would also ORDER BY the user name so that all the rows for each user name are together in the result set and the user names are in the order that you want to display them. the ordering of the dates in the result set is not important.

 

you would retrieve the data that the query matches and store it into a multi-dimensional array, with the 1st array dimension/index being the user name and the 2nd array dimension/index being the date.

 

to produce the output, you would loop over the array of data. this will give you the user name and a sub-array of dates and data for each date for that user. you would then loop over the dates from the start date to the end date and if there is data (the date array index value exists in the current sub-array of data) for any date, display it. if there is no data for any date, you would display whatever output you want for this condition (an empty cell, 'N/A', ...)

[unistake]

Cheers mac_gyver,

 

Fortunately I was thinking along those lines.

 

So far I have the code below however I do not know how to store my matched queries in a multidimensional array indexed firstly by $row['Code'] and then by $row['SectorDate'] as you mentioned.

 

This is the code I have so far:

<table class="">
<thead>
	<tr>
	  <th> </th>
<?php
 // Set timezone
 date_default_timezone_set('UTC');
 
 // Start date
 $date = date("Y-m-d");
 // End date
 $end_date = date ("Y-m-d", strtotime("+30 days", strtotime($date)));
 
 while (strtotime($date) <= strtotime($end_date)) {
				echo "<th>".date("D", strtotime($date))."<br />".date("d M", strtotime($date))."</th>";
				$date = date ("Y-m-d", strtotime("+1 day", strtotime($date)));
 }
 
?>
	</tr>
</thead>
<tbody>
<?php
$today = '2016-05-18';
$sql = "SELECT rosters.Code,rosters.SectorDate,rosters.Duty,rosters.BeginTime,rosters.Dep,rosters.Arr,rosters.EndTime,Users.Ffname FROM rosters INNER JOIN Users ON rosters.Code = Users.Code WHERE Users.Base = 'DUB' AND rosters.SectorDate BETWEEN '2016-05-18' AND '2016-05-28' ORDER BY rosters.Code";
$result = mysqli_query($cxn,$sql)
	or die($cxn->error);
while($row=mysqli_fetch_array($result)) {
	foreach($row as $key=>$value) {
		echo $key.' '.$value.'<br />';
	}
		echo '<p> </p>';
}
?>						
</tbody>
</table>

[mac_gyver]

$data = array(); // define the data array

while($row=mysqli_fetch_assoc($result))

{

    $data[$row['Code']][$row['SectorDate']] = $row;

}