unistake Posted May 18, 2016 Share Posted May 18, 2016 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 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted May 18, 2016 Share Posted May 18, 2016 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? Quote Link to comment Share on other sites More sharing options...
unistake Posted May 18, 2016 Author Share Posted May 18, 2016 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! Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 18, 2016 Share Posted May 18, 2016 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', ...) 1 Quote Link to comment Share on other sites More sharing options...
unistake Posted May 18, 2016 Author Share Posted May 18, 2016 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> Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 18, 2016 Share Posted May 18, 2016 $data = array(); // define the data array while($row=mysqli_fetch_assoc($result)) { $data[$row['Code']][$row['SectorDate']] = $row; } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.