X-Cart forums - HERE is how to change invoice numbering with MYSQL

X-Cart forums (https://forum.x-cart.com/index.php)

- Dev Questions (https://forum.x-cart.com/forumdisplay.php?f=20)

- - HERE is how to change invoice numbering with MYSQL (https://forum.x-cart.com/showthread.php?t=13213)

HERE is how to change invoice numbering with MYSQL

I was trying to find out how to change the invoice numbering. The data for invoice numbers is stored in your my sql database.

Unforch, all the posts on how to change the invoice numbering said to use phpMyAdmin. It's a great program, but installng it or figuring out how to use it can be as much work as making this change by telnetting in and making the change to mysql that way.

Soooooooo, I saved all my work on how to just telnet in and I'm sharing it with anyone who wants it.

This is how to change invoice numbering very easily. It assumes you know how to telnet into your host. If you don't know, I'm sure there is info in this forum.

BTW, I did this with fake orders during development so as to play it safe. And as always, when monkeying around, backup your mysql database in case something goes wrong.

--------------
Us the telnet program of your choice to connect to your web host. Log in. Once you are successfully logged in, you will access your mysql database via command-line prompts in telnet.

(letters in ALL CAPS below should be substituted with your own user info)

1. get into mysql
at the prompt, enter:

Code:

mysql -h HOSTNAME -u USERNAME -p

the shell will reply with

Code:

Enter password:

enter your password for your mysql database

the prompt will now look like this:

Code:

mysql>

you want to tell it which database to use, so enter the command

Code:

use YOURDATBASENAME;

All you want to do is change the number of the last (highest numbered) order in your system to whatever you want to start the invoice numbers at.
I didn't want them to read 1, 2, 3. I like to have them start at 50000, with the 5 representing 2005. In 2006, I will reset the invoice order to 60000.

So, first, we want to see the orderids already in mysql, so issue the command

Code:

select orderid from xcart_orders;

for me it returned: (your data will probably be different)

Code:

+---------+

| orderid |

+---------+

|       1 |

|       2 |

|       3 |

|       4 |

|       5 |

|       6 |

|       7 |

|       8 |

|       9 |

|      10 |

+---------+

10 rows in set (0.00 sec)

This shows me the orderid info stored in the table "xcart_orders." There are 10 orders in the database, with invoices numbered 1 through 10.

Invoice numer 10 is the last one in there, and that's the only one I want to change. So I told mysql to add 50000 to the orderid number, when the orderid number = 10. (This will change order 10 to 50010 and leave the other ones alone.)

the command to do that was:

Code:

update xcart_orders set orderid=orderid+50000 where orderid=10;

Now, if we ask to see the orderid data again,we issue the command:

Code:

select orderid from xcart_orders;

it says:

Code:

+---------+

| orderid |

+---------+

|       1 |

|       2 |

|       3 |

|       4 |

|       5 |

|       6 |

|       7 |

|       8 |

|       9 |

|   50010 |

+---------+

10 rows in set (0.00 sec)

So, the next order that is placed will be 50011. Ba bing.

I hope this makes sense and saves someone some time.

This works too ;)

Code:

ALTER TABLE xcart_orders AUTO_INCREMENT = xxxxxx;

and you can do all this without shell or phpmyadmin just by entering the sql command into the sql part of the patch upgrade page in your admin

How exactly is it done step by step? (reset order numbering to a desired figure). I prefer to do this from X-Cart admin or from "phpmyadmin".
I have no idea what mysql, php etc. is.

Thanks for your help!

As Jon said above, just enter this command into the SQL Queries section of the Patch/Updgrade area of your admin:

ALTER TABLE xcart_orders AUTO_INCREMENT = xxxxxx;

Replace xxxxx with the number of the invoice.

Ryan, thanks. I saw several posts on this matter and I saw this one, but I was not sure where exactly to go on which program. Everyone talks about "mysql" and I don't have mysql. I found "phpmyadmin" on my Plesk and I remember somehow digging into it when I had another cart and being able to reset (edit, update, change, customise) the orderid (order number), but in X-Cart's phpmyadmin it's just not there. I tried everything in phpmyadmin and nothing worked. Also I saw a post with a long 4-5 line code to be entered somewhere on X-Cart Admin area and then he posted also another code how to remove it. Now a question: after I enter this code (if I find where to enter it) will I have to remove it with another code after a next order is placed?

P.S. Do I have to also include the ; sign after the x'es?

Thanks again.

You do have MySQL, that is the database type. X-Cart doesn't run without it! :) PHPMyAdmin is the interface tool used to edit a MySQL database.

Yes, enter that statement exactly as posted, with the ;

You don't have to change anything, this simply sets the next invoice number.

Error: perl executable is not found or patch.pl has no right

Now I went to "Patch/Upgrade" section and I'm getting the following red text warning right away by just going there, not doing anything:

Patch/Upgrade center

Error: perl executable is not found or patch.pl has no rights to execute. In that case you cannot use patch/upgrade code of X-Cart.

You need to specify the pearl path in General Settings and make sure patch.pl has the correct permissions.

E:\perl\bin\perl.exe - no difference

I found some info on the forum about it and entered:

E:\perl\bin\perl.exe

into the empty field under "General Settings/General Options"

The full path to Perl interpreter executable (e.g. WIN32: 'd:\perl\bin\perl.exe'; UNIX: '/usr/bin/perl' or leave this field empty): (was empty, then I entered E:\perl\bin\perl.exe)

... but nothing, so I cleaned the field again. There seems to be no solution? I wonder what X-Cart providers say about this issue?