group multiple inputs with the same ID into a single row in html

[sashavalentina]

I have a system that saves the user purchase into a table called ordered_items. Where the purchase data will be saved into this table if the product stock available is more than the quantity that the user intended to purchase. The way how the system saved the data is by id. Which means if the USER A bought one iPad, two iPhone , USER B bought one AirPods, Iphone and an Ipad, USER C bought three IMac, two Macbook. The data that store in the table will look like this 

Now I created another table called the invoice_price table where i want the above data to be group according to the order_id in the ordered_items table. So after the table is grouped, the data saved in the database will look something like this

I tried the below sql but it does not work. Does anyone knows what can i do to achieve what I described above?

 if($product_stocks >= $quantity) {
          $osql = "INSERT INTO `ordered_items`(`order_id`, `user_id`, `seller_id`, `product_id`, `quantity`, `purchase_price`) 
          VALUES ('$order_id', '$user_id', '$seller_id', '$product_id', '$quantity', '$purchase_price')";
          if($conn->query($osql)) {
              $product_name = $row['cart_name'];
              $cn_product_name = $row['cn_cart_name'];
              $m_product_name = $row['m_cart_name'];
              $nsql = "INSERT INTO `notifications`(`notification_title`,`cn_notification_title`,`m_notification _title`, `notification_date`, `notification_text`,`cn_notification_text`,`m_notification_text`, `user_id`, `seller_id`, `order_id`, `product_id`, `user_notify`, `seller_notify`, `admin_notify`) 
              VALUES 
              ('New $order_type ','新订单','Pesanan Baharu', '$now', '<b>Order No: #$order_id</b><br>$order_type for $quantity $product_name is Placed Successfully', '<b>订单号: #$order_id</b><br>已成功下单 $quantity 的 $cn_product_name ', 
              '<b>Nombor Pesanan: #$order_id</b><br>Pesanan untuk  $quantity $m_product_name anda sudah berjaya ', '$user_id', '$seller_id', '$order_id', '$product_id', 0, 0, 0)";
             
              if($conn->query($nsql)){
                 $discountsql = "INSERT INTO invoice_price (order_id) SELECT order_id FROM ordered_items GROUP BY order_id WHERE order_id = '$order_id'";
                  $conn->query($discountsql);
              }
          }

 

[requinix]

26 minutes ago, sashavalentina said:

Now I created another table called the invoice_price table where i want the above data to be group according to the order_id in the ordered_items table. So after the table is grouped,

Explain what that means. "Group" how?

[sashavalentina]

3 minutes ago, requinix said:

Explain what that means. "Group" how?

means i want the data to be saved by grouping the order_id from the orderd_items table to the invoice_price table

[sashavalentina]

5 minutes ago, requinix said:

Explain what that means. "Group" how?

i tried the sql codes query that i posted, it doesn't got inserted into the invoice_price table

 

[requinix]

4 minutes ago, sashavalentina said:

means i want the data to be saved by grouping the order_id from the orderd_items table to the invoice_price table

That does not help to explain why you're doing this. I'm asking because this query

INSERT INTO invoice_price (order_id) SELECT order_id FROM ordered_items GROUP BY order_id WHERE order_id = '$order_id'

doesn't make any sense: you take the $order_id that you already know, search for it in the ordered_items table, and then insert those results into invoice_price? Why aren't you inserting the $order_id value directly just like you did with ordered_items?

[sashavalentina]

35 minutes ago, requinix said:

That does not help to explain why you're doing this. I'm asking because this query

INSERT INTO invoice_price (order_id) SELECT order_id FROM ordered_items GROUP BY order_id WHERE order_id = '$order_id'

doesn't make any sense: you take the $order_id that you already know, search for it in the ordered_items table, and then insert those results into invoice_price? Why aren't you inserting the $order_id value directly just like you did with ordered_items?

Because i wanted to group the order_id into another table. Which i have no idea how should i do it. If i do it like the one i did with the ordered_items table, the order_id will be saved in the database saperately into my invoice_price table. I want the order_id to be saved in groups. Can i know is there any way i can do that?

 

 

[requinix]

You need to stop saying "group by" and start describing what you want to do.

[sashavalentina]

9 minutes ago, requinix said:

You need to stop saying "group by" and start describing what you want to do.

 

9 minutes ago, requinix said:

You need to stop saying "group by" and start describing what you want to do.

What i want to do is to insert the data where the same order_id will not be duplicated. Which means, i have order_id of

order_id 
1001

1001

1001

1002

1002

1034

1034

1034

1034

1066

in the table "ordered_items".

Now i want the order_id to be saved in the "invoice_price" looks like this
order_id
1001

1002

1034

1066

you get what i mean? the same order_id will be inserted as one in the invoice_price table. There won't be duplicated order_id in the invoice_price table. 
 

[Barand]

I suspect your notification insert may be failing with an "unknown column" error. (You appear to have a space added in one of the column names) Implement database error checking.

I also suspect that the concept of data normalization is completely foreign to you.

You currenty have

+-------------------+              +-------------------+              +-----------------------+
| order_price       |              | ordered_items     |              | notifications         |
+-------------------+              +-------------------+              +-----------------------+
|  id               |-------+      | id                |              | id                    |
|  order_id         |       +------| order_id          |------+       | notification_title    |
+-------------------+              | user_id           |      |       | cn_notification_title |
                                   | seller_id         |      |       | m_notification_title  |
                                   | product_name      |      |       | notification_date     |
                                   | quantity          |      |       | notification_text     |
                                   | purchase_price    |      |       | cn_notification_text  |
                                   +-------------------+      |       | m_notification_text   |
                                                              |       | user_id               |
                                                              |       | seller_id             |
                                                              +-------| order_id              |
                                                                      | product_id            |
                                                                      | user_notify           |
                                                                      | seller_notify         |
                                                                      | admin_notify          |
                                                                      +-----------------------+

Basically

• Data should be stored in one place only (with exception of ids which are used for the relational joins)
• Derived data should not be stored

Your notifications table, with the exception of the date and final three (repeated) columns is either duplicated or derived. I would suggest the following model

+-------------------+              +-------------------+              +----------------+
| order             |              | order_item        |              | product        |
+-------------------+              +-------------------+              +----------------+
| order_id          |------+       | id                |      +-------| product_id     |
| user_id           |      +------<| order_id          |      |       | product_name   |
| order_date        |      |       | product_id        |>-----+       | seller_id      |
+-------------------+      |       | quantity          |              | purchase_price |
                           |       +-------------------+              +----------------+
                           |
                           |
                           |       +-------------------+              +-----------------------+
                           |       | notification_log  |              | notification_title    |
                           |       +-------------------+              +-----------------------+
                           |       |                   |              | id                    |
                           |       | id                |       +------| who_notified          |
                           |------<| order_id          |       |      | notification_title    |
                                   | who_notified      |>------+      +-----------------------+
                                   | date_notified     |
                                   +-------------------+

Also, you should be using prepared statements for those inserts instead of putting data values directly into the queries.

Consider using transactions when inserting multiple records at one time.

Edited June 22, 2021 by Barand