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

Posted on 2006-07-13
Medium Priority
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

by:Cornelia Yoder
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');
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!

LVL 27

Expert Comment

by:Cornelia Yoder
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 500 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 51

Assisted Solution

by:Steve Bink
Steve Bink earned 500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

750 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