PHP MySQLi – Generate Invoice Number for Ordered Products

MySQLmysqliPHP

I am trying to generate invoice number in orders table, like if user select 1 or multiple products and add it to cart then place order then invoice number should be same in our orders table for that 1 order in multiple rows according to products.
I was using function mt_rand(); but it generates randomly. I want to generate numbers from 0 to so on.
Like, 1, 2, 3, 4, 5, 6, 7 and so on.
I've also tried this to generate invoice number.
$i++

$squery = "SELECT * FROM cart WHERE user_id = '$user_id'";
$sresult = $this->db->select($squery);
if($sresult){
    $totalPrice = 0;
    $i = 0;
    while($svalue = $sresult->fetch_assoc()){
        $i++;
        $pro_id = $svalue['pro_id'];
        $pro_name = $svalue['pro_name'];
        $quantity = $svalue['quantity'];
        $pro_price = $svalue['pro_price'] * $quantity;
        $pro_image = $svalue['pro_image'];
        $query = "INSERT INTO orders(user_id, pro_id, invoice_no, pro_name, status, date) "
                . "VALUES('$user_id', '$pro_id', '$i', '$pro_name', 'processing', NOW())";
        $result = $this->db->insert($query);
        if($result){
            header("location: orders-list.php");
        } else {
            $msg = "<div class='alert alert-danger'>Something's went wrong. Please try again.</div>";
            return $msg;
        }
    }
    return true;
}

But it doesn't inserts same number in multiple ordered products rows of 1 order.
Please help me how can i generate invoice number from 0 to so on.

Best Answer

I would use the MySQL database to calculate order numbers for you using a self populating AUTO_INCREMENT integer column.

Separate out your order table into two distinct tables. One for the order top line details (e.g. order number, customer details) and a second for the order specifics (item, quantity, item ID etc.).

This way you could then insert a line into the first table to 'create' the order. Then you can query the database to get the OrderNumber for this order. Then use that to populate the table containing the details of the order.

There's a very rough example here.

You can then get the order ID using this query

select OrderNumber from Orders where CustomerID = 57
order by OrderDate DESC limit 1;

and get all of the order items with this one:

select * from OrderDetails where ordernumber = 1;

To get all info in one request, assuming you have the OrderNumber, can be done like this:

Select o.OrderNumber
  ,o.CustomerID
  ,o.BillingAddressID
  ,o.OrderDate
  ,o.OrderStatus
  ,od.pro_id
  ,od.pro_name
  from Orders o
  INNER JOIN OrderDetails od
    on o.OrderNumber = od.OrderNumber
      where o.OrderNumber = 1

Without knowing your exact code workflow and customer journey it's hard to say what will work best. You will have to figure that out.

I'd definitely recommend on reading up about normalising your data so you can create the best data structure for your needs.

Related Question