Barand

Booking date looks OK when I run it. I had to comment out the trainername column, that should be in a trainer table, not in availability table) mysql> SELECT trainer -> -- , trainername -> , dayofweek -> , bookingdate -> , CONCAT(from_time,'') as from_time -> , to_time, timeslot -> FROM -> ( -> SELECT a.trainer -> -- , a.trainername -> , dayofweek -> , bookingdate -> , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot -> , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time -> , start_time as to_time -> , @prevend := end_time as prevend -> , @prevdate := bookingdate as prevdate -> FROM bookingavailability a -> JOIN (SELECT @prevend:=null,@prevdate:=null) as init -> INNER JOIN bookingscalendar c -> ON a.trainer = c.trainer -> AND WEEKDAY(c.bookingdate) = a.dayofweek -> -> UNION -> -> SELECT a.trainer -> -- , a.trainername -> , dayofweek -> , bookingdate -> , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot -> , IFNULL(MAX(end_time),open_time) as from_time -> , close_time as to_time -> , null as prevend -> , null as prevdate -> FROM bookingavailability a -> LEFT JOIN bookingscalendar c -> ON a.trainer = c.trainer -> AND WEEKDAY(c.bookingdate) = a.dayofweek -> GROUP BY a.trainer,dayofweek,bookingdate -> ) as gaps -> WHERE timeslot > '00:00:00' -> ORDER BY trainer, dayofweek, bookingdate, from_time; +---------+-----------+-------------+-----------+----------+----------+ | trainer | dayofweek | bookingdate | from_time | to_time | timeslot | +---------+-----------+-------------+-----------+----------+----------+ | 1 | 0 | 2014-08-18 | 09:00:00 | 10:00:00 | 01:00:00 | | 1 | 0 | 2014-08-18 | 11:00:00 | 13:00:00 | 02:00:00 | | 1 | 0 | 2014-08-18 | 14:30:00 | 16:00:00 | 01:30:00 | | 1 | 0 | 2014-08-18 | 17:30:00 | 20:00:00 | 02:30:00 | | 1 | 1 | 2014-08-19 | 10:30:00 | 17:00:00 | 06:30:00 | | 1 | 2 | 2014-08-20 | 11:00:00 | 12:00:00 | 01:00:00 | | 1 | 2 | 2014-08-20 | 13:00:00 | 15:00:00 | 02:00:00 | | 1 | 3 | 2014-08-21 | 08:00:00 | 10:00:00 | 02:00:00 | | 1 | 3 | 2014-08-21 | 11:00:00 | 13:00:00 | 02:00:00 | +---------+-----------+-------------+-----------+----------+----------+ Check your data

From your data +--------+--------+------+ | worker | client | main | +--------+--------+------+ | w1 | c1 | 1 | | w1 | c3 | 0 | | w1 | c4 | 0 | | w1 | c5 | 0 | | w2 | c1 | 0 | | w2 | c2 | 1 | | w2 | c4 | 0 | | w3 | c3 | 0 | | w3 | c4 | 0 | | w3 | c5 | 1 | | w4 | c1 | 0 | | w4 | c2 | 0 | | w4 | c4 | 0 | | w4 | c5 | 1 | | w5 | c1 | 0 | | w5 | c3 | 1 | | w5 | c4 | 0 | | w6 | c2 | 0 | | w6 | c3 | 1 | | w7 | c1 | 0 | | w7 | c2 | 1 | | w7 | c4 | 0 | +--------+--------+------+ I got this assessment schedule (which is as even as I can get it) c1 c2 c3 c4 c5 w1 M A w2 A M w3 A M w4 A M w5 M A w6 A M w7 A M The code $sql = "SELECT worker , client , main FROM worker_client"; $worker = []; // worker assessments $client = []; // client assessed by $res = $db->query($sql); while (list($w,$c,$m) = $res->fetch_row()) { $client[$c][$w] = 0; if ($m) { // allocate assessments of main clients first $worker[$w][] = $c; $client[$c][$w] = 1; } } // while still workers with < 2 assessments while (array_filter($worker, 'assess1')) { // sort to get clients with least assessments first uasort($client, function($a,$b){ return array_sum($a) - array_sum($b);}); foreach ($client as $c => $warr) { asort($warr); foreach ($warr as $w => $k) { if ($k==0) { // if worker has < 2 assessments, add client to their list if (count($worker[$w])<2) { $worker[$w][] = $c; $client[$c][$w]++; break; } } } } } echo '<pre>',print_r($worker, true),'</pre>'; // assessment schedule function assess1($var) // array filter function { return count($var) < 2; }

