MySQL query to bulk cancel a product.

[Chris74]

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.

 

 

[RadWebHosting]

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!

[Chris74]

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.