PHP mySQL logic puzzle...

[StevenOliver]

My mind grasps it for a few seconds, then I lose it. Here's my question:

My site will has a max of 1000 sales a day. On the "sale confirmation page" each customer is issued an order number "month day year minutes and seconds" (e.g. "927180516") which gets inserted into mySQL database using "insert into database (ordernumber) values ('927180516')".

Because theoretically 2 sales could happen simultaneously I want to avoid 2 order numbers being the same!

Are there flaws in this? Is this too slow?

$conn=mysqli_connect("localhost","username","password","database");

$ordernumber = date("njis");
$num = false;
while ($is_unique == false){
$query  = "SELECT * from database where ordernumber = '$ordernumber'";
    $result =  $conn->query($query);
    if (!mysqli_fetch_assoc($result)){
        $is_unique = true;
    } else {
    $ordernumber = $ordernumber + 1;
   }
}
// $ordernumber is now unique. Insert into the database:
insert into database (customer,ordernumber) values ('fred','$ordernumber);

// Now that $ordernumber is inserted into database, display success to customer:
echo "SUCCESS! Thank you for your order! Your Ordernumber is ".$ordernumber;

 

 

 

[ginerjm]

You could use an autoincrement column in your table instead of a date value.  Add the date of the order as a new column and actually store the order datetime there.

[StevenOliver]

Thank you, but I cannot use autoincrement because a clever competitor could see how many sales per day I get (they would 'create an order' at the beginning of the day -- ordernumber 9275341000 -- and then create another order at the end of the day -- 9275341005 -- and see I had 5 orders that day :-)

My question was are there any flaws or is there a faster alternative to this code:

$conn=mysqli_connect("localhost","username","password","database");

$ordernumber = date("njis");
$num = false;
while ($is_unique == false){
$query  = "SELECT * from database where ordernumber = '$ordernumber'";
    $result =  $conn->query($query);
    if (!mysqli_fetch_assoc($result)){
        $is_unique = true;
    } else {
    $ordernumber = $ordernumber + 1;
   }
}
// $ordernumber is now unique. Insert into the database:
insert into database (customer,ordernumber) values ('fred','$ordernumber);

// Now that $ordernumber is inserted into database, display success to customer:
echo "SUCCESS! Thank you for your order! Your Ordernumber is ".$ordernumber;

 

 

 

 

 

 

 

[Barand]

You should put a UNIQUE INDEX on order number which would give an error if you attempt a duplicate insert. You would then trap these duplicate key errors and issue another number.

You should be storing the customers ID, not the name.

You should be using using prepared statements and not putting values directly into the query.

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$conn = mysqli_connect(HOST,USERNAME,PASSWORD,DATABASE);
    
$cust = 'fred' ;
$orderno = '123456789' ;

$stmt = $conn->prepare("INSERT INTO orders (customer, order_number) VALUES (?,?)");
$stmt->bind_param('ss', $cust, $orderno);
$ok = 0;
do {
    try {
        $stmt->execute();
        $ok = 1;
    }
    catch (mysqli_sql_exception $e) {
        $orderno++;
        $ok = 0;
    }
} while (!$ok);

 

Edited September 27, 2018 by Barand

[ginerjm]

The autoincrement number would simply be a unique key field.  The order number could then be anything you want it to be.  The ai number would never be revealed to the user if you had things such as customer numbers, order numbers, and order dates and times and statuses to use as user-oriented and app-oriented identifiers. 

You could also use +10 as an increment on new order numbers.

[StevenOliver]

1 hour ago, ginerjm said:

The autoincrement number would simply be a unique key field....The ai number would never be revealed

Thank you, but I'm sorry I'm confused....

If I got an email from a customer saying "Look up my ordernumber 9271000" and I looked into my mySQL records, I would see this:

| customer | ordernumber | id
| Fred    | 9271000   | 1 |
| Joan    | 9271999   | 2 |
| Paul    | 9271000   | 3 | <-- note the duplicate ordernumber
| Kris    | 9285412   | 4 |
| Suzy    | 9296547   | 5 |

I would not know which of the two order numbers it is.

Is this what you mean?

Edited September 27, 2018 by StevenOliver
clarity

[StevenOliver]

1 hour ago, Barand said:

You should put a UNIQUE INDEX on order number...You would then trap these duplicate key errors and issue another number.

You should be storing the customers ID, not the name.

You should be using using prepared statements and not putting values directly into the query.

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$conn = mysqli_connect(HOST,USERNAME,PASSWORD,DATABASE);
    
$cust = 'fred' ;
$orderno = '123456789' ;

$stmt = $conn->prepare("INSERT INTO orders (customer, order_number) VALUES (?,?)");
$stmt->bind_param('ss', $cust, $orderno);
$ok = 0;
do {
    try {
        $stmt->execute();
        $ok = 1;
    }
    catch (mysqli_sql_exception $e) {
        $orderno++;
        $ok = 0;
    }
} while (!$ok);

 

OMG I  tried this code -- it "works right out of the box" this seems EXACTLY what I want!!

It seems the "mySQL catch exception" gets triggered when a duplicate key tries to be inserted into a UNIQUE field -- therefore this code is both automatic and efficient! (And, the code just "looks cool!" :-)

Last question: in theory, 2 customers could click their respective "Submit" buttons at the same time thus causing my mySQL server to simultaneously trigger and "catch exception" and "increment" and turn into an endless race to finish. Is it therefore recommended to "LOCK" the table precisely when the order number is created? Or is this ridiculous overkill?

 

 

[Barand]

The way you had it before (checking if the keys exists then adding if it doesn't) could result in a race condition. With unique keys, one will get written first causing the second to fail and retry.

[kicken]

5 hours ago, StevenOliver said:

If I got an email from a customer saying "Look up my ordernumber 9271000" and I looked into my mySQL records, I would see this  [...] I would not know which of the two order numbers it is.

You'd look up orders by first identifying the customer, then looking up the order number within that customers order's not your entire list of orders. A single customer having two orders at the same second should be highly unlikely/impossible.  You could ensure you can find the customer by presenting your order numbers as customerId-orderNumber on invoices.

The UNIQUE constraint is the ideal solution however as then you're having the database guarantee that there are no duplicates.

 

Edited September 27, 2018 by kicken