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

Posted on 2006-07-13
Last Modified: 2013-12-12
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
Question by:soniya
LVL 10

Expert Comment

by:Kshitij Ahuja
ID: 17097788
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,


LVL 27

Expert Comment

ID: 17098228
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.

Author Comment

ID: 17098317
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');
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

LVL 27

Expert Comment

ID: 17098407
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?
LVL 10

Expert Comment

by:Kshitij Ahuja
ID: 17098471
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.

LVL 10

Accepted Solution

Kshitij Ahuja earned 125 total points
ID: 17098487
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.

LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 125 total points
ID: 17112357
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question