title: Storing order details in MYSQL database using php
body: <p>I'm trying to learn php and MYSQL through developing an eCommerce website. So far the experience with these new languages are really good, but I'm stuck on one of my ideas. I have managed to add the order details to my database, however it adds only a product at a time. Originally what I wanted to do is to add more than a product and store it in a database.</p>
  
  <p>For an example let's say an user wants to buy 5 items. How can I store this into my database. With my current code this just add as a single product with the total of 5 products.(Even the quantity is not adding up).</p>
  
  <p>I'm not sure whether my code is correct. Any suggestions on this is really appreciated. (Please feel free to modify code, it will be really helpful)</p>
  
  <p>Following is the code I'm using to add orders to database.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;div class="col-md-12"&gt;
    &lt;h4 class="text-center"&gt;Paypal Intergration Goes Here.&lt;/h4&gt;
    &lt;img src="./images/paywith_paypal.png" class="img-responsive  center-block" alt="Pay With Paypal"&gt;
  
  
    &lt;?php
  // Getting product details
  $total = 0;
  global $con;
  $ip = getIp();
  
  $sel_price = "SELECT * FROM zeus_limited.cart WHERE ip_address='$ip'";
  $run_price = mysqli_query($con, $sel_price);
  
  while($p_price=mysqli_fetch_array($run_price)){
    $product_id = $p_price['cart_product_id'];
    $product_price = "SELECT * FROM zeus_limited.product WHERE product_id='$product_id'";
    $run_product_price = mysqli_query($con,$product_price);
  
    while ($prod_price = mysqli_fetch_array($run_product_price)){
      $product_price = array($prod_price['product_price']);
      $product_id = $prod_price['product_id'];
      $pro_name = $prod_price['product_title'];
      $values = array_sum($product_price);
      $total +=$values;
    }
  }
  
  // Getting Quantity from cart
  $get_quantity = "SELECT * FROM zeus_limited.cart WHERE cart_product_id='$product_id'";
  $show_quantity = mysqli_query($con, $get_quantity);
  $row_qty = mysqli_fetch_array($show_quantity);
  $quantity = $row_qty['quantity'];
  if($quantity == 0){
    $quantity = 1;
  }
  else {
    $quantity = $quantity;
    $total = $total * $quantity;
  }
  
  // Getting Customer details
  $user = $_SESSION['customer_email'];
  $get_customer = "SELECT * FROM zeus_limited.customer WHERE customer_email='$user'";
  $show_customer = mysqli_query($con, $get_customer);
  $row_c = mysqli_fetch_array($show_customer);
  
  $customer_id = $row_c['customer_id'];
  $customer_email = $row_c['customer_email'];
  $customer_name = $row_c['customer_fname'];
  
  $trx_id = mt_rand();
  $currency = 'USD';
  $invoice = mt_rand();
  
  
  // Insert data to ORDER table
  $add_order = "INSERT INTO zeus_limited.orders (order_product_id, order_customer_id, order_quantity, invoice_no, status, order_date) VALUES ('$product_id','$customer_id','$quantity','$invoice','in Progress',NOW())";
  $run_order = mysqli_query($con, $add_order);
  
  // Insert data to PAYMENT table
  $add_payment = "INSERT INTO zeus_limited.payment (amount, payment_customer_id, payment_product_id, trx_id, payment_currency, payment_date) VALUES ('$total','$customer_id','$product_id','$trx_id','$currency',NOW())";
  $run_payment = mysqli_query($con, $add_payment);
  
  // Removing products from CART
  $empty_cart = "DELETE FROM zeus_limited.cart";
  $show_customerart = mysqli_query($con, $empty_cart);
  
  
  if($total == $total){
    echo "&lt;div class='text-center'&gt;";
    echo "&lt;h3&gt;Welcome:" . $_SESSION['customer_email']. "&lt;br&gt;" . "Hooray! Your Payment was successful!&lt;/h3&gt;";
    echo "&lt;a href='./customer/my_account.php'&gt;Go to your Account&lt;/a&gt;&lt;br&gt;";
    echo "&lt;/div&gt;";
  
  }
  else {
    echo "&lt;div class='text-center'&gt;";
    echo "&lt;h4&gt;Welcome Guest, Payment process failed... Please try again&lt;/h4&gt;&lt;br&gt;";
    echo "&lt;a href='/shop_products.php'&gt;Go to Back to shop&lt;/a&gt;";
    echo "&lt;/div&gt;";
  }
  
  ?&gt;
  &lt;/div&gt;</code></pre>
  </div>
  </div>
  </p>
  
  <p>I'm attaching the cart code as well.(if it's needed)</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;?php
  session_start();
  require_once './includes/init.php';
  require_once './functions/functions.php';
  
  echo getCart();
  
  ?&gt;
  &lt;!DOCTYPE html&gt;
  &lt;html&gt;
  &lt;head&gt;
    &lt;title&gt;Zeus Pvt. Ltd&lt;/title&gt;
    &lt;link rel="stylesheet" href="css/bootstrap.min.css" /&gt;
    &lt;link rel="stylesheet" href="css/font-awesome.min.css" /&gt;
    &lt;link rel="stylesheet" href="css/style.css" /&gt;
    &lt;meta name="viewport" content="width=device-width, initial-scale=1,user-scalable=no"&gt;
    &lt;script src="js/jquery-3.2.1.min.js" type="text/javascript"&gt;&lt;/script&gt;
    &lt;script src="js/bootstrap.min.js"&gt;&lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
    &lt;div class="header"&gt;
      &lt;div class="container container-bg"&gt;
        &lt;div class="row"&gt;
          &lt;div class="col-md-4"&gt;&lt;img src="images/logo.png" class="img-responsive" alt="Responsive image"&gt;&lt;/div&gt;
          &lt;div class="col-md-5"&gt;&lt;/div&gt;
          &lt;div class="col-md-3"&gt;
            &lt;div class="zeus_cart"&gt;
              &lt;div class="cart_bg"&gt;
                &lt;ul class="cart"&gt;
                  &lt;i class="cart_icon"&gt;&lt;/i&gt;&lt;p class="cart_desc"&gt;&lt;?php getTotalCartPrice() ?&gt;&lt;br&gt;&lt;span class="yellow"&gt;&lt;?php getTotalItems() ?&gt;&lt;/span&gt;&lt;/p&gt;
                  &lt;div class='clearfix'&gt;&lt;/div&gt;
                &lt;/ul&gt;
                &lt;ul class="product_control_buttons"&gt;
                  &lt;li&gt;&lt;a href="#"&gt;&lt;img src="images/close.png" alt=""/&gt;&lt;/a&gt;&lt;/li&gt;
                  &lt;li&gt;&lt;a href="#"&gt;Edit&lt;/a&gt;&lt;/li&gt;
                &lt;/ul&gt;
                &lt;div class='clearfix'&gt;&lt;/div&gt;
              &lt;/div&gt;
              &lt;ul class="quick_access"&gt;
                &lt;li class="view_cart"&gt;&lt;a href="cart.php"&gt;View Cart&lt;/a&gt;&lt;/li&gt;
                &lt;li class="check"&gt;&lt;a href="checkout.php"&gt;Checkout&lt;/a&gt;&lt;/li&gt;
                &lt;div class='clearfix'&gt;&lt;/div&gt;
              &lt;/ul&gt;
            &lt;/div&gt;
          &lt;/div&gt;
        &lt;/div&gt;
      &lt;/div&gt;
      &lt;div class="container container-bg"&gt;
        &lt;nav class="navbar navbar-default"&gt;
          &lt;div class="container"&gt;
            &lt;div class="navbar-header"&gt;
              &lt;button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar"&gt;
                &lt;span class="sr-only"&gt;Toggle navigation&lt;/span&gt;
                &lt;span class="icon-bar"&gt;&lt;/span&gt;
                &lt;span class="icon-bar"&gt;&lt;/span&gt;
                &lt;span class="icon-bar"&gt;&lt;/span&gt;
              &lt;/button&gt;
              &lt;a class="navbar-brand"&gt;&lt;/a&gt;
            &lt;/div&gt;
            &lt;div id="navbar" class="navbar-collapse collapse"&gt;
              &lt;ul class="nav navbar-nav"&gt;
                &lt;li&gt;&lt;a href="index.php"&gt;Home&lt;/a&gt;&lt;/li&gt;
                &lt;li class="active"&gt;&lt;a href="shop_products.php"&gt;Shop Products&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="customer/my_account.php"&gt;My Account&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="contact_us.php"&gt;Contact Us&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;
                  &lt;?php
                  if(!isset($_SESSION['customer_email'])){
                    echo '&lt;a href="login.php"&gt;Login&lt;/a&gt;';
                  }
                  else {
                    echo '&lt;a href="logout.php"&gt;Logout&lt;/a&gt;';
                  }
                  ?&gt;
                &lt;/li&gt;
                &lt;li class="welcome"&gt;&lt;a&gt;Welcome &lt;?php echo getUsername(); ?&gt;&lt;/a&gt;&lt;/li&gt;
              &lt;/ul&gt;
              &lt;form method="get" action="results.php" enctype="multipart/form-data" class="navbar-form navbar-right"&gt;
                &lt;div class="form-group search"&gt;
                  &lt;input type="text" name="user_query" class="form-control" placeholder="Search Products"&gt;
                &lt;/div&gt;
                &lt;button type="submit" class="btn btn-default btn-search"&gt;&lt;/button&gt;
              &lt;/form&gt;
            &lt;/div&gt;
          &lt;/div&gt;
        &lt;/div&gt;
      &lt;/nav&gt;
    &lt;/div&gt;
    &lt;div class="main"&gt;
      &lt;div class="container container-bg"&gt;
        &lt;div class="row"&gt;
          &lt;div class="col-md-12"&gt;
            &lt;div class="shoppingcart-title"&gt;
              &lt;h4 class="text-center sidebar-main-menu"&gt;Shopping Cart&lt;/h4&gt;
              &lt;div class="table-responsive"&gt;
                &lt;form action="" method="POST" enctype="multipart/form-data"&gt;
                  &lt;table class="table"&gt;
                    &lt;thead&gt;
                      &lt;tr&gt;
                        &lt;th&gt;Product No.&lt;/th&gt;
                        &lt;th&gt;Product(s)&lt;/th&gt;
                        &lt;th&gt;Name&lt;/th&gt;
                        &lt;th&gt;Quantity&lt;/th&gt;
                        &lt;th&gt;Unit Price&lt;/th&gt;
                        &lt;th&gt;Sub Total&lt;/th&gt;
                        &lt;th&gt;Remove&lt;/th&gt;
                      &lt;/tr&gt;
                    &lt;/thead&gt;
                  &lt;/tbody&gt;
                  &lt;?php
                  global $con;
  
                  $total = 0;
                  $ip = getIp();
                  $select_price = "SELECT * FROM zeus_limited.cart WHERE zeus_limited.cart.ip_address='$ip'";
                  $run_price = mysqli_query($con, $select_price);
  
                  while ($product_price = mysqli_fetch_array($run_price)) {
  
                    $product_id = $product_price['cart_product_id'];
                    $product_quantity = $product_price['quantity'];
                    $product_price = "SELECT * FROM zeus_limited.product WHERE zeus_limited.product.product_id ='$product_id'";
                    $run_product_price = mysqli_query($con, $product_price);
  
                    while ($product_new_price = mysqli_fetch_array($run_product_price)) {
  
                      $product_price = array($product_new_price['product_price']);
                      $product_id = $product_new_price['product_id'];
                      $product_image = $product_new_price['product_image_carousel'];
                      $product_title = $product_new_price['product_title'];
                      $unit_product_price = $product_new_price['product_price'];
  
                      $values = array_sum($product_price);
                      $total += $values * $product_quantity;
  
                      ?&gt;
  
                      &lt;tr&gt;
                        &lt;td&gt;&lt;?php echo $product_id; ?&gt;&lt;/td&gt;
                        &lt;td&gt;&lt;img src="images/products/&lt;?php echo $product_image; ?&gt;" &lt;/td&gt;
                        &lt;td&gt;&lt;?php echo $product_title; ?&gt;&lt;/td&gt;
                        &lt;td&gt;&lt;input id="" type="text" name="quantity[&lt;?php echo $product_id; ?&gt;]" size="5" value="&lt;?php echo $product_quantity; ?&gt;" style="text-align:center;"/&gt;
                          &lt;input type="hidden" name="product_id[&lt;?php echo $product_id; ?&gt;]" value="&lt;?php echo $product_id; ?&gt;"
                        &lt;/td&gt;
  
                        &lt;!--Updating the quantity--&gt;
                        &lt;?php
                        $ip = getIp();
  
                        if (isset($_POST['update_cart'])){
  
                          foreach ($_POST['product_id'] as $pid =&gt; $id) {
  
                            $product_id = $id;
                            $product_quantity = $_POST['quantity'][$pid];
                            $update_products = "UPDATE zeus_limited.cart SET quantity = '$product_quantity' WHERE cart_product_id = '$product_id' AND ip_address = '$ip';";
                            $run_update = mysqli_query($con, $update_products);
                          }
                          if($update_products){
  
                            echo "&lt;script&gt;window.open('cart.php','_self')&lt;/script&gt;";
                          }
                        }
                        ?&gt;
  
                        &lt;td&gt;&lt;?php echo 'Rs '. $unit_product_price; ?&gt;&lt;/td&gt;
                        &lt;td&gt;&lt;?php echo 'Rs '.$unit_product_price * $product_quantity  ?&gt;&lt;/td&gt;
                        &lt;td&gt;&lt;input type="checkbox" name="remove[]" value="&lt;?php echo $product_id; ?&gt;"/&gt;&lt;/td&gt;
                      &lt;/tr&gt;
                    &lt;/tbody&gt;
                    &lt;?php } } ?&gt;
                    &lt;thead&gt;
                      &lt;tr&gt;
                        &lt;th&gt;&lt;/th&gt;
                        &lt;th&gt;&lt;/th&gt;
                        &lt;th&gt;&lt;/th&gt;
                        &lt;th&gt;&lt;/th&gt;
                        &lt;th&gt;Grand Total: &lt;/th&gt;
                        &lt;th&gt;&lt;?php echo 'Rs '. $total ?&gt;&lt;/th&gt;
                        &lt;th&gt;&lt;/th&gt;
                      &lt;/tr&gt;
                    &lt;/thead&gt;
                  &lt;/table&gt;
                  &lt;div class="cart_buttons"&gt;
                    &lt;input type="submit" name="update_cart" value="Update Cart"/&gt;
                    &lt;input type="submit" name="continue_shopping" value="Continue Shopping"/&gt;
                    &lt;input type="submit" name="checkout" value="Checkout"/&gt;
                    &lt;?php if (isset($_POST['checkout'])){echo "&lt;script&gt;window.open('checkout.php','_self')&lt;/script&gt;"; } ?&gt;
                  &lt;/div&gt;
                &lt;/form&gt;
                &lt;?php
                global $con;
                $ip = getIp();
  
                if (isset($_POST['update_cart'])){
                  foreach ($_POST['remove'] as $remove_id) {
  
                    $delete_product = "DELETE FROM zeus_limited.cart WHERE cart_product_id='$remove_id' AND ip_address='$ip'";
                    $run_delete = mysqli_query($con, $delete_product);
  
                    if($run_delete){
  
                      echo "&lt;script&gt;window.open('cart.php','_self')&lt;/script&gt;";
                    }
                  }
                }
  
                if (isset($_POST['continue_shopping'])){
  
                  echo "&lt;script&gt;window.open('shop_products.php','_self')&lt;/script&gt;";
                }
              ?&gt;
            &lt;/div&gt;
          &lt;/div&gt;
        &lt;/div&gt;
        &lt;div class="clearfix"&gt;&lt;/div&gt;
      &lt;/div&gt;
    &lt;/div&gt;
  &lt;/div&gt;
  &lt;/div&gt;
  &lt;div class="footer"&gt;
    &lt;div class="container"&gt;
      &lt;div class="row"&gt;
        &lt;div class="col-md-4 footer-grid"&gt;
          &lt;h3&gt;Menu&lt;/h3&gt;
          &lt;ul class="list1"&gt;
            &lt;li&gt;&lt;a href="index.php"&gt;Home&lt;/a&gt;&lt;/li&gt;
            &lt;li&gt;&lt;a href="shop_product.php"&gt;Shop Products&lt;/a&gt;&lt;/li&gt;
            &lt;li&gt;&lt;a href="customer/my_account.php"&gt;My Account&lt;/a&gt;&lt;/li&gt;
            &lt;li&gt;&lt;a href="contact_us.php"&gt;Contact Us&lt;/a&gt;&lt;/li&gt;
            &lt;li&gt;&lt;a href="login.php"&gt;Login&lt;/a&gt;&lt;/li&gt;
          &lt;/ul&gt;
        &lt;/div&gt;
        &lt;div class="col-md-4 footer-grid"&gt;
          &lt;h3&gt;Your Account&lt;/h3&gt;
          &lt;ul class="list1"&gt;
            &lt;li&gt;&lt;a href="#"&gt;My Orders&lt;/a&gt;&lt;/li&gt;
            &lt;li&gt;&lt;a href="#"&gt;Edit Account&lt;/a&gt;&lt;/li&gt;
            &lt;li&gt;&lt;a href="#"&gt;Change Password&lt;/a&gt;&lt;/li&gt;
            &lt;li&gt;&lt;a href="#"&gt;Delete Account&lt;/a&gt;&lt;/li&gt;
            &lt;li&gt;&lt;a href="#"&gt;My Cart&lt;/a&gt;&lt;/li&gt;
          &lt;/ul&gt;
        &lt;/div&gt;
        &lt;div class="col-md-4 footer-grid"&gt;
          &lt;h3&gt;About Us&lt;/h3&gt;
          &lt;p class="footer_desc"&gt;Zeus is a pharmacy focused on providing patients with what they need and deserve - exceptional pharmacy care. It is our responsibility and passion to care for your medication needs.&lt;/p&gt;
          &lt;p class="f_text"&gt;&lt;span class="fa fa-phone" aria-hidden="true"&gt;&lt;/span&gt;  Phone:  &amp;nbsp;&amp;nbsp;&amp;nbsp;+081 123 45 67&lt;/p&gt;
          &lt;p class="email"&gt;&lt;span class="fa fa-envelope" aria-hidden="true"&gt;&lt;/span&gt;  Email: &amp;nbsp;&amp;nbsp;&amp;nbsp;&lt;span&gt;info@zeuspharmacy.com&lt;/span&gt;&lt;/p&gt;
        &lt;/div&gt;
        &lt;div class="clearfix"&gt; &lt;/div&gt;
      &lt;/div&gt;
    &lt;/div&gt;
  &lt;/div&gt;
  &lt;/div&gt;
  &lt;div class="footer-bottom"&gt;
    &lt;div class="container"&gt;
      &lt;div class="row"&gt;
        &lt;div class="col-md-12 footer-grid-bottom"&gt;
          &lt;div class="copyrights"&gt;
            &lt;p&gt;&amp;copy; 2017 Zeus Pharmacy. Made with &lt;span class="fa fa-heart" aria-hidden="true"&gt;&lt;/span&gt; by &lt;a href="https://dilumtharaka.github.io"&gt;Dilum Tharaka&lt;/a&gt;&lt;/p&gt;
          &lt;/div&gt;
        &lt;/div&gt;
      &lt;/div&gt;
    &lt;/div&gt;
  &lt;/div&gt;
  &lt;/body&gt;
  &lt;/html&gt;</code></pre>
  </div>
  </div>
  </p>
  
  <p><a href="https://i.stack.imgur.com/P6ZcV.png" rel="nofollow noreferrer">order table structure image</a></p>
  
  <p><a href="https://i.stack.imgur.com/WKoQm.png" rel="nofollow noreferrer">payment table structure image</a></p>
  
  <p>Above are the table structures I'm using. Thanks in advance</p>