Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,612

  • Joined

  • Last visited

  • Days Won

    834

 Content Type 

Everything posted by Barand

  1. Barand
    Are sure you really want to match on a.player_id=b.exp_total? Seems strange to match an id against a total, or just bad column naming. If that is the case, why bother to select both columns if you know they are going to be the same in the results
  2. Barand
    this code will demonstrate how to use ajax to change the three prices when a different product is selected The form <?php $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); function productOptions($db) { $sql = "SELECT id, name FROM product ORDER BY name"; $res = $db->query($sql); $opts = "<option value=''>- choose -</option>\n"; while (list($id,$name) = $res->fetch_row()) { $opts .= "<option value='$id'>$name</option>\n"; } return $opts; } ?> <html> <head> <title>Sample</title> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script type='text/javascript'> $().ready(function() { $("#product").change(function() { var pid = $(this).val(); $.get( "get_prod_prices.php" , {"pid":pid} , function(data) { $.each(data, function(k,v) { $("#price"+k).val(v); if (v) $("#label"+k).html("€"+v); else $("#label"+k).html(""); }) }, "JSON" ) }) }) </script> </head> <body> <form> <select name="product" id="product" > <?=productOptions($db)?> </select> <input type="radio" name="price" id="price1" value="0"><label for="price1" id="label1"> </label> <input type="radio" name="price" id="price2" value="0"><label for="price2" id="label2"> </label> <input type="radio" name="price" id="price3" value="0"><label for="price2" id="label3"> </label> </form> </body> </html> the script call with AJAX request (get_prod_prices.php) <?php $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); if (!isset($_GET['pid'])) { $prices = [1=>'','','']; exit(json_encode($prices)); } $pid = intval($_GET['pid']); $sql = "SELECT price1, price2, price3 FROM product WHERE id = $pid"; $res = $db->query($sql); list($p1, $p2, $p3) = $res->fetch_row(); $prices = [1=>$p1, $p2, $p3]; echo json_encode($prices); ?>
  3. Barand
    There should not be commas before "from" and before "right join" Queries are more efficient if you use explicit join syntax ie FROM A JOIN B ON ... instead of FROM A,B WHERE
  4. Barand
    If I understand correctly what you want, you need to call an AJAX request when the dropdown selection changes. Send the selected product id and get back the 3 product prices. Then set the price values of the 3 buttons from the returned data.
  5. Barand
    Congratulations. Mine's a gin'n'tonic
  6. Barand
    The first half of the UNION finds (for each trainer) free time before first booking free time between bookings all trainers time if no bookings The second part finds trainers' free time after last booking of the day. SELECT trainer , from_time , to_time , timeslot FROM ( SELECT a.day , TIMEDIFF(IFNULL(start_time,close_time), IF(a.trainer=@prevt,@prevend,open_time )) as timeslot , CAST(IF(a.trainer=@prevt,@prevend,open_time ) as TIME) as from_time , IFNULL(start_time,close_time) as to_time , @prevend := end_time as prevend , @prevt := a.trainer as trainer FROM bookingavailability a JOIN (SELECT @prevend:=null,@prevt:=null) as init LEFT JOIN bookingscalendar c ON a.trainer = c.trainer AND WEEKDAY(c.bookingdate) = a.day AND c.bookingdate = '2014-08-18' WHERE a.day = WEEKDAY('2014-08-18') ORDER BY a.trainer, c.start_time ) gaps UNION SELECT a.trainer , MAX(end_time) as from_time , a.close_time as to_time , TIMEDIFF(MAX(end_time),close_time) as timeslot FROM bookingavailability a INNER JOIN ( SELECT trainer , MAX(end_time) as end_time FROM bookingscalendar WHERE bookingdate = '2014-08-18' GROUP BY trainer ) eod WHERE a.day = WEEKDAY('2014-08-18') ORDER BY trainer,from_time;
  7. Barand
    This is the download function that I use. function sql2csv($mysqli, $sql, $filename='', $headings=1) /** * Parameters * $mysqli - connection * $sql - the sql query to be executed * $filename - name of download file (default "download_yymmddhhii.csv") * $headings - 1 if fieldname headings required (default), 0 if not required */ { if (!$filename) $f = 'download_' . date('ymdhi') . '.csv'; else $f = $filename; $fp = fopen('php://output', 'w'); // so you can fputcsv to STDOUT if ($fp) { $res = $mysqli->query($sql); if ($res) { header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="'.$f.'"'); header('Pragma: no-cache'); header('Expires: 0'); $row = $res->fetch_assoc(); if ($headings) { fputcsv($fp, array_keys($row)); } do { fputcsv($fp, $row); } while ($row = $res->fetch_assoc()); } else echo "Error in query"; fclose($fp); } } Use it in a php file that you link to with a download link.
  8. Barand
    If you do that you won't know about the clients that are double-booked to inform them and change their bookings, you just hide the problem. This query will pull any booking where a room is booked at the same time as another booking. SELECT id ,bookingdate ,room ,start_time ,end_time ,trainer ,customer_id FROM bookingscalendar WHERE id IN ( SELECT b1.id FROM bookingscalendar b1 INNER JOIN bookingscalendar b2 ON b1.bookingdate = b2.bookingdate AND b1.room = b2.room AND b1.start_time < b2.end_time AND b1.end_time > b2.start_time AND b1.id <> b2.id ) ORDER BY bookingdate,room,start_time; Ensure at the time of booking that only free rooms can be allocated.
  9. Barand
    Your availability is for trainer 2, your bookings for trainer 1. Dates will be null when no matching bookings for a trainer
  10. Barand
    I notice you have added another level of complexity with the inclusion of the "room" in the bookings. So as well as checking for trainer availability you also have to check for room availability.
  11. Barand
    These are my table definitions CREATE TABLE `bookingscalendar` ( `id` int(11) NOT NULL AUTO_INCREMENT, `bookingdate` date DEFAULT NULL, `trainer` int(11) DEFAULT NULL, `start_time` time DEFAULT NULL, `end_time` time DEFAULT NULL, `customer_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `bookingavailability` ( `availability_id` int(11) NOT NULL AUTO_INCREMENT, `trainer` int(11) DEFAULT NULL, `dayofweek` int(11) DEFAULT NULL, `open_time` time DEFAULT NULL, `close_time` time DEFAULT NULL, PRIMARY KEY (`availability_id`) )
  12. 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
  13. Barand
    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 |
  14. Barand
    PS - apologies for the bisexual dog in the data, but you get the idea
  15. Barand
    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 |
  16. Barand
    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"}]} *****************************************************************************/ ?>
  17. Barand
    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
  18. Barand
    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
  19. Barand
    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 | +---------+------+-------------+-----------+----------+----------+
  20. Barand
    MySQL Admin is fine for editing records. Open a connection under "SQL Development" Right click a table name and select "Edit table data"
  21. Barand
    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>
  22. Barand
    I expected you would remove the lines you added last time since you no longer want the totals by date, despite your earlier post:
  23. Barand
    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.
  24. Barand
    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"; ?>
  25. Barand
    Do you mean absent and present on each date?
×
×
  • 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.