Sales Order Page, and "hitting the Back Button"

[StevenOliver]

When I place merchandise order on other websites, and I make a mistake, I like being able to "go back" and make changes (e.g. "oops I want to buy two widgets instead of one").

I would like my website to also allow "going back" to correct mistakes!

Problem: orders are assigned a Unique Order Number and get stored in a mySQL database with the Order Number as primary key!

This seems to work. Am I doing this right?

<?php
session_start();
if( ! isset($_SESSION["OrderNumber"] ) ) {
session_start();

// Creates Unique Order Number in mySQL:
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$OrderNumber = date('njis');
$stmt = $db->prepare("INSERT INTO OrderNumberDatabase (OrderNumber) VALUES (?)");
$stmt->bind_param('s', $OrderNumber);
$ok = 0;
do {
    try {
        $stmt->execute();
        $ok = 1;
    }
    catch (mysqli_sql_exception $e) {
        $OrderNumber++;
        $ok = 0;
    }
} while (!$ok);

// Unique Order Number generated and saved. Now register it in Session:
$_SESSION["sessionOrderNumber"]=$OrderNumber;
}

At Order Completion ("Thank You For Your Order!"), this unique Order Number as well as ALL other data (merchandise data, sales price, time and date, etc.) are inserted into a completely separate mySQL database.

This seems to work. I can hit back button and make changes all day long, and my final database has the desired result of  just ONE order, not a gazillion duplicated error orders.
 
Questions:
1.) Is this the best way to do this?
2.) Anything redundant?
3.) Any loopholes I'm not seeing?

Thank you.

 

[Barand]

It certainly isn't the best algorithm for generating a unique value in the first place. Consider

 $d1 = new DateTime('2018-01-11 09:30:45');
 echo $d1->format('njis') . '<br>';                       //--> 1113045
 
 $d2 = new DateTime('2018-11-01 15:30:45');
 echo $d2->format('njis') . '<br>';                       //--> 1113045
 

Edited October 29, 2018 by Barand

[requinix]

The unique identifier for the order data should be an auto-generated value from the database. As in auto_increment. If you want to present something friendly to the user that needs to be separate.

[StevenOliver]

12 hours ago, Barand said:

It certainly isn't the best algorithm for generating a unique value

Good point, thank you. I was hoping the "catch and mysqli_sql_exception" portion of the code would take care of the unique value... but I know  it is not a "best practice" to rely on code to fix sloppy coding :-)  

So I'll get to work on that and fix it.

I revised my code a bit. What are your thoughts? Is it as efficient as it can be?

<?php
session_start();
if( ! isset($_SESSION["OrderNumber"] ) ) {

// Creates Unique Order Number in mySQL:
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$OrderNumber = $auto_incremented_number_from_database;
$stmt = $db->prepare("INSERT INTO OrderNumberDatabase (OrderNumber) VALUES (?)");
$stmt->bind_param('s', $OrderNumber);
$ok = 0;
do {
    try {
        $stmt->execute();
        $ok = 1;
    }
    catch (mysqli_sql_exception $e) {
        $OrderNumber++;
        $ok = 0;
    }
} while (!$ok);

// Unique Order Number generated and saved. Now register it in Session:
$_SESSION["sessionOrderNumber"]=$OrderNumber;
} else {
$OrderNumber = $_SESSION["sessionOrderNumber"];
}

 

Edited October 30, 2018 by StevenOliver

[Barand]

You get the auto_incremented id *after* inserting the record by calling the $db->insert_id() function to get the id of the last inserted record

[StevenOliver]

5 hours ago, Barand said:

...by calling the $db->insert_id() function

I had no idea about this function. I just now added an auto_increment column to my Order ID table to make this work.

Problem: My Prepared Statement already increments $orderid++ on mySQL error, so I can't logically figure out where to put the insert_id() function.

Where would it go and how do I append the auto_increment number?

(I spent all morning trying to figure this out so I wouldn't have to ask...)

[Barand]

What is your table structure now?

[Barand]

You want to keep it secret? OK, you don't have to answer our questions, we don't have to answer yours.

Suppose you have

CREATE TABLE `order` (
  `ordernumber` int(11) NOT NULL AUTO_INCREMENT,
  `orderdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `cust_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`ordernumber`)
)

Then running this code three times

$db->query("INSERT INTO orders (ordernumber) VALUES (NULL)");
$orderNumber = $db->insert_id;

echo $orderNumber;       // --> 3

and the table now contains

+-------------+---------------------+---------+
| ordernumber | orderdate           | cust_id |
+-------------+---------------------+---------+
|           1 | 2018-10-30 20:13:20 |    NULL |
|           2 | 2018-10-30 20:14:02 |    NULL |
|           3 | 2018-10-30 20:15:24 |    NULL |
+-------------+---------------------+---------+

Good luck.

[StevenOliver]

5 hours ago, Barand said:

You want to keep it secret?

Sorry for the delay, had to take a break from computer to help a neighbor prepare for Halloween. But yes my table structure is very very secret.... I'll share it only with you if you promise not to tell anyone: 

mysql> explain orderid;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| orderid | varchar(15)      | NO   | UNI |         |                |
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

So, that $db->insert_id thing of yours is definitely cool! ...I'm beginning to suspect that you probably know of a one-liner function that could replace my entire website of code :-)

Thank you again for helping me with this code. I might shift some stuff around though, something clever with order numbers like $orderid .= pow($db->insert_id,3);

 

Edited October 31, 2018 by StevenOliver