What attribute of an evaluation has to be proportional? To what should it be proportional? Perhaps the number of words in each evaluation should be proportional to the client's weight? Give us a clue.

Just in case anyone is tempted to use this "shuffle and pray" shortcut approach to this problem I checked out its validity. I took arrays of N drivers and shuffled them N! times, storing each combination. I then checked to see how many were unique. These are the results ACB BAC BAC BAC CBA CBA 3 drivers: 3 duplicate combinations out of 6 (50%) ABCD ACDB ADBC ADBC ADCB BADC BCAD BCDA BCDA BDCA CABD CABD CBAD CBDA DABC DACB DACB DACB DBAC DBCA DBCA DCAB DCAB DCBA 4 drivers: 7 duplicate combinations out of 24 (29%) ABDCE ABDEC ABECD ABECD ABEDC ABEDC ACBDE ACDBE ACDBE ACDEB ADBEC ADEBC ADECB AEBDC AECBD AECDB AEDCB BACED BADCE BADEC BADEC BCAED BCDEA BCDEA BCEAD BDACE BDACE BDAEC BDAEC BDCAE BDCAE BDEAC BDECA BEDAC BEDAC BEDCA CABDE CABED CABED CAEDB CAEDB CAEDB CBAED CBDAE CBEAD CBEDA CBEDA CDABE CDAEB CDBAE CDBEA CDEAB CDEBA CDEBA CDEBA CDEBA CDEBA CEABD CEADB CEADB CEADB CEBDA CEDAB CEDAB CEDAB CEDBA DABCE DABCE DABCE DAEBC DBAEC DBCAE DBEAC DCABE DCBAE DCBEA DCBEA DCBEA DCEAB DCEBA DCEBA DEACB DEACB DEBAC DEBAC DEBCA DECAB DECAB DECBA DECBA DECBA EADBC EADBC EADBC EADBC EBACD EBACD EBACD EBACD EBCDA EBDCA EBDCA ECABD ECBAD ECBAD ECBDA ECBDA ECDAB ECDBA ECDBA EDABC EDABC EDACB EDACB EDACB EDBCA EDBCA EDCAB EDCBA EDCBA 5 drivers: 46 duplicate combinations out of 120 (38%) 6 drivers: 265 duplicate combinations out of 720 (36%) 7 drivers: 1841 duplicate combinations out of 5040 (36%) 8 drivers: 14761 duplicate combinations out of 40320 (36%) 9 drivers: 327861 duplicate combinations out of 362880 (90%) Verdict: Not fit for purpose. The code used to test: <?php $drivers = ['A','B']; $extra = ['C','D','E','F','G','H','I']; $factorials = [ 3 => 6, 4 => 24, 5 => 120, 6 => 720, 7 => 5040, 8 => 40320, 9 => 362880 ]; foreach ($extra as $e) { $drivers[] = $e; // add to drivers echo countUnique($drivers, $factorials); // test with N drivers } function countUnique($d, $facts) { $combos = []; $k = count($d); $f = $facts[$k]; for ($i=0; $i<$f; $i++) { shuffle($d); // shuffles n! times $str = join('',$d); // and stores each $combos[] = $str; // random combination } // output the smaller shuffled combo arrays to view duplicates if ($k < 6) { sort($combos); $chunks = array_chunk($combos, 12); foreach($chunks as $coms) echo join(' ', $coms)."<br>"; echo '<br>'; } $n = count(array_unique($combos)); // count how many were unique $n1 = $f - $n; // number of duplicate values $n2 = count($combos); // to check that n! were generated return sprintf('%d drivers: %6d duplicate combinations out of %6d (%d%%)<br><br>', count($d), $n1, $n2, $n1*100/$n2 ); } ?>

@Birdmansplace (if you are still here after that short diversion) I have shown you how to get the combinations for my envisaged scenario using a simple cross join in the SQL. To use the scenario envisaged by grissom then the method is more complex. As it requires a recursive solution then read the data into arrays rather than have queries called recursively. The data (I restricted to 4 of each for brevity of output) $cars = [ 'Audi', 'BMW', 'Chrysler', 'Daewoo' ]; $drivers = [ 'Adam', 'Ben', 'Cathy', 'Diana' ] The code $k = count($cars); $thead = "<tr><th rowspan='2'>Round</th><th colspan='$k'>Cars</th></tr><tr><th>" . join('</th><th>', $cars) . "</th></tr>\n"; $tbody = ''; $results = []; combos($drivers, $results, []); // call the recursive function to get combinations foreach ($results as $k => $names) { $tbody .= "<tr><td>".(++$k)."</td><td>" . join('</td><td>', $names) . "</td></tr>\n"; } function combos($set, &$results, $tmp) { $k = count($set); if ($k==1) { $results[] = array_merge($tmp, $set); } else { // takes each one in turn and call with remaining set members foreach ($set as $k => $x) { $copy = $set; unset($copy[$k]); combos($copy, $results, array_merge($tmp, [$x])); } } } Output

Keeping it simple, for the sake of my limited maths knowledge, let's say we have 3 cars (A,B,C) and 3 drivers (1,2,3). The combinations are then A1 B1 C1 A2 B2 C2 A3 B3 C3 which I count to be 9 (ie 3 x 3); Using the Grissom method the count would be 3! (ie 6), so which three would be left out?

Now if the car were a mini-bus with 10 seats and you wanted to know how many different ways you could seat the ten passengers, then I'd agree with you. But that's another problem.

@grissom, my apologies for the image.

(Each of the 10 cars can have each of the 10 drivers. 10 x 10 = 100)

If you have 10 cars and 10 drivers there are 100 different combinations, not 10. Table : car (car_id | carname) Table : driver (driver_id | drivername) To get all combinations SELECT carname, drivername FROM car CROSS JOIN driver

Corrected version $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = $sires = $dams = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm]; } function getAncestors($id, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id] = $dist; if (isset($dogs[$id]) ) { getAncestors($dogs[$id][0], $dogs, $ancests, $dist+1); getAncestors($dogs[$id][1], $dogs, $ancests, $dist+1); } } $dogid = 1; getAncestors($dogs[$dogid][0], $dogs, $sires, 1); getAncestors($dogs[$dogid][1], $dogs, $dams, 1); ksort($sires); ksort($dams); $common = array_intersect_key($sires,$dams); echo "<pre>"; echo "| ID | NAME | SIRE | DAM |\n"; echo "| | | DIST | DIST |\n"; echo "|-----|--------------------|------|------|\n"; foreach ($common as $id => $dist) { printf("|%4d | %-18s | %4d | %4d |\n", $id, $dogs[$id][2], $sires[$id], $dams[$id]); } | ID | NAME | SIRE | DAM | | | | DIST | DIST | |-----|--------------------|------|------| | 8 | dog I | 3 | 2 | | 16 | dog Q | 4 | 3 | | 17 | dog R | 4 | 3 |

