Jump to content

Barand

Moderators
  • Posts

    22,119
  • Joined

  • Last visited

  • Days Won

    599

Everything posted by Barand

  1. You could just forget about the session value and SELECT ... ORDER BY RAND(dayofyear(CURDATE()))
  2. Good luck with that. The MQTT is javascript, which runs in the client browser. PHP runs on the server.
  3. I get it now. (I'll take the order example as I happened to have a convenient pair of test tables) TABLE: oc_order +----------+-----------+----------+------------+-----------------+ | order_id | firstname | lastname | date_added | order_status_id | +----------+-----------+----------+------------+-----------------+ | 1 | Hugh | Jass | 2020-05-01 | 1 | | 2 | Laura | Norder | 2020-05-03 | 1 | | 3 | Tom | DiCanari | 2020-05-05 | 1 | | 4 | Peter | Dowt | 2020-05-07 | 1 | +----------+-----------+----------+------------+-----------------+ 4 rows in set (0.00 sec) TABLE: oc_order_product +------------------+----------+-------------+----------+ | order_product_id | order_id | name | quantity | +------------------+----------+-------------+----------+ | 1 | 1 | Gizmo | 2 | | 2 | 1 | Widget | 5 | | 3 | 1 | Wotsit | 1 | | 4 | 2 | Thingy | 2 | | 5 | 2 | Widget | 1 | | 6 | 2 | Thingumajig | 1 | | 7 | 3 | Gizmo | 20 | | 8 | 3 | Widget | 15 | | 9 | 4 | Gizmo | 25 | | 10 | 4 | Thingy | 5 | | 11 | 4 | Wotsit | 10 | +------------------+----------+-------------+----------+ PROBLEM: to produce a paginated result with 2 orders per page SOLUTION: Use a paginated subquery for the orders instead of the order table itself PAGE 1 SELECT o.order_id , customer , p.name , p.quantity FROM ( SELECT order_id , concat(firstname, ' ', lastname) as customer FROM oc_order ORDER BY order_id LIMIT 0, 2 -- <- paginate here ) o JOIN oc_order_product p USING (order_id) +----------+--------------+-------------+----------+ | order_id | customer | name | quantity | +----------+--------------+-------------+----------+ | 1 | Hugh Jass | Gizmo | 2 | | 1 | Hugh Jass | Widget | 5 | | 1 | Hugh Jass | Wotsit | 1 | | 2 | Laura Norder | Thingy | 2 | | 2 | Laura Norder | Widget | 1 | | 2 | Laura Norder | Thingumajig | 1 | +----------+--------------+-------------+----------+ PAGE 2 SELECT o.order_id , customer , p.name , p.quantity FROM ( SELECT order_id , concat(firstname, ' ', lastname) as customer FROM oc_order ORDER BY order_id LIMIT 2, 2 ) o JOIN oc_order_product p USING (order_id) +----------+--------------+--------+----------+ | order_id | customer | name | quantity | +----------+--------------+--------+----------+ | 3 | Tom DiCanari | Gizmo | 20 | | 3 | Tom DiCanari | Widget | 15 | | 4 | Peter Dowt | Gizmo | 25 | | 4 | Peter Dowt | Thingy | 5 | | 4 | Peter Dowt | Wotsit | 10 | +----------+--------------+--------+----------+
  4. Are you sure this is a database table and not a spreadsheet? Can you show a sample of the data to ilustrate the problem? It's difficult to imagine why this might be the case.
  5. If you want a wavy line, just generate a sine wave.
  6. The first one looks to me like it was created with &sext; or similar
  7. You gotta be joking. There's only one true Editor - PHPEd 😁 - and so it begins.
  8. An earlier post of yours shows you apparently disapprove of people posting solutions, so why should we?
  9. While you're in a receptive mood I have another couple of recommendations for you. Use prepared statements so you aren't putting data values directly into the queries. Switch to PDO from mysqli. It's far more streamlined and makes (1) above much easier.
  10. BTW, the time_start, time_end, break_time columns in your stafftime table should be type TIME, not VARCHAR.
  11. How you do it depends on how you are entering the data. The are basically two approaches... Create the timesheet record first then, each day add that days stafftime record Have a form where you enter the timesheet header data and all days' times at once for a user. If you use the first, have a dropdown so the user can select the timesheet record id and write that id with the day's times into stafftime. Doing it the second way, on posting the form you would first write the timesheet record (id would be an autoincrement column) then call lastInserId() to get the id of that new record. You then insert the time records with that last inserted id as the sheet_id.
  12. Received and loaded. Thanks. For each timesheet record you should have up to 7 (1 for each day of the week) records in stafftime. These stafftime records for the week should have a sheet_id value which matches the timesheet id of the parent timesheet record. You stafftime.sheet_id values match none of the timesheet ids.
  13. Not what I asked for. There is no way that enables me to run your query on your data. I wanted something similar to what I posted in your previous topic...
  14. Do you want to post a dump of your data so I can run your query at my end?
  15. Did you read the bit in the JOIN tutorial about LEFT JOIN to a table (user in your case) and then referencing a column from that table in the WHERE clause?
  16. Follow the link in my sig. There's a section in the tutorials dedicated to JOINS
  17. Put this line immediately before your line that creates your mysqli connection mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); That will force mysqli to report any erors it encounters.
  18. As @gizmola said, the first problem is missing style= second) You are using obsolete html markup 3) you have specified a couple of classes but not put them inside the class="w3-container" 4) Again inline style without the style= 5) You have broken up the style attributes with a couple of rogue quotes
  19. If you know the cat_id, can't you then determine the category? All you should need to pass is the cat_id, which should be the value in your options.
  20. Why? What's the difference between your category and cat_title?
  21. Looking at the code you posted it looks you have decided to ignore the rules of syntax for inline style definitions and class specifications. Plus a bit of obsolete html markup thrown in for good measure
  22. Here's my attempt SELECT sale_date , sum(cashpaid) + sum(cashpartpaid) + sum(codpaid) as total_cash_sales , sum(cashdue) + sum(cashpartdue) + sum(carddue) + sum(coddue) as total_credit_sales , sum(codpaid) + sum(coddue) as total_cod_sales , sum(cardpaid) as total_card_sales FROM ( SELECT bad.basket_id , bad.amount_due as due , SUM(p.amount_paid) as paid , p.payment_method_id , b.payment_status , DATE(bad.sale_time) as sale_date , CASE WHEN payment_method_id = 1 AND payment_status = 'paid' THEN sum(p.amount_paid) ELSE 0 END as cashpaid , CASE WHEN payment_method_id = 1 AND payment_status = 'due' THEN bad.amount_due ELSE 0 END as cashdue , CASE WHEN payment_method_id = 1 AND payment_status = 'partial' THEN SUM(p.amount_paid) ELSE 0 END as cashpartpaid , CASE WHEN payment_method_id = 1 AND payment_status = 'partial' THEN bad.amount_due - SUM(p.amount_paid) ELSE 0 END as cashpartdue , CASE WHEN payment_method_id = 2 THEN SUM(p.amount_paid) ELSE 0 END as cardpaid , CASE WHEN payment_method_id = 2 THEN bad.amount_due - SUM(p.amount_paid) ELSE 0 END as carddue , CASE WHEN payment_method_id = 4 AND payment_status <> 'due' THEN SUM(p.amount_paid) ELSE 0 END as codpaid , CASE WHEN payment_method_id = 4 AND payment_status = 'due' THEN bad.amount_due ELSE 0 END as coddue FROM basket_amount_due bad JOIN basket b USING (basket_id) LEFT JOIN basket_payment p USING (basket_id) GROUP BY basket_id, payment_method_id ) detail GROUP BY sale_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.