Group multiple inputs with the same ID into a single row and insert it into new table

[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 and be inserted into this new table called invoice_price . So after the data 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);
          }
  }

[benanamen]

Your code is vulnerable to an SQL Injection Attack. NEVER EVER put variables in your query.

Never trust user input.
You need to use Prepared Statements.
Do not create variables for nothing.

[Callisto]

In fact, you should use pdo for all your sql queries.

Check this exemple :

function pdo($x, $y){
    $db = new Database();
    $req = $db->prepare("SELECT * FROM table_name WHERE x = '?' AND y = '?'",[$x,$y]);
}

You can use pre-existing database pdo class like this :

 class Database {

        private $dbName;
        private $dbUser;
        private $dbPass;
        private $dbHost;
        private $pdo;

        public function __construct($dbName, $dbUser, $dbPass, $dbHost) {
            $this->dbName = $dbName;
            $this->dbUser = $dbUser;
            $this->dbPass = $dbPass;
            $this->dbHost = $dbHost;

        }

        private function getPDO() {
            if($this->pdo === null) {
                $pdo = new \PDO("mysql:dbname=".$this->dbName.";host=".$this->dbHost.';charset=UTF8', $this->dbUser, $this->dbPass);
                $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
                $this->pdo = $pdo;
            }
            return $this->pdo;
        }

        public function prepare($statement, $values, $one = false) {
            $req = $this->getPDO()->prepare($statement);
            $req->execute($values);
            $req->setFetchMode(\PDO::FETCH_OBJ);
            if($one){
                $datas = $req->fetch();
            } else {
                $datas = $req->fetchAll();
            }

            return $datas;
        }

    }