Barand

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

Yes. The referential integrity enforced by the FK will allow you to insert car or vacation records only if the parent person exists. No, they can be used in updates and deletions too.

Should look something like this <?php if ($_SERVER['REQUEST_METHOD'] == 'POST') { $quantities = array_filter($_POST['qty']); # remove 0 qtys foreach ($quantities as $id => $qty) { echo "Product #$id : $qty ordered<br>" ; } } $results = $pdo->query("SELECT id, description FROM product"); ?> <!DOCTYPE html> <html lang='en'> <head> <title>Vehicle Allocation</title> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'> </head> <body> <form method='POST'> <table style='width:500px'> <tr style='background-color: #ccc'><td>ID</td><td>Product</td><td>Quantity</td></tr> <?php foreach($results as $r) { echo "<tr><td>{$r['id']}</td> <td>{$r['description']}</td> <td> <input type='number' value='0' name='qty[{$r['id']}]'> </td> </tr> "; } ?> </table> <br> <button>Submit</button> </form> </body> </html>

We can't see the query which get the list of products, so we don't know what the index should be. It will be whatever your SELECT clause says it is.

Should be possible. Without seeing your SQL code we can't help. This worked... mysql> CREATE TABLE `person` ( -> `id` int(11) NOT NULL, -> `username` varchar(20) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.21 sec) mysql> CREATE TABLE `favourite_car` ( -> `id` int(11) NOT NULL, -> `year` year(4) DEFAULT NULL, -> `make` varchar(45) DEFAULT NULL, -> `model` varchar(45) DEFAULT NULL, -> `color` varchar(45) DEFAULT NULL, -> `person_id` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `FK_car_person_idx` (`person_id`), -> CONSTRAINT `FK_car_person` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.26 sec) mysql> CREATE TABLE `favourite_vacation` ( -> `id` int(11) NOT NULL, -> `location` varchar(45) DEFAULT NULL, -> `hotel` varchar(45) DEFAULT NULL, -> `duration` int(11) DEFAULT NULL, -> `person_id` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `FK_vacation_person_idx` (`person_id`), -> CONSTRAINT `FK_vacation_person` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.31 sec)

Since DATETIME and TIMESTAMP now behave similarly, you can now have a column to auto-record the time inserted and a separate on to record update times EG CREATE TABLE `a_test_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `start` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `finish` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; The differences are Being 1 byte smaller (4 bytes) TIMESTAMP has a reduced date range TIMESTAMP is stored as UTC time and converted back to current timezone on retrieval

i would name my qty fields using the product id as the key <td><input type="text" id="qty" name="qty[<?=$d_row['id']?>]"></td> then your $_POST['qty'] array for products 1, 4 and 7 will look like Array ( 1 => 5, 4 => 15, 7 => 10 ) - you're products and ids in one bundle.

Here's my version of a calendar script. It uses PDO::FETCH_GROUP as @mac_gyver suggested. I am using it to group by week number to make it easy to start a new row for each week. My test data is a schedule of fictional adult evening classes in English, pottery, dog grooming and origami. It also makes use of my "digit" table to generate dates. +----+------------+------------+ mysql> select num from digit; | id | coursecode | coursedate | +------+ +----+------------+------------+ | num | | 21 | ENG | 2022-05-06 | +------+ | 22 | ENG | 2022-05-13 | | 0 | | 23 | ENG | 2022-05-27 | | 1 | | 24 | ENG | 2022-06-03 | | 2 | | 25 | ENG | 2022-06-10 | | 3 | | 26 | DOG | 2022-05-02 | | 4 | | 27 | DOG | 2022-05-09 | | 5 | | 28 | DOG | 2022-05-16 | | 6 | | 29 | DOG | 2022-05-23 | | 7 | | 30 | DOG | 2022-05-30 | | 8 | | 31 | POT | 2022-05-03 | | 9 | | 32 | POT | 2022-05-10 | +------+ | 33 | POT | 2022-05-17 | | 34 | POT | 2022-05-24 | | 35 | POT | 2022-05-31 | | 36 | ORI | 2022-06-03 | | 37 | ORI | 2022-06-17 | | 38 | ORI | 2022-07-01 | | 39 | ORI | 2022-05-13 | | 40 | ORI | 2022-05-16 | | 41 | ORI | 2022-06-17 | | 42 | DOG | 2022-06-10 | +----+------------+------------+ and the output for May 2022 is CODE <?php include 'db_inc.php'; # use your own $pdo = pdoConnect('test'); # connection code $numYear = 2022; $numMonth = 5; $dt1 = (new DateTime("{$numYear}-{$numMonth}-01")); $lastday = (clone $dt1)->modify('+1 month')->format('Y-m-d'); $monthname = $dt1->format('F'); $dstr = $dt1->modify('last monday')->format('Y-m-d'); $res = $pdo->query("SELECT weekofyear(thedate) , thedate , day(thedate) as day , month(thedate) as mth , dayofweek(thedate) as dayno , GROUP_CONCAT(coursecode SEPARATOR '<br>') as courses FROM ( SELECT '$dstr' + INTERVAL a.num*10 + b.num DAY as thedate -- subquery to generate all FROM digit a, digit b -- dates in required range WHERE '$dstr' + INTERVAL a.num*10 + b.num DAY < '$lastday' ) d LEFT JOIN a_course_date a ON d.thedate = a.coursedate GROUP BY thedate "); $dates = $res->fetchAll(PDO::FETCH_GROUP); ?> <!DOCTYPE html> <html lang='en'> <head> <title>Sample Calendar</title> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'> <style type='text/css'> body { font-family: calibri, sans-serif; font-size: 11pt; } table { border-collapse: collapse; width: 700px; margin: 50px auto; } tr { vertical-align: top; } th { background-color: #EEE; width: 14%; padding: 8px; } th.we { background-color: #CCC; } td { background-color: #e6ffe6; width: 14%; height: 75px; padding: 4px; text-align: center; } td.we { background-color: #b3ffb3; } td.today { border: 3px solid #8F1FCF; } td.blank { background-color: white; color: #AAA; } span.dno { float: right; font-size: 9pt; } </style> </head> <body> <table border='1'> <caption><?="$monthname $numYear"?></caption> <tr><th>Mon</th> <th>Tue</th> <th>Wed</th> <th>Thu</th> <th>Fri</th> <th class='we'>Sat</th> <th class='we'>Sun</th> </tr> <?php foreach ($dates as $wk => $days) { echo "<tr>\n"; foreach ($days as $d) { if ($d['mth'] == $numMonth) { $today = $d['thedate'] == date('Y-m-d') ? 'today' : ''; $we = in_array($d['dayno'], [1,7]) ? 'we' : ''; $cls = "$we $today"; } else { $cls = 'blank'; } echo "<td class='$cls' > <span class='dno'>{$d['day']}</span> <br> {$d['courses']} </td>\n"; } echo "</tr>\n"; } ?> </table> </body> </html>