WHMCS is popular software used to manage a web hosting business. It works, but it can be clunky and the company frustrates me with what appears to be a culture of denial about problems. I am posting this so that hopefully other WHMCS users can find a solution to a hair-tearing problem that the company hasn’t fixed despite it being brought to their attention.
The problem:
Now that there is about 500 domains available managing them and keeping them in any sort of meaningful order is close to impossible. Wholesale prices are regularly changing and maintaining just that can be tricky. The issue is that the domains are in some sort of random order, it seems to be based on the order in which they are added to the system. That is fair enough, there has to be some sort of default, but then re-ordering them should be easy, but it is a nightmare.
See the circled handle below? The way they have it set is that you have to grab that, one at a time, and drag it to the position you want the domain.
Imagine trying to do that for 500 domains that are not in alphabetical order but you want them to be. It would be hard enough if it was simply a long list, but in a browser where you have to drag and scroll at the same time…forget it.
A solution:
This solution is a bit complex and not for the faint of heart, but if you are running a server you should have the skills to be able to follow quite easily.
Firstly, and do I even need to say this, back up your data. We are going to do that anyway, but is it possible to have too many backups?
Jump into PHPmyAdmin open the table tbldomainpricing and export it as a CSV file. This is a backup, but we are also going to manipulate it, so make a copy if you want.
Open the table in a spreadsheet, I used Google Sheets because it is so easy. Once opened you will see that column A is the domain ID, Column B is the domain name and column H is the sort order.
The first thing I did was to order the sheet by Column B, A->Z as that is what I am after, an alphabetical list.
We can immediately see the scale of the problem, ideally, Column H should be sequential numbers, it is way off.
Fortunately with spreadsheets, filling a column with sequential values is trivially easy. Firstly put the number 1 in row 1 and 2 in row 2 like this. (They yellow is just highlighting).
Next select both row 1 and row 2. The easiest way is to hold the shift key and click H1 then H2 it will look like this
The important thing to note is the handle on the bottom right of the blue square. Grab it and drag it down, all the way to the bottom of the column…yeah it is tedious, but it is easy.
If you want to test, you can always drag it a little way and let it go to see the outcome.
Once you have dragged all the way to the bottom and released the handle your domains will be have the database order column (Column H) in sequential order, assuming the domain name column (Column A) was ordered alphabetically as well.
Now is your opportunity to bump any popular or featured domains to the top of the WHMCS list. Simply change the value in Column H to 0. You can have multiple identical valued domains, they will be kept in numerical order then ordered alphabetically. I am not going to elaborate on the logic here…if you aren’t following, you really shouldn’t be messing with databases.
We are ready to save the sheet or if you are in Google Sheets, you need to download it as a CSV – click file > download and in your browser you may have to click save file.
Now we are going back to PHPmyAdmin and the database. Most importantly, make sure you have that original CSV file as a backup or another way to restore your database.
Empty the tbldomainpricing table and import your CSV file, that should get your WHMCS domains in alphabetical order on your site.
Of course, it would be easier if WHMCS came up with a way to do this painlessly, and they might, but I won’t hold my breath when despite conceding the current process is difficult, their attitude is “If we receive feedback from other users in this vein then it will only help informing our prioritisation of such work.”
