Jump to content

MySQL query to bulk cancel a product.


Recommended Posts

Hi, unfortunately WHMCS doesn't come with any basic bulk operations that can be actioned on product search results (apart from to email the client).

I'm looking for the correct MySQL  query syntax to set all instances of a particular product to cancelled.

I have a product (with ID 16) and customers have purchased this product 260 times. I want to set them all to cancelled. You'd think that the product search options in WHMCS would allow you to bulk select and change status - but no. After over a decade, simple stuff like this is still not included :-(

Rather than manually set each one to cancelled, I was hoping someone here with good WHMCS database knowledge would be so kind as to help me out with the correct MySQL query to do this.

As mentioned, the product ID (packageid) is 16 and the product is in the "other product / service" category, although I think these are still stored within the tblhosting table. I'm also  little confused because the status field appears to be called "domainstatus" in the database which seems a little odd!

So I think I need to update the tblhosting table and set  the domainstatus field to "cancelled" where the packageid is 16.

Any help much appreciated.



Link to comment
Share on other sites

A crude resolution, would be to use UpdateClientProduct API which requires ServiceID, which can be gathered (even more crudely), by using the Reports in WHMCS Admin to generate the ServiceIDs of all products that are active with your desired ProductID. 

From here, you can Concatenate the API calls by inserting the unique ServiceIDs into your API calls. This would allow you use the Status parameter to modify all 260 status' to "Cancelled". 

Obviously, this is really only suitable for one-off situation where you have 260 products to cancel and not an ongoing requirement for these status changes based upon any other outside factors.


I hope someone smarter than me can provide the MySQL query that magically solves the issue!

Link to comment
Share on other sites

Thanks for your reply. It wasn't the query as much as a confirmation of the correct fields. Brian came to my rescue and confirmed what I thought.

UPDATE tblhosting
SET domainstatus = 'Cancelled'
WHERE packageid = 16;


Worked perfectly.

Strange that they chose "domainstatus" as a field name for products.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated