Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,604

  • Joined

  • Last visited

  • Days Won

    830

Everything posted by Barand

  1. Barand
    Slightly simpler this way (by resource headings) CODE <?php require 'db_inc.php'; // $pdo = mdbConnect('db1'); // USER YOUR OWN CONNECTION CODE $wkcomm = $_GET['wkcomm'] ?? date('Y-m-d'); ################################################################################ ## COLUMN HEADINGS ################################################################################ $res = $pdo->query("SELECT id, description FROM resource ORDER BY id "); $theads = "<tr class='w3-dark-gray'><th>Time Slot</th>"; foreach ($res as $r) { $theads .= "<th>{$r['description']}</th>"; } $theads .= "</tr>\n"; ################################################################################ ## QUERY BOOKINGS AND BUILD ARRAY IN REQUIRED STRUCTURE FOR OUTPUT ################################################################################ $res = $pdo->prepare(" -- -- create temporary table containing the daily 30-minute timeslots -- WITH RECURSIVE timeslot (n, starttime, endtime, date) AS ( SELECT 1, '07:00:00', '07:30:00', ? UNION ALL SELECT n+1 , addtime(starttime, '00:30:00') , addtime(endtime, '00:30:00') , date FROM timeslot WHERE n < 24 ) SELECT r.id as resid , time_format(starttime, '%H:%i') as time , b.id -- -- cross join the resource table with temp timeslot table to give rows for every timeslot for each resource -- then match these against the bookings to see which slots fall withing the booking range -- for the matching resource -- FROM resource r CROSS JOIN timeslot t LEFT JOIN booking b ON CONCAT(t.date, ' ', t.starttime) < b.book_end AND CONCAT(t.date, ' ', t.endtime) > b.book_start AND r.id = b.resource_id ORDER BY starttime, resid "); $res->execute([ $wkcomm ]); $data = []; foreach ($res as $r) { $data[$r['time']][$r['resid']] = $r['id']; } ################################################################################ ## LOOP THROUGH ARRAY TO CREATE HTML ROWS ################################################################################ $tdata = ''; foreach ($data as $tslot => $slotdata) { $tdata .= "<tr><td class='w3-gray w3-text-white'>$tslot</td>"; foreach ($slotdata as $booked) { $class = $booked ? 'w3-red' : ''; $tdata .= "<td class='$class'>&nbsp;</td>"; } $tdata .= "</tr>\n"; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset="utf-8"> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> table { border-collapse: collapse; width: 100%; } th, td { padding: 4px; } th { border: 1px solid white; } </style> </head> <body> <header class='w3-indigo w3-padding'> <h1>Resource Bookings</h1> </header> <form class='w3-light-gray w3-padding w3-margin-bottom'> Date: <input type='date' name='wkcomm' value='<?=$wkcomm?>'> &emsp; <button class='w3-blue w3-button'>Refresh</button> </form> <div class='w3-content'> <table class='w3-small' border='1'> <?=$theads?> <?=$tdata?> </table> </div> </body> </html>
  2. Barand
    Try it and see.
  3. Barand
    I suggest output like this below. I have used yiur two resources and ny booking data looke like this... mysql> select * from booking; +----+-------------+---------------------+---------------------+ | id | resource_id | book_start | book_end | +----+-------------+---------------------+---------------------+ | 1 | 1 | 2024-05-13 09:00:00 | 2024-05-13 12:00:00 | | 2 | 1 | 2024-05-15 09:00:00 | 2024-05-15 19:00:00 | | 4 | 1 | 2024-05-16 12:00:00 | 2024-05-16 18:00:00 | | 5 | 2 | 2024-05-13 12:00:00 | 2024-05-17 18:00:00 | +----+-------------+---------------------+---------------------+ The output looks lke this for the 2 resources... CODE <?php require 'db_inc.php'; // $pdo = mdbConnect('db1'); // USE YOUR OWN CONNECTION CODE $wkcomm = $_GET['wkcomm'] ?? date('Y-m-d'); $resid = $_GET['resid'] ?? ''; ################################################################################ ## COLUMN HEADINGS ################################################################################ $d1 = new DateTime($wkcomm); $di = new DateInterval('P1D'); $dp = new DatePeriod($d1, $di, 6); $theads = "<tr class='w3-dark-gray'><th>Time Slot</th>"; foreach ($dp as $d) { $theads .= "<th>" . $d->format('D M d') . "</th>"; } $theads .= "</tr>\n"; ################################################################################ ## QUERY BOOKINGS AND BUILD ARRAY IN REQUIRED STRUCTURE FOR OUTPUT ################################################################################ $res = $pdo->prepare("-- -- create temporary table containing the dates of the selected week -- WITH RECURSIVE dateslot (n, slotdate) AS ( SELECT 1, ? UNION ALL SELECT n+1 , date_add(slotdate, INTERVAL 1 DAY) FROM dateslot WHERE n < 7 ), -- -- create temporary table containing the daily 30-minute timeslots -- timeslot (n, starttime, endtime) AS ( SELECT 1, '07:00:00', '07:30:00' UNION ALL SELECT n+1 , addtime(starttime, '00:30:00') , addtime(endtime, '00:30:00') FROM timeslot WHERE n < 24 ) SELECT slotdate , time_format(starttime, '%H:%i') as time , b.id -- -- cross join the two temporary tables to give rows for every timeslot for the seven days -- then match these against the bookings to see which slots fall withing the booking range -- for the selected resource -- FROM dateslot d CROSS JOIN timeslot t LEFT JOIN booking b ON CONCAT(d.slotdate, ' ', t.starttime) < b.book_end AND CONCAT(d.slotdate, ' ', t.endtime) > b.book_start AND b.resource_id = ? ORDER BY starttime, slotdate "); $res->execute([ $wkcomm, $resid ]); $data = []; foreach ($res as $r) { $data[$r['time']][$r['slotdate']] = $r['id']; } ################################################################################ ## LOOP THROUGH ARRAY TO CREATE HTML ROWS ################################################################################ $tdata = ''; foreach ($data as $tslot => $slotdata) { $tdata .= "<tr><td class='w3-gray w3-text-white'>$tslot</td>"; foreach ($slotdata as $booked) { $class = $booked ? 'w3-red' : ''; $tdata .= "<td class='$class'>&nbsp;</td>"; } $tdata .= "</tr>\n"; } ################################################################################ ## FUNCTIONS ################################################################################ function resourceOptions($pdo, $current) { $res = $pdo->query("SELECT id, description FROM resource ORDER BY description "); $opts = "<option value=''>- select resource -</option>\n"; foreach ($res as $r) { $sel = $r['id']==$current ? 'selected' : ''; $opts .= "<option $sel value='{$r['id']}'>{$r['description']}</option>\n"; } return $opts; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset="utf-8"> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> table { border-collapse: collapse; width: 100%; } th, td { padding: 4px; } th { border: 1px solid white; } </style> </head> <body> <header class='w3-indigo w3-padding'> <h1>Resource Bookings</h1> </header> <form class='w3-light-gray w3-padding w3-margin-bottom'> Week commencing: <input type='date' name='wkcomm' value='<?=$wkcomm?>'> &emsp; Resource: <select name='resid'> <?=resourceOptions($pdo, $resid)?> </select> <button class='w3-blue w3-button'>Refresh</button> </form> <div class='w3-content'> <table class='w3-small' border='1'> <?=$theads?> <?=$tdata?> </table> </div> </body> </html>
  4. Barand
    When posting code, use the <> button to create a code block
  5. Barand
    There's a beginners' SQL tutorial in my forum signature. It may help.
  6. Barand
    @phppup, Given that dog's breakfast of a table that you are storing as a spreadsheet in your data base, you can do it all with SQL but you need a different approach to that above. (Using the same votes table as above) SELECT opt as `Option` , SUM(vote) as Votes FROM ( SELECT 'Balloons' as opt , balloons as vote FROM votes UNION ALL SELECT 'Soda' as opt , soda as vote FROM votes UNION ALL SELECT 'Ribbons' as opt , ribbons as vote FROM votes UNION ALL SELECT 'Bows' as opt , bows as vote FROM votes ) data GROUP BY opt ORDER BY Votes DESC; +-------------+-------------+ | option | votes | +-------------+-------------+ | Balloons | 4 | | Ribbons | 2 | | Bows | 2 | | Soda | 1 | +-------------+-------------+ However, the correct way to do it to store normalized data so that each option that is voted for is stored in its own record. Here's a simple data model and query... TABLE : poll TABLE: poll_option TABLE: vote +---------+-----------+ +--------+----------+-------------+ +-----+----------+-----------+ | id | name | | id | poll_id | opt | | id | user_id | option_id | +---------+-----------+ +--------+----------+-------------+ +-----+----------+-----------+ | 1 | Pets | | 1 | 1 | Cat | | 1 | 1 | 4 | | 2 | Parties | | 2 | 1 | Dog | | 2 | 1 | 6 | +---------+-----------+ | 3 | 1 | Iguana | | 3 | 2 | 4 | | 4 | 2 | Balloons | | 4 | 2 | 6 | | 5 | 2 | Soda | | 5 | 2 | 7 | | 6 | 2 | Ribbons | | 6 | 3 | 4 | | 7 | 2 | Bows | | 7 | 3 | 5 | +--------+----------+-------------+ | 8 | 4 | 4 | | 9 | 4 | 7 | +-----+----------+-----------+ SELECT o.opt as `option` , count(v.option_id) as votes FROM poll_option o JOIN vote v ON o.id = v.option_id WHERE o.poll_id = 2 GROUP BY o.opt ORDER BY votes DESC +-------------+-------------+ | option | votes | +-------------+-------------+ | Balloons | 4 | | Ribbons | 2 | | Bows | 2 | | Soda | 1 | +-------------+-------------+
  7. Barand
    @Danishhafeez, mysql> select * from votes; +----+----------+------+---------+------+ | id | balloons | soda | ribbons | bows | +----+----------+------+---------+------+ | 1 | 1 | NULL | 1 | NULL | | 2 | 1 | NULL | 1 | 1 | | 3 | 1 | 1 | NULL | NULL | | 4 | 1 | NULL | NULL | 1 | +----+----------+------+---------+------+ 4 rows in set (0.00 sec) mysql> SELECT -> SUM(balloons) AS balloons_total, -> SUM(soda) AS soda_total, -> SUM(ribbons) AS ribbons_total, -> SUM(bows) AS bows_total -> FROM -> votes -> GROUP BY -> balloons, soda, ribbons, bows -> ORDER BY -> balloons_total DESC, soda_total DESC, ribbons_total DESC, bows_total DESC; +----------------+------------+---------------+------------+ | balloons_total | soda_total | ribbons_total | bows_total | +----------------+------------+---------------+------------+ | 1 | 1 | NULL | NULL | | 1 | NULL | 1 | 1 | | 1 | NULL | 1 | NULL | | 1 | NULL | NULL | 1 | +----------------+------------+---------------+------------+ 4 rows in set (0.00 sec)
  8. Barand
    I think you will find that line no 21374 in the "spare" csv has only 73 values instead of the expected 75.
  9. Barand
    perhaps you should show the while loop and your query which gets $result->id too.
  10. Barand
    Strange a function that is defined twice should be flagged as "undefined". You have a surfeit of quotes in this line... echo '<select name="avatar" id="Avatar" onChange="changeAvi()"">'; ^
  11. Barand
    Working with what you've unfortunately got now, when you read through the query results store each record in an array which has a subarray for month values. Covert the start and dates to index values for this array (0 if the start date is earlier than Jan and 11 if the end date is after Dec. Just use that portion of the array to calculate totals and averages.
  12. Barand
    Now you have all the data you need to do what you wanted to do. For each mark record,j ust include the values for those months that lie within the data range and job done.
  13. Barand
    Finally...
  14. Barand
    How do you know that he started in July? How do you know that Kuldeep Singh (#230) didn't leave at the end August and his average shouldn't be calculated over 8 months? In other words, what is the rule for when to include blank values in the averages and when to ignore them? [edit] BTW, there is no "branch" column the "mark" table
  15. Barand
    Stop storing spreadsheets in your database. Come back when you have normalized data.
  16. Barand
    If you work in base 9 rather than base 10 1 + 5 + 7 + 6 = 21
  17. Barand
    Does this count? 7 * 6 * 1 * .5
  18. Barand
    22 is easy (5 - 1) * 7 - 6 but wouldn't an id card that had an age entry like that look just a lttle suspicious? eg Age : (5 - 1) * 7 - 6 I'll keep trying for 21.
  19. Barand
    'til now they have been mathematical so here's one that's purely grammatical Q4 Correctly punctuate this sentence so that it makes sense the two boys had written very similar essays but peter where paul had had had had had had had had had had been preferred by the teacher
  20. Barand
    Welll done. I knew you'd do it. Hint for others:
  21. Barand
    Your foreach() loop expects several items and that each will be put into $row as you loop through. But get(cart_item) only returns a single item so when you use foreach() you are looping through its properties Try $row = Session::get("cart_item"); echo <<<ITEM <tr> <td width="70"><center>{$row['ItemCode']}</center></td> <td>{$row['ItemDesc']}</td> <td width="70"><center>{$row['ItemQty']}</center></td> <td width="70"><center>{$row['ItemAmount']}</center></td> <td width="70"><center>{$row['ItemTotalAmount']}</center></td> </tr> ITEM;
  22. Barand
    Are you saying that if you foreach(Session::get("cart_item") as $row) { print_r($row); } then you get... for each item?
  23. Barand
    That wasn't thinking outside the box, that was a cop-out. The woodchuck was just slower than the others collecting his twigs. There is a (none-zero) solution.
  24. Barand
    For anyone else out there, feel free to join in. Meanwhile... Q3 Some animals are gathered in the forest having a midwinter party, all seated around a fire. When the fire starts to die down they all move off to gather more wood. The walrus manages to gather 7 twigs and throw them on the fire, the widgeon throws on 21 twigs, the wombat 11 twigs, the weasel 20 twigs and the wolverine throws on 28 twigs. How much wood would a woodchuck chuck if a woodchuck would chuck wood?
  25. Barand
    I took the integration / volume of rotation route and came up with the solution.
×
×
  • 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.