Add a new field in database and increment by 1 with each new order

I used Dreamweavers Cartweaver extension to create a shopping cart. It created database and all necessary php files.  
It is working but I have a few problems.
It generated a very long order id using many different data such as a prefix (char) customer id , order etc. The autogenerated order_id is a long number including characters and digits. Same order_id is used on invoices and displaying the order.

I want the order number a simple 4 digit number.
I want to add a new field "Order Number" and based on every new Order Id generated. I want the new order number a 4 digit number, which increments by 1 with each new order.
This order number can be used to print on invoices. The autogenerated Order ID can be hidden on the invoice.

I am new to pHp and have just working knowledge.

Please help me create a new field in the order processing and increment it with 1 with each new order.
thank you
Who is Participating?
Kshitij AhujaConnect With a Mentor Technology DeveloperCommented:
Oops !

Sorry, i think order_id is the one u r currently using to store the long id generated. Better remove it if  u dnt need such long  ids.

Also if u need a to generate a small length id with digits, then let me know, other wise continue with what u planned. The four digit id.

Kshitij AhujaTechnology DeveloperCommented:
Hi Soniya,

I think its not a big game. If you really wish to hide the actual long order_id,and use a 4 Digit id to display in the invoices, you can just add a new field in the database with datatype "integer" and set the value to auto-increment. If you wish to preset the value to a particular number and begin orders after that, just enter that value in the dbase. For example instead of starting from 0001 you might prefer to start at 5623 or any other number( of 4 or lesser digits). To do so,just add the number in the dbase manually and the it will increment on its own.

Let me explain you how will this work :
1. You create a new dbase field with int data type, and set it to auto increment.
2.  Whenever a new order is generated [this will be done using the shopping cart u have made], the value of 4 digit order id field is incremented automatically in the dbase by 1.

I hope this serves your query,


Cornelia YoderArtistCommented:
Just to add to what kshitij_ahuja said ...

Add a field to your database called OrderID (or whatever) with attributes


When your code inserts a new row into the database, use the MySQL query

INSERT INTO TableName (....., OrderID, ......)  values(......, '', ......)

where the ..... represent any other fields that you wish to specify.

That is, use two single quotes for the value of OrderID in the INSERT statement.  (It isn't necessary to specify the field, but if you're like me and despise letting the system default values for you, that's how you do it.)  This will add 1 to the highest existing value in that field in the database.

Then follow the mysql_query immediately with the php function mysql_insert_id( ) to retrieve the newly generated auto_increment value.

$orderid = mysql_insert_id( )

will retrieve the value of the newly inserted OrderID.


mysql_query("INSERT INTO TableName (....., OrderID, ......)  values(......, '', ......);");
$orderid = mysql_insert_id( ) ;

will insert a new row into the database and set the php variable $orderid to the value of the newest OrderID.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

soniyaAuthor Commented:
Can you please help me add this new field? I tried adding new filed but I get error by selecting auto-increment . The error says that there can be only one field with autoincrement value. I am trying to add
order_num INT

Here is the table structure:
-- Table structure for table `tbl_orders`

CREATE TABLE `tbl_orders` (
  `order_ID` varchar(75) NOT NULL default '',
  `order_TransactionID` varchar(50) default NULL,
  `order_Date` datetime default NULL,
  `order_Status` int(11) default NULL,
  `order_CustomerID` varchar(50) NOT NULL default '',
  `order_Tax` double default NULL,
  `order_Shipping` double default NULL,
  `order_Total` double default NULL,
  `order_ShipMeth_ID` int(11) default NULL,
  `order_ShipDate` datetime default NULL,
  `order_ShipTrackingID` varchar(100) default NULL,
  `order_Address1` varchar(125) default NULL,
  `order_Address2` varchar(50) default NULL,
  `order_City` varchar(100) default NULL,
  `order_State` varchar(50) default NULL,
  `order_Zip` varchar(50) default NULL,
  `order_Country` varchar(75) default NULL,
  `order_Notes` mediumtext,
  `order_ActualShipCharge` double default NULL,
  `order_ShipName` varchar(75) default NULL,
  PRIMARY KEY  (`order_ID`),
  KEY `order_ShipTrackingID` (`order_ShipTrackingID`),
  KEY `OrdersCustomerID` (`order_CustomerID`),
  KEY `OrdersOrderID` (`order_ID`),
  KEY `OrdersShippingMethodID` (`order_ShipMeth_ID`),
  KEY `tbl_Customerstbl_Orders` (`order_CustomerID`),
  KEY `tbl_ShipMethodtbl_Orders` (`order_ShipMeth_ID`)

-- Dumping data for table `tbl_orders`

INSERT INTO `tbl_orders` VALUES ('cw44b61b36431eb', '1961367', '2006-07-13 05:06:46', 1, 'cw665b00cc6', 0, 8, 59.9, 35, NULL, NULL, 's', '', 's', 'SC', 's', 'USA', NULL, NULL, 's s');
Cornelia YoderArtistCommented:
I don't see an auto_increment in that list, but check the table that exists in your database and make sure there isn't already a field that is auto_increment.  If there is, see if you need it, or could use it for your order_num.

How did you try to add the field?  By recreating the table or by adding it though something like phpmyadmin?
Kshitij AhujaTechnology DeveloperCommented:
yodercm is right, there is not auto_increment in the above dump.

I think your order_id should be the one u need to use for 4 digit numbers and this needs to be set to auto increment.

In my personal view, you should delete the long id generatd by script that dnt intend to use. Simply because it will save a lot of space in the database plus may be a speed up the execution of query a little.

Steve BinkConnect With a Mentor Commented:
From MySQL docs:

Note: There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT  value. As of MySQL 3.23, an AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.

The problem is that you are creating the field, but not indexing it.  Take a look here for a proper example.  It is shown a couple paragraphs above the user comments.  Search for "AUTO_INCREMENT" when you get to the page:

Note that their example shows the creation of a primary key based on the autonumber field.  Theoretically, it should work when you only add an index.  My understanding was that the autonumber was REQUIRED to be the PK, but the docs note above says differently.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.