[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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');
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Industry Leaders: 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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

656 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