Barand

I think they appeared about an hour later when there was another comment on same topic. An hour is long lag for what is essentially the same data, slightly different query

After writing my version I compared it to yours and noticed it was almost identical to your subquery. Your version only needed the query you have in the subquery (+ HAVING clause + ORDER BY). The rest was completely superfluous.

Activity (condensed mode) is losing topics). Today I had 2 notifications for a topic Which appear in the expanded activity page But have disappeared from the condensed version

Yes, it's to preserve the price at which the product was sold so that historical reports can use that price and not the price it is currently being sold at (if at all). Keeps the accounting straight.

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;

What are the keys on those table?

As in Test data : that data for which the query works

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.

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.)

Any chance of a dump of the test tables for that query?

Did I hear the sound of a penny dropping?

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.

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

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 | | +------------+---------------+------+-----+---------+----------------+

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.

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; }

I type them out in my editor then paste them as code to retain the monospaced font.

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>

It would still only be a single query (with a few joins). What is your problem with that?

Your UPDATE query should be an INSERT .. ON DUPLICATE KEY query. If there is a duplicate you should be updating the city, not the id. INSERT INTO user_city (id_city, city) VALUES ('$id_city', '$city') ON DUPLICATE KEY UPDATE city = '$city' Better still, use prepared statements.

Once you have the product ids and quantities from your POST data, write them to a db table (cart or order_items - call it what you will) Then you just query that table joined to product table to produce the invoice. This is the bare bones of the process +----------------+ +----------------+ | product | | order_item | +----------------+ +----------------+ | id | | id | | description |- - - <| product_id | | price | | qty | +----------------+ +----------------+ SELECT p.description , o.qty , o.qty * p.price as amount FROM order_item o JOIN product p ON o.product_id = p.id WHERE o.id = ?

<a href='page_to_go_to'> <img src='image_file_is_here' > </a>

Oops!. Sorry about that. I am so used to passing an array to the execute() method that I forget PDO also has parameter binding option.

Before you fetch() us this .... https://www.php.net/manual/en/mysqli-stmt.bind-result.php

It looks like you expect $sql->fetch() to return an associative array. It puts the resuts into bound variables