WarpConduit Computing

  • Quick Tips
  • Web Development
  • WordPress Plugins
  • Home
  • Passphrase Generator
  • Password Generator
  • About
  • Contact

How to Change the Order Increment ID and Allow Very Large Order Numbers in OpenCart

May 4, 2012 by Josh Hartman

OpenCart Large Order Number Increment IDProblem

I recently found myself needing to set up various stores with very large custom order numbers to avoid conflicting order numbers when centrally processing orders from multiple stores.

Background Information

When OpenCart is installed the tables with order_id columns are setup as datatype INT(11) and by default these columns are signed, meaning they support negative or positive numbers (which doesn’t make any sense for order numbers). As an INT(11) SIGNED column it could contain order numbers up to 2147483647 and if the field was INT(11) UNISGNED it could then contain order numbers up to 4294967295. But for my administrative needs this was still not enough.

Solution

Note: The following method has been tested on OpenCart v1.5.2 only and I can’t say how it will behave on other versions.

Update (7/9/2014): This method does not work on OpenCart v1.5.4+. OpenCart is now converting the large order number into a smaller integer before it is written to the database thereby breaking this method.

First things first, you should take this opportunity to backup your OpenCart database in case something goes wrong.

Allowing Very Large Order Numbers

After eliminating the other integer data types I turned to the BIGINT UNSIGNED datatype which can contain order numbers up to 18446744073709551615.

To make use of this datatype you will need to run some SQL queries to alter some columns in the database schema. If your install has a table prefix setup adjust the queries as necessary.

ALTER TABLE `affiliate_transaction` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `coupon_history` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `customer_reward` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `customer_transaction` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `order_download` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order_fraud` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `order_history` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order_option` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order_product` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order_total` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order_voucher` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `return` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `voucher` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `voucher_history` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;

Setting the Order Increment ID

Now you can the set the order table’s order_id column AUTO_INCREMENT value to a non-negative number between 1 and 18446744073709551615. The SQL command for that follows:

ALTER TABLE `order` AUTO_INCREMENT = 9010003001;

Once again, if your install is using a table prefix, update the query as necessary.

Hopefully in the future the OpenCart developers will recognize that some need to use very large custom order numbers and adjust the install database schema so that we don’t have to hack the tables. But until then this is what works for me.

Filed Under: Web Development Tagged With: bigint, increment, mysql, number, opencart, order

Comments

  1. Hariom Sharma says

    May 30, 2012 at 1:27 AM

    Thanks you For this post it’s working for me

    • Josh Hartman says

      May 30, 2012 at 8:00 AM

      Great! A note to any future posters, please leave your OpenCart version number so we can see what versions this solution is compatible with.

  2. Sunny says

    June 13, 2012 at 1:39 AM

    If you modify the product_id can also use?

    int (11) → bigint (20)?

    I need EAN_13 encoding ~ thank you

    • Josh Hartman says

      June 15, 2012 at 8:17 PM

      The product_id field is an internal identifier, the best place for the EAN-13 is the UPC field on the product’s “Data” tab which corresponds to the product table and upc field. By default it is VARCHAR(12) but you can alter that easily.

      ALTER TABLE `product` CHANGE COLUMN `upc` `upc` VARCHAR(20) NOT NULL;

      I’d recommend that you create a development install and do some testing.

  3. John says

    September 7, 2012 at 6:26 PM

    Thanks Josh, this post saved me from having to reinvent the wheel. This might be a candidate for a future module.

  4. Prashant says

    September 12, 2012 at 6:25 AM

    We are doing the same on opencart but query becomes very slow any suggestions?
    The page can be found on
    http://www.diamondsinafrica.com/search-for-gia-certified-diamonds.php
    it takes 50 sec. to load the data
    before we got primary key converted to bigint (originally it was int) query use to take 5-7sec.
    The query is not just; just the data type is changed

    • Josh Hartman says

      September 12, 2012 at 8:28 AM

      Changing the column data type will not cause this. Review and troubleshoot your PHP code to find out exactly which piece of code is creating the delay. You can also backup your database and switch back to INT if you feel that is really the issue.

  5. Sayantan Das says

    June 27, 2014 at 12:26 AM

    How to implement in version 1.5.4.1?

    • Josh Hartman says

      July 9, 2014 at 9:23 PM

      Unfortunately there are too many places in OpenCart that would need modification from (int)$order_id to (float)$order_id in order to make this method work. So for now I don’t have a good solution. Hacking a bunch of core files doesn’t qualify, perhaps a vQmod could be created.

Connect

  • Facebook
  • GitHub
  • RSS
  • Twitter
  • YouTube

Recent Posts

  • How to Permanently Remove the “Learn about this picture” Spotlight Wallpaper Icon From Your Windows Desktop
  • How to Quickly Test a Fax Machine
  • Extremely Useful Applications for Web Development and IT Tasks

Tags

automatic benchmark bigint class composer css embed escape event font function gzip helper htaccess html htmlspecialchars image increment javascript jquery list magento media mysql number observer opencart order output photo php profiling random redirect rijndael software text type ubuntu url windows windows 7 wordpress xampp xss

Blogroll

  • CodeIgniter
  • Fusion Forward
  • jQuery
  • Nettuts+
  • Smashing Magazine

© 2025 WarpConduit Computing. All Rights Reserved.