PS - apologies for the bisexual dog in the data, but you get the idea

A couple of years back I was working on dog pedigree charts so still had some test data. The chart of the data is attached and, as you can see, there are common ancestors. To find them you need to use a recursive function so I would load your data into an array and use that for the recursive search rather bombard your server with dozens of queries. This code will give the common ancestors and the generational distance on the sire and dam sides. You would then need to calc the IC value for each of these ancestors. $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = $sires = $dams = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm]; } function getAncestors($id, $key, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id] = $dist; if (isset($dogs[$id]) ) { getAncestors($dogs[$id][$key], 0, $dogs, $ancests, $dist+1); getAncestors($dogs[$id][$key], 1, $dogs, $ancests, $dist+1); } } $dogid = 1; getAncestors($dogs[$dogid][0], 0, $dogs, $sires, 0); getAncestors($dogs[$dogid][1], 1, $dogs, $dams, 0); ksort($sires); ksort($dams); $common = array_intersect_key($sires,$dams); echo "<pre>"; echo "| ID | NAME | SIRE | DAM |\n"; echo "| | | DIST | DIST |\n"; echo "|-----|--------------------|------|------|\n"; foreach ($common as $id => $dist) { printf("|%4d | %-18s | %4d | %4d |\n", $id, $dogs[$id][2], $sires[$id], $dams[$id]); } Outputs | ID | NAME | SIRE | DAM | | | | DIST | DIST | |-----|--------------------|------|------| | 8 | dog I | 2 | 1 | | 16 | dog Q | 3 | 2 | | 17 | dog R | 3 | 2 |

