Solved

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

Posted on 2006-07-13
10
242 Views
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
0
Comment
Question by:soniya
10 Comments
 
LVL 10

Expert Comment

by:kshitij_ahuja
Comment Utility
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,

-k-



0
 
LVL 27

Expert Comment

by:yodercm
Comment Utility
Just to add to what kshitij_ahuja said ...

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

OrderID INT NOT NULL AUTO_INCREMENT

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.

Thus,

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.
0
 
LVL 3

Author Comment

by:soniya
Comment Utility
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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- 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');
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 27

Expert Comment

by:yodercm
Comment Utility
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?
0
 
LVL 10

Expert Comment

by:kshitij_ahuja
Comment Utility
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.

-k-
0
 
LVL 10

Accepted Solution

by:
kshitij_ahuja earned 125 total points
Comment Utility
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.

0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 125 total points
Comment Utility
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:

http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
This article discusses four methods for overlaying images in a container on a web page
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 create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now