StevenOliver Posted October 29, 2018 Share Posted October 29, 2018 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. Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2018 Share Posted October 29, 2018 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 Link to comment Share on other sites More sharing options...
requinix Posted October 29, 2018 Share Posted October 29, 2018 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. Link to comment Share on other sites More sharing options...
StevenOliver Posted October 30, 2018 Author Share Posted October 30, 2018 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"]; } Link to comment Share on other sites More sharing options...
Barand Posted October 30, 2018 Share Posted October 30, 2018 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 Link to comment Share on other sites More sharing options...
StevenOliver Posted October 30, 2018 Author Share Posted October 30, 2018 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...) Link to comment Share on other sites More sharing options...
Barand Posted October 30, 2018 Share Posted October 30, 2018 What is your table structure now? Link to comment Share on other sites More sharing options...
Barand Posted October 30, 2018 Share Posted October 30, 2018 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. Link to comment Share on other sites More sharing options...
StevenOliver Posted October 31, 2018 Author Share Posted October 31, 2018 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); Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.