Here is an example using the bookingscalendar table <?php include("db_inc.php"); $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); $sql = "SELECT bookingdate , trainer , start_time , end_time , customer_id FROM bookingscalendar ORDER BY bookingdate,start_time"; $data = array(); $res = $db->query($sql); while ($row = $res->fetch_assoc()) { $data[$row['bookingdate']][] = array_slice($row,1,4,true); } $json = json_encode($data); // // write to file // file_put_contents('mybookings.json', $json); // // show results (for demo purposes only) // echo '<pre>',print_r($data, true),'</pre>'; echo $json; /**** RESULTS *************************************************************** The array: Array ( [2014-08-18] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 10:00:00 [end_time] => 11:00:00 [customer_id] => 101 ) [1] => Array ( [trainer] => 1 [start_time] => 13:00:00 [end_time] => 14:30:00 [customer_id] => 102 ) [2] => Array ( [trainer] => 1 [start_time] => 16:00:00 [end_time] => 17:30:00 [customer_id] => 103 ) ) [2014-08-19] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 09:00:00 [end_time] => 10:30:00 [customer_id] => 106 ) ) [2014-08-20] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 09:00:00 [end_time] => 11:00:00 [customer_id] => 101 ) [1] => Array ( [trainer] => 1 [start_time] => 12:00:00 [end_time] => 13:00:00 [customer_id] => 105 ) [2] => Array ( [trainer] => 1 [start_time] => 15:00:00 [end_time] => 17:00:00 [customer_id] => 106 ) ) [2014-08-21] => Array ( [0] => Array ( [trainer] => 1 [start_time] => 10:00:00 [end_time] => 11:00:00 [customer_id] => 102 ) ) ) The JSON-encoded array data: {"2014-08-18":[{"trainer":"1","start_time":"10:00:00","end_time":"11:00:00","customer_id":"101"}, {"trainer":"1","start_time":"13:00:00","end_time":"14:30:00","customer_id":"102"}, {"trainer":"1","start_time":"16:00:00","end_time":"17:30:00","customer_id":"103"}], "2014-08-19":[{"trainer":"1","start_time":"09:00:00","end_time":"10:30:00","customer_id":"106"}], "2014-08-20":[{"trainer":"1","start_time":"09:00:00","end_time":"11:00:00","customer_id":"101"}, {"trainer":"1","start_time":"12:00:00","end_time":"13:00:00","customer_id":"105"}, {"trainer":"1","start_time":"15:00:00","end_time":"17:00:00","customer_id":"106"}], "2014-08-21":[{"trainer":"1","start_time":"10:00:00","end_time":"11:00:00","customer_id":"102"}]} *****************************************************************************/ ?>

