1

I have a system where users can order multiple items, lets say a user added 3 items to the cart and submit the order then every item gets inserted to the order table but order number is same for all these items. ex below.

order_id product_id    order_no 
10895    ACP1001       WKO00000003659 
10894    ACP1000       WKO00000003659 
10893    ACP1001       WKO00000003658 
10892    ACP1000       WKO00000003658

I am generating this order_no from the last inserted order number, for that I use my php script to generate the order number. Everything works perfectly except when there are simultaneous orders. If there are simultaneous orders from different users then the same order number is being assigned to all users. How can I overcome this situation?

Professor Abronsius
  • 33,063
  • 5
  • 32
  • 46
Rijo Joy
  • 335
  • 3
  • 6

2 Answers2

1

You can use session in Php for this. In session for each user will have order_id. You can also use cookies. You can easily maintain session variable for a user. This session variable contain the order_id value. what is latest order_id, store to database. when new user come then read that order id from database and increment it then assign to user. After update to database.

what is session-

When you work with an application, you open it, do some changes, and then you close it. This is much like a Session. The computer knows who you are. It knows when you start the application and when you end. But on the internet there is one problem: the web server does not know who you are or what you do, because the HTTP address doesn't maintain state.

Session variables solve this problem by storing user information to be used across multiple pages (e.g. username, favorite color, etc). By default, session variables last until the user closes the browser.

So; Session variables hold information about one single user, and are available to all pages in one application.

I am settng an example only. It may be differ for your case. 

 mysql > desc order_id_generate;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+

<?php
// Start the session
session_start();
?>
<!DOCTYPE html>
<html>
<body>

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

if(isset($_SESSION['order_id']) && !empty($_SESSION['order_id'])) {
   echo "Already ordered set and not empty ! ".$_SESSION[`order_id`];
}
else {
   $query = "INSERT INTO order_id_generate VALUES ()";
   $mysqli->query($query);
   $temp=$mysqli->insert_id;
   // set the new order
   $_SESSION['order_id']=$temp;
   echo "Ordered set and not empty ! ".$_SESSION[`order_id`];
}

$conn->close();
?>
</body>
</html>

more @ w3school and php mannual

Hitesh Mundra
  • 1,538
  • 1
  • 9
  • 13
  • this needs some help. It needs to define how the number is incremented and preventing a clash – Drew Oct 01 '15 at 06:37
  • Yes, the problem is incrementing the number , not storing in session. – Rijo Joy Oct 01 '15 at 06:42
  • definitely don't do that Hitesh. 9k users could run that line of code at once – Drew Oct 01 '15 at 06:54
  • @RIjoJoy i meant that maintained it for each user in sessions. – Hitesh Mundra Oct 01 '15 at 06:54
  • @Drew the order no is (string+int) combo so can't auto increment. And thats why i am doing one update command for single record table. – Hitesh Mundra Oct 01 '15 at 06:58
  • this is unsafe --> `$result = $conn->query($sql);` and this user needs to deal with a schema problem. Any column can have a derived value jammed in it, but you just gave all those users the same order_id if run at the same time – Drew Oct 01 '15 at 06:59
  • @Drew I am settng and example only. I am not php devloper. So i didn't understand your concern. Will you please elaborate the scenario ? – Hitesh Mundra Oct 01 '15 at 07:02
  • ok, 9k users run `$sql = "SELECT order_id FROM store_order_id"; $result = $conn->query($sql);` at the same time. What do you think is going to happen ? – Drew Oct 01 '15 at 07:03
  • @Drew it will read from table and store the record to $result variable – Hitesh Mundra Oct 01 '15 at 07:05
  • of course it will. all 9k users will have the same order_id – Drew Oct 01 '15 at 07:26
  • @Drew we can use `transection` and `locking` here. – Hitesh Mundra Oct 01 '15 at 07:29
  • but you aren't and you don't have to, by using auto_increment. Ok, I am moving on ! – Drew Oct 01 '15 at 07:29
  • 1
    @Drew transaction and locking is tuff process. Auto_increment is easy and i used this approch update my code example . Thanks – Hitesh Mundra Oct 01 '15 at 07:51
1

A user, any user, creates an order. The db inserts a row in an orders table that has something like order_id int auto_increment primary key guaranteed to be different from the next guy.

Under this order_id you hang all your item/product purchases in something like an orderlines table (the order details).

There is zero chance of it screwing up with duplicates at the order_id level.

Now, if you have to have some derived or otherwise custom other column with a WKO or some other prefix, that is possible too. But the main thing is that the order_id is secured from collision with others.

Read this for mysqli, read this for PDO.

Mysql Using AUTO_INCREMENT

Drew
  • 24,851
  • 10
  • 43
  • 78
  • this shows a little schema in action [here](http://stackoverflow.com/a/32313259/1816093), or [this](http://stackoverflow.com/a/32662924/1816093) one. Every question is a bit different – Drew Oct 01 '15 at 06:50
  • ok. for ex: the order ids 10895 ,10894 should have only one one order number 'WKO00000003659' how we can achieve that? – Rijo Joy Oct 01 '15 at 07:35
  • an order has a number. now explain to me what order_id is and what order_number is, in business speak, nothing more – Drew Oct 01 '15 at 07:37
  • order_id is auto incremented ids, order number is a specific format like 'WKO00000003659' – Rijo Joy Oct 01 '15 at 07:39
  • what you appear to need is to rip the WKO out of the primary key. I spoke about that above and way above in comments. Your order has a unique number. It is a number. It is not a string. In the detail table, it uses that order number. Back in the order table, if you want a column with a prefix string, do that. But that has nothing to do with auto_increment ints – Drew Oct 01 '15 at 07:39
  • So, orders table has an auto_inc int. Same for orderlines (the details). If you want the orders table to have an additional column varchar such as WKO in it, do that. – Drew Oct 01 '15 at 07:43
  • multiple order_ids associated with one order number. how can I handle that situation? – Rijo Joy Oct 01 '15 at 07:49
  • i just told you. moving on – Drew Oct 01 '15 at 07:49