Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,566

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. Barand
    Use {..} around the complex variables, e.g. echo "<li><a href='{$row['url']}' title='{$row['title']}'><i class='fas fa-user site-nav--icon'></i> Help</a></li>"; ^___________^ ^_____________^
  2. Barand
    Here's an example showing bookings/available times for 17th Nov 2020 DATA TABLE: jib_reservation TABLE: jib_booking_period +-------+--------+--------+---------------------+---------------------+--------------+ +-----------+------------+----------+ | resid | roomid | userid | start_time | end_time | participants | | period_id | start_time | end_time | +-------+--------+--------+---------------------+---------------------+--------------+ +-----------+------------+----------+ | 1 | 1 | 1 | 2020-11-17 09:00:00 | 2020-11-17 10:30:00 | 6 | | 1 | 08:00:00 | 08:30:00 | | 2 | 2 | 2 | 2020-11-17 08:30:00 | 2020-11-17 10:30:00 | 15 | | 2 | 08:30:00 | 09:00:00 | | 3 | 2 | 3 | 2020-11-17 12:00:00 | 2020-11-17 14:00:00 | 12 | | 3 | 09:00:00 | 09:30:00 | | 4 | 1 | 1 | 2020-11-17 11:00:00 | 2020-11-17 12:00:00 | 5 | | 4 | 09:30:00 | 10:00:00 | | 5 | 3 | 2 | 2020-11-17 15:00:00 | 2020-11-17 17:00:00 | 6 | | 5 | 10:00:00 | 10:30:00 | +-------+--------+--------+---------------------+---------------------+--------------+ | 6 | 10:30:00 | 11:00:00 | | 7 | 11:00:00 | 11:30:00 | | 8 | 11:30:00 | 12:00:00 | | 9 | 12:00:00 | 12:30:00 | | 10 | 12:30:00 | 13:00:00 | | 11 | 13:00:00 | 13:30:00 | | 12 | 13:30:00 | 14:00:00 | | 13 | 14:00:00 | 14:30:00 | | 14 | 14:30:00 | 15:00:00 | | 15 | 15:00:00 | 15:30:00 | | 16 | 15:30:00 | 16:00:00 | | 17 | 16:00:00 | 16:30:00 | | 18 | 16:30:00 | 17:00:00 | +-----------+------------+----------+ CODE <?php require 'db_inc.php'; $db = pdoConnect('test'); $chosen_date = '2020-11-17'; ### ### Get status of bookings on chosen date ### $res = $db->prepare("SELECT r.roomid , concat(p.start_time, ' - ', p.end_time) as slot , u.firstname , v.participants FROM jib_room r CROSS JOIN jib_booking_period p LEFT JOIN jib_reservation v ON r.roomid = v.roomid AND TIME(v.start_time) < p.end_time AND TIME(v.end_time) > p.start_time AND DATE(v.start_time) = ? LEFT JOIN jib_user u USING (userid) ORDER BY slot, roomid" ); $res->execute([ $chosen_date ]); $slots = []; // arrange results into an array foreach ($res as $r) { if (!isset($slots[$r['slot']])) { $slots[$r['slot']] = array_fill_keys(range(1,4),''); // empty room array } $slots[$r['slot']][$r['roomid']] = $r['firstname'] ? "{$r['firstname']} ({$r['participants']})" : '';; } // output array to table $bookdata = ''; foreach ($slots as $s => $rooms) { $bookdata .= "<tr><td>$s</td>"; foreach ($rooms as $user) { $cls = $user ? 'booked' : ''; $bookdata .= "<td class='$cls'>$user</td>"; } $bookdata .= "</tr>\n"; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="creation-date" content="11/16/2020"> <title>Bookings</title> <style type='text/css'> table { width: 800px; font-family: verdana, sans-serif; font-size: 11pt; border-collapse: collapse; } th { background-color: black; color: white; padding: 8px; } td { padding: 4px 8px; } td.booked { background-color: red; color: white } </style> </head> <body> <table border='1'> <tr><th>Booking slot</th><th>Room 1</th><th>Room 2</th><th>Room 3</th><th>Room 4</th></tr> <?=$bookdata?> </table> </body> </html> OUTPUT
  3. Barand
    From the tutorial I linked you to ... This is working in day slots and you are working in 30 minute slots but the principle is exactly the same
  4. Barand
    Here's an example script <?php require 'db_inc.php'; $db = pdoConnect('test'); /* MY DATA ******************************************************************************************************** TABLE: a_student +------------+----------+---------------------+ | student_id | fullname | logged | +------------+----------+---------------------+ | 1 | Peter | 2020-08-02 17:23:56 | | 2 | Paul | 2020-08-22 23:23:56 | | 3 | Mary | 2020-07-28 22:23:56 | | 4 | Cath | 2020-01-25 04:23:56 | | 5 | Dave | 2020-01-04 18:23:56 | | 6 | Eric | 2020-02-12 07:23:56 | | 7 | Fred | 2020-10-23 06:23:56 | | 8 | John | 2020-07-19 11:23:56 | | 9 | Jane | 2020-07-28 15:23:56 | | 10 | Alec | 2020-05-06 02:23:56 | | 11 | Ben | 2020-04-07 15:23:56 | +------------+----------+---------------------+ *******************************************************************************************************************/ // // Process AJAX request // if (isset($_GET['ajax']) && $_GET['ajax']=='getstudents') { $res = $db->query("SELECT student_id , fullname , logged FROM a_student; "); exit(json_encode($res->fetchAll())); } ?> <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="creation-date" content="11/16/2020"> <title>Primes</title> <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.0/jquery.min.js"></script> <script type='text/javascript'> $().ready( function() { $("button").click( function() { var dir = $(this).data("dir"); getStudents(dir) }) }) function getStudents(dir) { if (dir == 'asc') { sortfunc = function(a,b) { if ( a.logged < b.logged ) return -1 else if (a.logged > b.logged ) return 1 else return 0 } } else { sortfunc = function(a,b) { if ( b.logged < a.logged ) return -1 else if (b.logged > a.logged ) return 1 else return 0 } } $.get ( "", {"ajax":"getstudents"}, function(resp) { resp.sort(sortfunc) $("#student-list").html("") $(resp).each( function (k, v) { $("#student-list").append("<tr><td>"+v.student_id+"</td><td>"+v.fullname+"</td><td>"+v.logged+"</td></tr>") }) }, "JSON" ) } </script> <style type='text/css'> table { width: 500px; font-family: verdana, sans-serif; font-size: 11pt; } th { background-color: black; color: white; padding: 8px; } td { padding: 4px 8px; } </style> </head> <body> <button data-dir='asc'>Oldest to Newest</button> &emsp; <button data-dir='desc'>Newest to Oldest</button> <hr> <table> <tr><th>ID</th><th>Name</th><th>Date</th></tr> <tbody id='student-list'> </tbody> </table> </body> </html>
  5. Barand
    You are trying to search for conditions in records that may not exist. As a result the query behaves as though an INNER JOIN were used intead of a LEFT JOIN.
  6. Barand
    The tutorial I linked you to shows how.
  7. Barand
    I'd put dates that are bookable in another table, perhaps a temporary table Let's say you want to check next weeks booking. Put Mon to Fri dates for next week into booking_dates. With your four rooms and 18 timeslots, this query will check all 360 available timeslots for each room for each day next week and output those still available SELECT r.roomid , d.date , start_time , p.end_time FROM room r CROSS JOIN booking_dates d CROSS JOIN booking_periods p LEFT JOIN reservation res USING (roomid, date, start_time) WHERE res.roomid IS NULL ORDER BY rddate, r.room, p.start_time;
  8. Barand
    Perhaps it's something to do with...
  9. Barand
    The user privileges defined in the mysql database for whichever username you are connecting with.
  10. Barand
    If you aren't storing the file you need to load it from the tmp_file. Make sure you have the privileges required to run a LOAD DATA query.
  11. Barand
    $newData = JSON.parse(data); Try commenting out the above line - it shouldn't be necessary as you specified a data-type: json
  12. Barand
    Storing reservations only, as you have done, is the efficient way to do it. However, as you have discovered, finding those not booked is like asking "Hands up up everyone who isn't here?". Create a fourth table to define time periods (a record for 30 minute slot between 8am and 5pm) Booking_periods : period_id (PK), start_time, end_time Now you have something you can match against to find available slots. Whether you use it in a query or use it to define a PHP array is up to you. You may find this useful
  13. Barand
    WHERE's don't work correctly on table that you LEFT JOIN to. You need to put the condition in the JOIN Try SELECT [PROD SCHD].[WORK ORDER], [PROD SCHD].[ENTERPRISE], [PROD SCHD].[PART NUMBER], [PPBOMFCB].[ITEM_NUMBER_BOM], [PPBOMFCB].[CATALOGUE_NUMBER_BOM], [PPBOMFCB].[DESCRIPTION_BOM] FROM [PROD SCHD] LEFT JOIN [PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM] AND [PPBOMFCB].[CATALOGUE_NUMBER_BOM] LIKE 'FA%' WHERE [PROD SCHD].[WORK ORDER] IS NOT NULL
  14. Barand
    Not sure how to say it with any more clarity. Your definition of the function has 4 parameters, namely $fields, $data, $rows and $page When you call the function you provide only the "$fields" parameter. There are no values provided for the other three.
  15. Barand
    When you call your "display_table()" function you are passing a single array parameter ($fields). Your function also expects $data, $rows and $page parameters. What happened to those?
  16. Barand
    You say you get the error on the fetch, but that is the bit of the code you decided not to show us!
  17. Barand
    As mac_gyver suggested, have two table viz. stock_receipts, stock_issues. CREATE TABLE `stock_receipt` ( | CREATE TABLE `stock_issue` ( `batch_id` int(11) NOT NULL AUTO_INCREMENT, | `stock_issue_id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) DEFAULT NULL, | `product_id` int(11) DEFAULT NULL, `receipt_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | `issue_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `quantity` int(11) DEFAULT NULL, | `job_no` int(11) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, | `quantity` int(11) DEFAULT NULL, PRIMARY KEY (`batch_id`), | `batch_id` int(11) DEFAULT NULL, KEY `index2` (`product_id`,`receipt_date`) | PRIMARY KEY (`stock_issue_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | KEY `index2` (`product_id`, `issue_date`), | KEY `index3` (`batch_id`) | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | When a new issue is entered, get the stock records for that product which have unissued stock remaining SELECT r.batch_id , r.quantity , SUM(i.quantity) as issued , CASE WHEN SUM(i.quantity) IS NULL THEN r.quantity ELSE r.quantity - SUM(i.quantity) END as remaining FROM stock_receipt r LEFT JOIN stock_issue i USING (batch_id) WHERE r.product_id = 9 AND receipt_date < NOW() GROUP BY r.batch_id HAVING remaining > 0; +----------+----------+--------+-----------+ | batch_id | quantity | issued | remaining | +----------+----------+--------+-----------+ | 1 | 15 | 10 | 5 | | 2 | 10 | NULL | 10 | +----------+----------+--------+-----------+ Loop through these records and allocate remaining quantities, creating issue records containing the batch and quantity. If there are more required issues than remain in stock, write a pending issue for the outstanding quantity without a batch_id. Therefore if 20 are issued, the issue records written to the stock_issues table would be Batch Qty ------+------ 1 5 2 10 5 When a new receipt is entered, the process is similar but reversed. I.E. Batch Qty ------+------ 1 5 2 10 3 2 3 get the pending issues for that product (no batch_id). Loop through them allocating the new batch_id where they can be fulfilled from the new receipt. If 2 are received then 2 of the pending 5 items can come from batch 3 (new batch) there would then be 3 pending. Below is the code for a small application to illustrate the process
  18. Barand
    Is this what you want to achieve Receipts Batch | a | b | c | d | Date | 2020-01-01 | 2020-01-02 | 2020-01-03 | 2020-01-04 | Qty | 15 | 10 | 23 | 30 | ------------------------+--------------+--------------+--------------+--------------+ Issues | | | | | | | | | | 2020-01-01 10 | 10 | | | | 10 from batch a 2020-01-02 7 | 5 | 2 | | | 5 from batch a, 2 from batch b 2020-01-03 25 | | 8 | 17 | | 8 from batch b, 17 from batch c 2020-01-04 16 | | | 6 | 10 | 6 from batch c, 10 from batch d
  19. Barand
    Still not totally clear. If, for the time being, we ignore which batch to issue from then you have received 78 and issued 58. That leaves 20, not 8 +------------+-----+----+-----+-------+ | Date | ID | In | Out | Level | +------------+-----+----+-----+-------+ | 2020-10-01 | 9 | 15 | 10 | 5 | | 2020-10-02 | 9 | 10 | 7 | | | 2020-10-03 | 9 | 23 | 25 | | | 2020-10-04 | 9 | 30 | 16 | | +------------+-----+----+-----+-------+ | 78 | 58 | +----+-----+
  20. Barand
    I will be waiting for paulvz to answer requinix's questions regarding the data, instead of just reposting it.
  21. Barand
    Here's another solution <?php include 'db_inc.php'; $db = pdoConnect(); ### ### PROCESS POSTED DATA ### if ($_SERVER['REQUEST_METHOD'] == 'POST') { $stmt = $db->prepare("UPDATE sales SET confirmed = 1 WHERE id = ? "); foreach ($_POST['confirm'] as $id => $v) { $stmt->execute([ $id ]); } header("Location: #"); // reload the page and refresh html table exit; } ### ### GET THE DATA TO DISPLAY ### $res = $db->query("SELECT DATE_FORMAT(s.sales_date, '%b %d, %Y') as date , CONCAT(u.firstname, ' ', u.lastname) as name , CONCAT_WS(', ', u.country, u.city, u.address) as address , FORMAT(SUM(d.quantity * p.price), 2) as total , s.id as salesid FROM sales s INNER JOIN users u ON s.user_id = u.id INNER JOIN details d ON s.id = d.sales_id INNER JOIN products p ON d.product_id = p.id WHERE s.confirmed = 0 GROUP BY s.id "); $tdata = ''; foreach ($res as $r) { $tdata .= "<tr><td>{$r['date']}</td> <td>{$r['name']}</td> <td>{$r['address']}</td> <td class='ra'>{$r['total']}</td> <td><input type='checkbox' name='confirm[{$r['salesid']}]' value='1'></td> "; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Example</title> <style type='text/css'> body, table { font-family: verdana, sans-serif; font-size: 10pt; } table { border-collapse: collapse; width: 80%; margin: 0 auto; } #header { background-color: indigo; color: white; margin-bottom: 32px; padding: 16px; } th { padding: 8px; background: blue; color: white; } td { padding: 4px 8px; } td.ra { text-align: right; } </style> </head> <body> <div id='header'> <h1>Example Sales Confirmation</h1> </div> <form method='POST'> <table border='1'> <tr><th>Date</th> <th>Name</th> <th>Address</th> <th>Total</th> <th>Confirm</th> </tr> <?=$tdata?> <tr><td colspan='4'>&nbsp;</td> <td><button type='submit'>Submit</button></td> </tr> </table> </form> </body> </html> [edit] PS It assumes your data looks like this users id ----+ firstname | lastname | address | city | country | | | sales products | id -----+ +-------- id +----< user_id | | price sales_date | | confirmed | | | details | | id | +----------< sales_id | product_id >-------+ quantity
  22. Barand
    I have no idea. Your continued use of "SELECT * " completely obfuscates your table structures. Also, that "solution" that you found is far from optimal.
  23. Barand
    You use a WHERE clause, just like you have in your UPDATE query. There is an SQL tutorial link in my signature.
  24. Barand
    Do not run queries in a loop like that where you are using each row from one query to provide the value to do another select query. You apparently are aware of joins, so just do a single query using another join. If you are concerned with rows where confirmed == 0, put that condition in your query instead of retrieving every row and the ignoring those you don't want. Also, don't use select *. Specifiy the columns you require. (i would rewrite the query for you but, because you used "*", I don't know what you are selecting from which tables.) Now to your question... Put the output in a form so it can be submitted to the server to update the records.
  25. Barand
    Illegible.
×
×
  • 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.