When JOINing table it is not uncommon for the same column name to be used in both tables. In my tables (above) the "trainer" column is used in bookingscalendar and bookingavailabilty tables. When referencing trainer, therefore, it is necessary to define which one otherwise you will get an "ambiguous column name" error. This is done by prefixing the column name with the table name SELECT bookingavailability.trainer, bookingavailability.day ... FROM .... Also, if you wanted to reference another database in your query then you would need to specify the database name too SELECT myotherdatabasename.tablename.columnname , ... FROM ... The writing and reading of the query is improved if you use table aliases Thus SELECT bookingavailability.trainer, bookingavailability.day, myotherdatabasename.tablename.columnname FROM bookingavailability INNER JOIN myotherdatabasename.tablename ON bookingavailability.trainer = myotherdatabasename.tablename.trainer WHERE myotherdatabasename.tablename.trainer > 1 ORDER BY myotherdatabasename.tablename.trainer becomes this if you use table aliases SELECT a.trainer, a.day, t.columnname FROM bookingavailability a INNER JOIN myotherdatabasename.tablename t ON a.trainer = t.trainer WHERE t.trainer > 1 ORDER BY t.trainer http://en.wikipedia.org/wiki/Alias_%28SQL%29

First, my apologies - it looks as though I accidentally clipped a couple of the lines of code when I copied from the command line screen. Here is the correct query SELECT trainer, day, bookingdate, from_time, to_time, timeslot FROM ( SELECT a.trainer , a.day , bookingdate , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time , start_time as to_time , @prevend := end_time as prevend , @prevdate := bookingdate as prevdate FROM bookingavailability a JOIN (SELECT @prevend:=null,@prevdate:=null) as init INNER JOIN bookingscalendar c ON a.trainer = c.trainer AND WEEKDAY(c.bookingdate) = a.day UNION SELECT a.trainer , day , bookingdate , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot , IFNULL(MAX(end_time),open_time) as from_time , close_time as to_time , null as prevend , null as prevdate FROM bookingavailability a LEFT JOIN bookingscalendar c ON a.trainer = c.trainer AND WEEKDAY(c.bookingdate) = a.day GROUP BY a.trainer,day,bookingdate ) as gaps WHERE timeslot > '00:00:00' ORDER BY trainer, day, bookingdate, from_time; @prevdate and @prevend are user variables that used to store values from each row in the resultset so they can be used in the following row. eg in row 1 store the end_time of the booking in @prevend. (line 10) In row2 the available gap between @prevend and the start_time of this booking is calculated (line 7) However, if the second record is not the same date as the first then the available gap is the time between the start of day (open_time) and the start_time of the booking. Therefore the date of each record is stored in @prevdate (line 11). , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time Similarly in the second part (line 23), if there are no bookings for the day the available time at the end of the day is close_time minus open_time instead of the end_time of last booking minus close_time , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot

