Jump to content
unistake

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

Recommended Posts

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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', ...)

  • Like 1

Share this post


Link to post
Share on other sites

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>

Share this post


Link to post
Share on other sites

$data = array(); // define the data array
while($row=mysqli_fetch_assoc($result))
{
    $data[$row['Code']][$row['SectorDate']] = $row;
}

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.