mysql query to find all users have a service and service is cancel

[ambaha]

I want a mysql query to find all the users (email and phone number) that, for example, have one or more dedicated servers, and the status of their dedicated server is CANCELED or TERMINATED.

 

[pRieStaKos]

\WHMCS\Database\Capsule::table("tblhosting")
    ->join("tblclients", "tblhosting.userid", "=", "tblclients.id")
    ->select("tblclients.email", "tblclients.phonenumber")
    ->whereIn("tblhosting.domainstatus", ["Cancelled", "Terminated"])
    ->where("tblhosting.domain", "<>", "")
    ->whereIn("tblhosting.packageid", [2, 3]) # Add here your product ids
    ->get();

# The above is actually this query

SELECT `tblclients`.`email`, `tblclients`.`phonenumber`
FROM `tblhosting`
INNER JOIN `tblclients` ON `tblhosting`.`userid` = `tblclients`.`id`
WHERE `tblhosting`.`domainstatus` IN ("Cancelled", "Terminated")
  AND `tblhosting`.`domain` <> ""
  AND `tblhosting`.`packageid` IN (2, 3) # Add here your product ids

Edited February 14 by pRieStaKos

[ambaha]

5 hours ago, pRieStaKos said:

\WHMCS\Database\Capsule::table("tblhosting")
    ->join("tblclients", "tblhosting.userid", "=", "tblclients.id")
    ->select("tblclients.email", "tblclients.phonenumber")
    ->whereIn("tblhosting.domainstatus", ["Cancelled", "Terminated"])
    ->where("tblhosting.domain", "<>", "")
    ->whereIn("tblhosting.packageid", [2, 3]) # Add here your product ids
    ->get();

# The above is actually this query

SELECT `tblclients`.`email`, `tblclients`.`phonenumber`
FROM `tblhosting`
INNER JOIN `tblclients` ON `tblhosting`.`userid` = `tblclients`.`id`
WHERE `tblhosting`.`domainstatus` IN ("Cancelled", "Terminated")
  AND `tblhosting`.`domain` <> ""
  AND `tblhosting`.`packageid` IN (2, 3) # Add here your product ids

Thank you for your reply, I want  based on service ID, not based on product ID

[pRieStaKos]

10 hours ago, ambaha said:

Thank you for your reply, I want  based on service ID, not based on product ID

AND `tblhosting`.`id` IN (2, 3) # Add here your service ids

Replace `tblhosting`.`packageid` with `tblhosting`.`id`

[ambaha]

On 2/15/2024 at 1:28 PM, pRieStaKos said:

AND `tblhosting`.`id` IN (2, 3) # Add here your service ids

Replace `tblhosting`.`packageid` with `tblhosting`.`id`

It was exactly right, thank you.
Now, if I want to find all the clients in a Group of services, is it possible? Each group includes several different host plans.

Edited February 16 by ambaha

[pRieStaKos]

4 hours ago, ambaha said:

It was exactly right, thank you.
Now, if I want to find all the clients in a Group of services, is it possible? Each group includes several different host plans.

\WHMCS\Database\Capsule::table("tblhosting")
  ->join("tblclients", "tblhosting.userid", "=", "tblclients.id")
  ->join("tblproducts", "tblhosting.packageid", "=", "tblproducts.id")
  ->select("tblclients.email", "tblclients.phonenumber")
  ->whereIn("tblhosting.domainstatus", ["Cancelled", "Terminated"])
  ->where("tblhosting.domain", "<>", "")
  ->whereIn("tblproducts.gid", [1, 2]) # Add product group id here
  ->get();

SELECT `tblclients`.`email`, `tblclients`.`phonenumber`, `tblproducts`.`name`
FROM `tblhosting`
INNER JOIN `tblclients` ON `tblhosting`.`userid` = `tblclients`.`id`
INNER JOIN `tblproducts` ON `tblhosting`.`packageid` = `tblproducts`.`id`
WHERE `tblhosting`.`domainstatus` ON ("Cancelled", "Terminated")
  AND `tblhosting`.`domain` <> ""
  AND `tblproducts`.`gid` in (1, 2) # Add product group id here

You can always modify it, as your desire.

Edited February 16 by pRieStaKos

[ambaha]

10 hours ago, pRieStaKos said:

\WHMCS\Database\Capsule::table("tblhosting")
  ->join("tblclients", "tblhosting.userid", "=", "tblclients.id")
  ->join("tblproducts", "tblhosting.packageid", "=", "tblproducts.id")
  ->select("tblclients.email", "tblclients.phonenumber")
  ->whereIn("tblhosting.domainstatus", ["Cancelled", "Terminated"])
  ->where("tblhosting.domain", "<>", "")
  ->whereIn("tblproducts.gid", [1, 2]) # Add product group id here
  ->get();

SELECT `tblclients`.`email`, `tblclients`.`phonenumber`, `tblproducts`.`name`
FROM `tblhosting`
INNER JOIN `tblclients` ON `tblhosting`.`userid` = `tblclients`.`id`
INNER JOIN `tblproducts` ON `tblhosting`.`packageid` = `tblproducts`.`id`
WHERE `tblhosting`.`domainstatus` ON ("Cancelled", "Terminated")
  AND `tblhosting`.`domain` <> ""
  AND `tblproducts`.`gid` in (1, 2) # Add product group id here

You can always modify it, as your desire.

Thanks a lot, but I think the correct query is as follows:

 

SELECT `tblclients`.`email`, `tblclients`.`phonenumber`, `tblproducts`.`name`
FROM `tblhosting`
INNER JOIN `tblclients` ON `tblhosting`.`userid` = `tblclients`.`id`
INNER JOIN `tblproducts` ON `tblhosting`.`packageid` = `tblproducts`.`id`
WHERE `tblproducts`.`gid` in (16);

 

[pRieStaKos]

You got the point 😃