You can do it with a minimal resource availability table but the difficulty lies with finding the times that are not there (ie the gaps between the bookings) So if you have a bookings table mysql> SELECT * FROM bookingscalendar -> ORDER BY bookingdate, start_time; +----+-------------+---------+------------+----------+-------------+ | id | bookingdate | trainer | start_time | end_time | customer_id | +----+-------------+---------+------------+----------+-------------+ | 1 | 2014-08-18 | 1 | 10:00:00 | 11:00:00 | 101 | | 2 | 2014-08-18 | 1 | 13:00:00 | 14:30:00 | 102 | | 3 | 2014-08-18 | 1 | 16:00:00 | 17:30:00 | 103 | | 8 | 2014-08-19 | 1 | 09:00:00 | 10:30:00 | 106 | | 4 | 2014-08-20 | 1 | 09:00:00 | 11:00:00 | 101 | | 5 | 2014-08-20 | 1 | 12:00:00 | 13:00:00 | 105 | | 6 | 2014-08-20 | 1 | 15:00:00 | 17:00:00 | 106 | | 7 | 2014-08-21 | 1 | 10:00:00 | 11:00:00 | 102 | +----+-------------+---------+------------+----------+-------------+ and a table for the trainer availability by day of the week (0 = Monday) mysql> SELECT * FROM bookingavailability; +-----------------+---------+------+-----------+------------+ | availability_id | trainer | day | open_time | close_time | +-----------------+---------+------+-----------+------------+ | 3 | 1 | 0 | 09:00:00 | 20:00:00 | | 4 | 1 | 1 | 09:00:00 | 17:00:00 | | 5 | 1 | 2 | 09:00:00 | 17:00:00 | | 6 | 1 | 3 | 08:00:00 | 13:00:00 | +-----------------+---------+------+-----------+------------+ you can now find those gaps. The first part of the query finds the time difference between a booking start_time and the end_time of the previous booking (or since the start of day if no previous booking that day). The second part of the query finds any gaps between the end of the last booking and that day's closing time. mysql> SELECT trainer, day, bookingdate, from_time, to_time, timeslo -> FROM -> ( -> SELECT a.trainer -> , a.day -> , bookingdate -> , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend, as timeslot -> , IF(bookingdate=@prevdate,@prevend,open_time ) as from_t -> , start_time as to_time -> , @prevend := end_time as prevend -> , @prevdate := bookingdate as prevdate -> FROM bookingavailability a -> JOIN (SELECT @prevend:=null,@prevdate:=null) as init -> INNER JOIN bookingscalendar c -> ON a.trainer = c.trainer -> AND WEEKDAY(c.bookingdate) = a.day -> -> UNION -> -> SELECT a.trainer -> , day -> , bookingdate -> , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) -> , IFNULL(MAX(end_time),open_time) as from_time -> , close_time as to_time -> , null as prevend -> , null as prevdate -> FROM bookingavailability a -> LEFT JOIN bookingscalendar c -> ON a.trainer = c.trainer -> AND WEEKDAY(c.bookingdate) = a.day -> GROUP BY a.trainer,day,bookingdate -> ) as gaps -> WHERE timeslot > '00:00:00' -> ORDER BY trainer, day, bookingdate, from_time; +---------+------+-------------+-----------+----------+----------+ | trainer | day | bookingdate | from_time | to_time | timeslot | +---------+------+-------------+-----------+----------+----------+ | 1 | 0 | 2014-08-18 | 09:00:00 | 10:00:00 | 01:00:00 | | 1 | 0 | 2014-08-18 | 11:00:00 | 13:00:00 | 02:00:00 | | 1 | 0 | 2014-08-18 | 14:30:00 | 16:00:00 | 01:30:00 | | 1 | 0 | 2014-08-18 | 17:30:00 | 20:00:00 | 02:30:00 | | 1 | 1 | 2014-08-19 | 10:30:00 | 17:00:00 | 06:30:00 | | 1 | 2 | 2014-08-20 | 11:00:00 | 12:00:00 | 01:00:00 | | 1 | 2 | 2014-08-20 | 13:00:00 | 15:00:00 | 02:00:00 | | 1 | 3 | 2014-08-21 | 08:00:00 | 10:00:00 | 02:00:00 | | 1 | 3 | 2014-08-21 | 11:00:00 | 13:00:00 | 02:00:00 | +---------+------+-------------+-----------+----------+----------+

Also Use a while loop and not do..while() otherwise the first record isn't read until the end of the first iteration. Use mysql_real_escape_string() instead of addslashes() (Better still, use mysqli or PDO)

MySQL Admin is fine for editing records. Open a connection under "SQL Development" Right click a table name and select "Edit table data"

