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);
    $totalPrice = 0;
    $i = 0;
    while($svalue = $sresult->fetch_assoc()){
        $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);
            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.


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
  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.

  • 2 tables? then how can i merge them? Sep 2 '19 at 9:04
  • and how can i insert in two tables when user click on place order Sep 2 '19 at 9:34
  • @ZainMedia With two tables you can either make two requests. Once to get the order ID and main details followed by one to get the item details. Or you could make one single request using the JOIN clause. I'm not sure the example I gave saved properly so I'll update my answer with some extra details as it also shows the insert. There's no right or wrong way to do it. You know your user's journey but you can make as many SQL calls as you need in your WHILE loop.
    – KevH
    Sep 2 '19 at 9:39
  • yeah but now, there is 2 tables, order and order_details. I think i should not insert orders table in while loop, coz it will then insert multiple rows, in while loop, i should only insert order_details Sep 2 '19 at 9:44
  • Yes - creating the Order should happen outside of the loop.
    – KevH
    Sep 2 '19 at 9:48

Okay, As per our discussion, what you want is the same invoice number to, one order(one order can have multiple products but from one User).

So I think you need one table which can save the Invoice Number So when you are inserting into order table before that you fetch the Invoice Number and increment it, then you in order table


$squery = "SELECT * FROM cart WHERE user_id = '$user_id'";
$sresult = $this->db->select($squery);
    $totalPrice = 0;
    $i = 0;
    while($svalue = $sresult->fetch_assoc()){
 $query = "INSERT INTO orders(user_id, pro_id, invoice_no, pro_name, status, date) "
                . "VALUES('$user_id', '$pro_id', '$INV_NUM', '$pro_name', 'processing', NOW())";

This strategy might help you. Please let me know, it works or not?

  • No brother. This is not the answer. Kindly read my question carefully first Sep 2 '19 at 7:50
  • so what is going on, is there any entry in a database or not!?? Sep 2 '19 at 8:06
  • so what you need? one Invoice Id for multiple products purchased by one user ? Sep 2 '19 at 8:11
  • yes, same invoice id for multiple products ordered by once that we called it 1 order includes multiple products. mt_rand() function do it but generates number random. I wanna generate from 1 to so on, 1 by 1, Sep 2 '19 at 8:12
  • 1
    You also might want to research what SQL injections (first (on runtime) and second order (stored) ) are as your code seams to be prone on atleast one those kind of attacks if not both.. Sep 2 '19 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.