Change starting order ID

[sb12759]

Can someone tell me how I would change the starting order ID so it doesn't start at 1?

[straightlight]

With an empty order table and if you do not have any orders involved on your store yet, you could change the auto-incrementation setting from PHPMyAdmin to a higher value when re-creating the table.

[sb12759]

thanks - can you tell me how I would do that exactly?

[straightlight]

Execute the following in your PHPMyAdmin - > your opencart database - > SQL Tab:

Code: Select all

DROP TABLE IF EXISTS `oc_order`;
CREATE TABLE `oc_order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_no` int(11) NOT NULL DEFAULT '0',
  `invoice_prefix` varchar(26) COLLATE utf8_bin NOT NULL,
  `store_id` int(11) NOT NULL DEFAULT '0',
  `store_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `store_url` varchar(255) COLLATE utf8_bin NOT NULL,
  `customer_id` int(11) NOT NULL DEFAULT '0',
  `customer_group_id` int(11) NOT NULL DEFAULT '0',
  `firstname` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `lastname` varchar(32) COLLATE utf8_bin NOT NULL,
  `email` varchar(96) COLLATE utf8_bin NOT NULL,
  `telephone` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `fax` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `payment_firstname` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `payment_lastname` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `payment_company` varchar(32) COLLATE utf8_bin NOT NULL,
  `payment_company_id` varchar(32) COLLATE utf8_bin NOT NULL,
  `payment_tax_id` varchar(32) COLLATE utf8_bin NOT NULL,   
  `payment_address_1` varchar(128) COLLATE utf8_bin NOT NULL,
  `payment_address_2` varchar(128) COLLATE utf8_bin NOT NULL,
  `payment_city` varchar(128) COLLATE utf8_bin NOT NULL,
  `payment_postcode` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',
  `payment_country` varchar(128) COLLATE utf8_bin NOT NULL,
  `payment_country_id` int(11) NOT NULL,
  `payment_zone` varchar(128) COLLATE utf8_bin NOT NULL,
  `payment_zone_id` int(11) NOT NULL,
  `payment_address_format` text COLLATE utf8_bin NOT NULL,
  `payment_method` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
  `payment_code` varchar(128) COLLATE utf8_bin NOT NULL,
  `shipping_firstname` varchar(32) COLLATE utf8_bin NOT NULL,
  `shipping_lastname` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `shipping_company` varchar(32) COLLATE utf8_bin NOT NULL,
  `shipping_address_1` varchar(128) COLLATE utf8_bin NOT NULL,
  `shipping_address_2` varchar(128) COLLATE utf8_bin NOT NULL,
  `shipping_city` varchar(128) COLLATE utf8_bin NOT NULL,
  `shipping_postcode` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',
  `shipping_country` varchar(128) COLLATE utf8_bin NOT NULL,
  `shipping_country_id` int(11) NOT NULL,
  `shipping_zone` varchar(128) COLLATE utf8_bin NOT NULL,
  `shipping_zone_id` int(11) NOT NULL,
  `shipping_address_format` text COLLATE utf8_bin NOT NULL,
  `shipping_method` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
  `shipping_code` varchar(128) COLLATE utf8_bin NOT NULL, 
  `comment` text COLLATE utf8_bin NOT NULL,
  `total` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `order_status_id` int(11) NOT NULL DEFAULT '0',
  `affiliate_id` int(11) NOT NULL,
  `commission` decimal(15,4) NOT NULL,
  `language_id` int(11) NOT NULL,
  `currency_id` int(11) NOT NULL,
  `currency_code` varchar(3) COLLATE utf8_bin NOT NULL,
  `currency_value` decimal(15,8) NOT NULL DEFAULT '1.0000',
  `ip` varchar(15) COLLATE utf8_bin NOT NULL,
  `forwarded_ip` varchar(15) COLLATE utf8_bin NOT NULL,
  `user_agent` varchar(255) COLLATE utf8_bin NOT NULL,
  `accept_language` varchar(255) COLLATE utf8_bin NOT NULL,
  `date_added` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=xxxxx;


Change: xxxxx to your preference and oc_ to your real table prefix name if differed from your installation.

[sb12759]

I appreciate you trying to help, unfortunately this is too advanced for me! So there isn't any other easier way to change the order ID number starting point?

Thanks!

[straightlight]

This is the only way without having to reassign a new field associated with the order ID in order to sort their orders.

[rph]

Code: Select all

ALTER TABLE `order` AUTO_INCREMENT = 1000;

[sb12759]

Code: Select all

ALTER TABLE `order` AUTO_INCREMENT = 1000;

Sorry, I'm not really a programmer. What would I do with this code? were does it go?

THanks!

[rph]

Run it in the OpenCart database in phpMyAdmin.

[sb12759]

OK - I'm finally getting round to doing this. Bear with me as I'm not too familiar with doing this sort of thing.
I logged into phpMyAdmin on my webhost control panel. Could you give me the steps from here?
Click on the database name on the left side - right? Then what?

Isn't there a way to just edit the last order number (right now it is 12) to lets say 5001? Then it would continue order numbers from there?
Really appreciate your help!

Steve

[victorj]

if you change the last ordernumber to whatever suits you the enxt will be counted from there on so if you change to 5000 the next wil be 5001

[avvici -arvixe-]

if you change the last ordernumber to whatever suits you the enxt will be counted from there on so if you change to 5000 the next wil be 5001

This is not actually always true. If there are deleted records then it will roll back The idea behind auto inc is to make a "unique" number.

[rph]

I logged into phpMyAdmin on my webhost control panel. Could you give me the steps from here?
Click on the database name on the left side - right? Then what?

Click on the tab that says SQL on the right-hand side, paste the query in the input box, and press "Go".

[sb12759]

I logged into phpMyAdmin on my webhost control panel. Could you give me the steps from here?
Click on the database name on the left side - right? Then what?

Click on the tab that says SQL on the right-hand side, paste the query in the input box, and press "Go".

So if the last order was 12 would it then go to 1012 and then 2012 etc? What do I do after I get to a number that I want to continue in normal succession from? I need to change it back right?

Thanks!

[rph]

1000 is just the starting number. Orders will keep incrementing by 1.

[sb12759]

Code: Select all

ALTER TABLE `order` AUTO_INCREMENT = 1000;

So I tried it and this is what I got:

#1146 - Table 'modolivi_pnc1.order' doesn't exist

(modolivi_pnc1) is the name of my database

Any ideas?

Should it be

Code: Select all

ALTER TABLE `order_id` AUTO_INCREMENT = 1000;

? Could that be the problem?

[rph]

No. You're probably in the wrong database.

[sb12759]

It was the right database. I looked around a little bit and I see the list of tables. All of them are preceded with a "oc_" - so the one that has the orders was oc_order.
I went ahead and figured out how to open up the oc_order table and then I edited the last order and changed the order number to 5000 and then saved it! And that seemed to work! (it just seemed like that would be the logical and simplest thing to do) I did a couple more test orders and they seemed keep numbering from there - 5001, 5002 etc.
Do you see anything wrong with doing this?
Thanks for all your help. I appreciate it!

[avvici -arvixe-]

Nothing wrong with that. To understand what I was saying above, and what everyone else has been talking about just go to the source and read

http://dev.mysql.com/doc/refman/5.0/en/ ... ement.html

That tells you everything you need to know including how to do what you want.

[rph]

It was the right database. I looked around a little bit and I see the list of tables. All of them are preceded with a "oc_" - so the one that has the orders was oc_order.

If your table has prefixes then the query needs to be modified appropriately.