it should now look something like this $sql = "SELECT DISTINCT date FROM attendance ORDER BY DATE"; $res = $db->query($sql); // mysqli query while ($row = $res->fetch_row()) { $dates[] = $row[0]; } /*********************************** * Table headings * ************************************/ $emptyRow = array_fill_keys($dates,''); $emptyRow['P'] = 0; $emptyRow['A'] = 0; // format dates foreach ($dates as $k=>$v) { $dates[$k] = date('d-M', strtotime($v)); } $heads = "<table border='1'>\n"; $heads .= "<tr><th>Name</th><th>" . join('</th><th>', $dates) . "</th><th>Present</th><th>Absent</th></tr>\n"; /*********************************** * Main data * ************************************/ $sql = "SELECT date, staffname, status FROM attendance ORDER BY staffname"; $res = $db->query($sql); $curname=''; $tdata = ''; while (list($d, $sn, $s) = $res->fetch_row()) { if ($curname != $sn) { if ($curname) { $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; } $rowdata = $emptyRow; $curname = $sn; } $rowdata[$d] = $s; $rowdata[$s]++; } $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; $tdata .= "</table\n"; ?> <html> <head> <style type="text/css"> td,th { text-align: center; padding: 5px; } table { border-collapse:collapse; } </style> </head> <body> <?php echo $heads; echo $tdata; ?> </body> </html>

I expected you would remove the lines you added last time since you no longer want the totals by date, despite your earlier post:

You need to add keys "P" and "A" to the $emptyRow array and increment those. $rowdata[$s]++; You'll also need to add the two headings to the table headings row.

Added a few lines <?php $sql = "SELECT DISTINCT date FROM attendance ORDER BY DATE"; $res = $db->query($sql); // mysqli query while ($row = $res->fetch_row()) { $dates[] = $row[0]; } /*********************************** * Table headings * ************************************/ $emptyRow = array_fill_keys($dates,''); // create arrays for "absent" and "present" $present = $absent = array_fill_keys($dates, 0); // ADD LINE // format dates foreach ($dates as $k=>$v) { $dates[$k] = date('d-M', strtotime($v)); } $heads = "<table border='1'>\n"; $heads .= "<tr><th>Name</th><th>" . join('</th><th>', $dates) . "</th></tr>\n"; /*********************************** * Main data * ************************************/ $sql = "SELECT date, staffname, status FROM attendance ORDER BY staffname"; $res = $db->query($sql); $curname=''; $tdata = ''; while (list($d, $sn, $s) = $res->fetch_row()) { if ($curname != $sn) { if ($curname) { $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; } $rowdata = $emptyRow; $curname = $sn; } $rowdata[$d] = $s; switch ($s) { // ADD THIS SWITCH STATEMENT case 'P' : $present[$d]++; break; default: $absent[$d]++; } } $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; // present and absent totals // ADD THESE LINES $tdata .= "<tr><td>PRESENT</td><td>" . join('</td><td>', $present). "</td></tr>\n"; $tdata .= "<tr><td>ABSENT</td><td>" . join('</td><td>', $absent). "</td></tr>\n"; $tdata .= "</table\n"; ?>

Do you mean absent and present on each date?

Here's my method for reports like that $sql = "SELECT DISTINCT date FROM attendance ORDER BY DATE"; $res = $db->query($sql); // mysqli query while ($row = $res->fetch_row()) { $dates[] = $row[0]; } /*********************************** * Table headings * ************************************/ $emptyRow = array_fill_keys($dates,''); // format dates foreach ($dates as $k=>$v) { $dates[$k] = date('d-M', strtotime($v)); } $heads = "<table border='1'>\n"; $heads .= "<tr><th>Name</th><th>" . join('</th><th>', $dates) . "</th></tr>\n"; /*********************************** * Main data * ************************************/ $sql = "SELECT date, staffname, status FROM attendance ORDER BY staffname"; $res = $db->query($sql); $curname=''; $tdata = ''; while (list($d, $sn, $s) = $res->fetch_row()) { if ($curname != $sn) { if ($curname) { $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; } $rowdata = $emptyRow; $curname = $sn; } $rowdata[$d] = $s; } $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $rowdata). "</td></tr>\n"; $tdata .= "</table\n"; ?> <html> <head> <style type="text/css"> td { text-align: center; } table { border-collapse:collapse; } </style> </head> <body> <?php echo $heads; echo $tdata; ?> </body> </html> Your dates (dd-mm-yyyy) are unusable in a database. Store as type DATE format YYYY-MM-DD so they can be correctly sorted or compared.