Jump to content

Barand

Moderators
  • Posts

    22,729
  • Joined

  • Last visited

  • Days Won

    647

Barand last won the day on May 15

Barand had the most liked content!

About Barand

Profile Information

Recent Profile Visitors

70,866 profile views

Barand's Achievements

Prolific Member

Prolific Member (5/5)

1.8k

Reputation

293

Community Answers

  1. Does this give what you need? SELECT roster_no , drill_date , count(distinct drill_no) as drills FROM mms_drills JOIN mms_drillatt USING (drill_no) WHERE YEAR(drill_date) = '2022' AND description Not Like '%National%' GROUP BY roster_no, drill_date HAVING drills > 1;
  2. What are the keys on those table?
  3. As in Test data : that data for which the query works
  4. Better than nothing - just I'll see what I can do. It's after midnight here so it'll be tomorrow, so don't hang around.
  5. If it were you wouldn't be getting those duplicate rows. Can you send the sql to create the tables then, and I'll have to make up some some test data to get the result you get. (There's nothing like having to do extra work to help someone.)
  6. Any chance of a dump of the test tables for that query?
  7. Order_id comes from the creation of the order record. If the input is for product #2 and qty 25 and we just created an order with id = 1234 mysql> SELECT 1234, id, 25, price -> FROM product -> WHERE id = 2; +------+----+----+-------+ | 1234 | id | 25 | price | +------+----+----+-------+ | 1234 | 2 | 25 | 9.00 | +------+----+----+-------+ The result row is inserted into the order_item table.
  8. Firstly, One of the syntaxes for an insert statement allows you to to directly insert records into a table that are selected from another. Useful for backing up a table to a copy... CREATE TABLE copy_of_A LIKE table_A; INSERT INTO copy_of_A SELECT * FROM table_A; Secondly, you are correct about the placeholders referencing $order_id, $qty and $id. The $id is used to select the product in the select statement. The price comes from the product row
  9. These two are useful for showing us your table structures: SHOW CREATE TABLE order_item; CREATE TABLE `order_item` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) DEFAULT NULL, `product_id` int(11) DEFAULT NULL, `qty` int(11) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 or, using the mysql CLI, mysql> DESCRIBE order_item; +------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | order_id | int(11) | YES | | NULL | | | product_id | int(11) | YES | | NULL | | | qty | int(11) | YES | | NULL | | | price | decimal(10,2) | YES | | NULL | | +------------+---------------+------+-----+---------+----------------+
  10. NOTE: In the script I posted earlier I used oi.qty * p.price as amount in the query to produce the invoice. As the invoice is being produced immediately in my script, the values product.price and order_item.price will be the same. However, if there is a delay before producing the invoices, or if you subsequently use the data to produce an end-of-quarter or end-of-year sales report, use the price from the order_item table - this is the price at which is was sold. The product price could have moved on since the sale.
  11. The code I posted shows you how to pull the current price from the product table into the order_detail table . This bit... $quantities = array_filter($_POST['qty']); # remove 0 qtys try { $pdo->beginTransaction(); // write order record $stmt = $pdo->prepare("INSERT INTO `order` (customer_id) VALUES (?)"); $stmt->execute([ $_SESSION['customer'] ]); // get the id of the order $order_id = $pdo->lastInsertId(); // now write the order item records, also storing the current prices frmm product table $stmt = $pdo->prepare("INSERT INTO `order_item` (order_id, product_id, qty, price) SELECT ?, id, ?, price FROM product WHERE id = ? "); foreach ($quantities as $id => $qty) { $stmt->execute([ $order_id, $qty, $id ]); } $pdo->commit(); } catch (PDOException $e) { $pdo->rollBack(); $order_id = null; throw $e; }
  12. I type them out in my editor then paste them as code to retain the monospaced font.
  13. This illustrates the mechanics of the process. It processes the posted quantities, writes them to an order and order item file then generates the invoice html. The invoice is written to orders/order_xxx.html where xxx is the order number Example invoice output The tables used are customer order +-------------+-------------+ +-------------+----------+ | Field | Type | | Field | Type | +-------------+-------------+ +-------------+----------+ | customer_id | int(11) | | id | int(11) | | fname | varchar(45) | | customer_id | int(11) | | lname | varchar(45) | | order_date | datetime | +-------------+-------------+ +-------------+----------+ product order_item +-------------+---------------+ +------------+---------------+ | Field | Type | | Field | Type | +-------------+---------------+ +------------+---------------+ | id | int(11) | | id | int(11) | | description | varchar(50) | | order_id | int(11) | | price | decimal(10,2) | | product_id | int(11) | +-------------+---------------+ | qty | int(11) | | price | decimal(10,2) | +------------+---------------+ ` Code <?php include '../db_inc.php'; ## use your own $pdo = pdoConnect(); ## connecction code session_start(); $_SESSION['customer'] = 125; ## I am assuming this would be set when the customer signed in $order_id = null; if ($_SERVER['REQUEST_METHOD'] == 'POST') { $quantities = array_filter($_POST['qty']); # remove 0 qtys try { $pdo->beginTransaction(); // write order record $stmt = $pdo->prepare("INSERT INTO `order` (customer_id) VALUES (?)"); $stmt->execute([ $_SESSION['customer'] ]); // get the id of the order $order_id = $pdo->lastInsertId(); // now write the order item records, also storing the current prices frmm product table $stmt = $pdo->prepare("INSERT INTO `order_item` (order_id, product_id, qty, price) SELECT ?, id, ?, price FROM product WHERE id = ? "); foreach ($quantities as $id => $qty) { $stmt->execute([ $order_id, $qty, $id ]); } $pdo->commit(); } catch (PDOException $e) { $pdo->rollBack(); $order_id = null; throw $e; } // prepare the invoice output $res = $pdo->prepare("SELECT oi.order_id , DATE_FORMAT(o.order_date, '%M %e %Y') as order_date , concat(c.fname, ' ', c.lname) as customer , p.description as product , oi.qty , oi.qty * p.price as amount FROM order_item oi JOIN `order` o ON o.id = oi.order_id JOIN customer c ON c.customer_id = o.customer_id JOIN product p ON oi.product_id = p.id WHERE oi.order_id = ? "); $res->execute([ $order_id ]); $row = $res->fetch(); $inv_total = 0; $inv_output = "<!DOCTYPE html> <html lang='en'> <head> <title>Sample order/invoice</title> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'> </head> <body> <table style = 'width: 400px;'><tr><td><b>Customer<b></td><td>{$row['customer']}</td></tr> <tr><td><b>Order No<b></td><td>" . sprintf('%06d', $order_id) . "</td></tr> <tr><td><b>Order Date<b></td><td>{$row['order_date']}</td></tr> </table><br><br> <table style='width: 600px; border-collapse: collapse;'> <tr style='background-color:#000; color: #FFF'> <td><b>Product</b></td> <td><b>Quantity</b></td> <td><b>Amount</b></td> <tr> "; do { $inv_output .= "<tr><td>{$row['product']}</td> <td>{$row['qty']}</td> <td>{$row['amount']}</td> </tr>"; $inv_total += $row['amount']; } while ($row = $res->fetch()); $inv_output .= "<tr><td colspan='3'>&nbsp;</td></tr> <tr><td colspan='2'><b>TOTAL</b></td><td>$inv_total</td><?tr> </table> </body> <?html>"; // WRITE INVOICE OUTPUT TO HTML FILE file_put_contents("orders/order_{$order_id}.html", $inv_output); header("Refresh: 0"); exit; } else { $results = $pdo->query("SELECT id , description , price FROM product"); $form_output = " <form method='POST'> <table style='width:500px'> <tr style='background-color: #ccc'><td>ID</td><td>Product</td><td>Quantity</td><td>Price</td></tr> "; foreach($results as $r) { $form_output .= "<tr><td>{$r['id']}</td> <td>{$r['description']}</td> <td> <input type='number' value='0' name='qty[{$r['id']}]'> </td> <td style='text-align: right;'>{$r['price']}</td> <tr> "; } $form_output .= " </table> <br> <button>Submit</button> </form> "; } ?> <!DOCTYPE html> <html lang='en'> <head> <title>Sample order/invoice</title> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'> </head> <body> <?php if ($order_id) { // was a new order created? echo $inv_output; } else { // if not, dsplay form echo $form_output; } ?> </body> </html>
  14. It would still only be a single query (with a few joins). What is your problem with that?
×
×
  • 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.