Where exactly (database) does it store clients products/services at?

[alturic]

Essentially I'm looking to make a MySQL query that will (laymans terms but still)

 

SELECT tblclients.phonenumber
FROM tblclients
WHERE tblproducts.name = 'Severe Weather Alerts'

 

That WHERE statement is where I would get hung-up for obvious reasons.

 

I have something like this:

 

SELECT tblclients.phonenumber as phone, tblclients.firstname, tblclients.lastname, tblproducts.name as product
FROM tblclients inner join tblproducts
WHERE tblproducts.name = 'Severe Weather Alerts'

 

But that returns everyone, for not-so-obvious reasons, to me.

 

I'm trying to find out (with no luck...) where and how WHMCS is storing all of the clients actual product info, if they are an active paying client (assumed it was tblclients.status) what products are attached to said client. It looks like tblclients houses all of the profile info, but I can't find anything about product/services attached to said clients?

[brian!]

as you suggest, tblclients holds the profile info - but tblproducts stores the details of the products/services that you are selling and is not linked to the clients table.

 

when a client purchases a product/service, the details will be stored in tblhosting (yes even if it's not a hosting product!), or if they order a domain, it will be in tbldomains.

 

the important column to look at in tblclients is 'id' - this is their client number and should be unique to them.

 

if you then take a look in tblhosting, there is a column called 'userid' - the values in there should equal a value from the 'id' column in tblclients... this will help you identify which client has bought that service.

 

another useful column in tblhosting is 'packageid' - this identifies the product purchased and can be referenced against the 'id' field in tblproducts.

 

so if we assume your 'Severe Weather Alerts' has a packageid (also referred to product ID) of 46, you could get a list of clients with that package using...

 

SELECT 
 tblclients.firstname,
 tblclients.lastname,
 tblclients.phonenumber
FROM tblhosting
 INNER JOIN tblclients
   ON tblhosting.userid = tblclients.id
WHERE tblhosting.packageid = 46

that should give you a list of clients (firstname, lastname, phonenumber) who have ordered the 'Severe Weather Alerts' service - if clients have multiple SWA services, they'll be listed multiple times (you could add DISTINCT after the SELECT command to get only one result per client).

 

if you wanted the product name in the output, you'd need to join the tblproducts table...

 

SELECT DISTINCT
 tblclients.firstname,
 tblclients.lastname,
 tblclients.phonenumber,
 tblproducts.name
FROM tblhosting
 INNER JOIN tblclients
   ON tblhosting.userid = tblclients.id
 INNER JOIN tblproducts
   ON tblhosting.packageid = tblproducts.id
WHERE tblhosting.packageid = 46

btw - a good place for you to look at WHMCS sql queries are in the reports and widgets files as you'll be able to view the query code that generates their output.

[alturic]

Hey Brian, thank you so much for the example. I modified it to get it to where I want it, but I have one last question which I won't lie is beyond the scope of simple WHMCS database structure.

 

So, we currently have 2 Custom Client Fields. Both are drop-downs, and both are identical in terms of the drop-down data. My query looks like this:

 

SELECT

tblclients.firstname,

tblclients.lastname,

tblclients.phonenumber,

tblproducts.name,

tblcustomfieldsvalues.value as monitoringcounty

FROM tblhosting

INNER JOIN tblclients

ON tblhosting.userid = tblclients.id

INNER JOIN tblproducts

ON tblhosting.packageid = tblproducts.id

INNER JOIN tblcustomfieldsvalues

ON tblclients.id = tblcustomfieldsvalues.relid

WHERE tblhosting.packageid = 1 AND tblcustomfieldsvalues.value <> ''

 

Which so far is doing exactly what I want. Selecting all of the clients who have the packageid of 1 and outputting them, with either one, or both, of the drop-downs depending on if the are empty.

 

The question is, can I also have it calculate how many are in each of those "monitoringcounty"? Here's what the var_dump of the query looks like:

 

array(5) { ["firstname"]=> string(5) "Jack" ["lastname"]=> string(6) "Johnson" ["phonenumber"]=> string(10) "1112223333" ["name"]=> string(17) "Daily Text Alerts" ["monitoringcounty"]=> string(20) " PA - Luzerne County" }

 

array(5) { ["firstname"]=> string(5) "Bob" ["lastname"]=> string(6) "Smith" ["phonenumber"]=> string(10) "5555551111" ["name"]=> string(17) "Daily Text Alerts" ["monitoringcounty"]=> string(20) " PA - Montgomery County" }

array(5) { ["firstname"]=> string(5) "Bob" ["lastname"]=> string(6) "Smith" ["phonenumber"]=> string(10) "5555552222" ["name"]=> string(17) "Daily Text Alerts" ["monitoringcounty"]=> string(20) " PA - Luzerne County" }

 

I THINK I'd need another query to actually have it query how many people have the PA - Luzerne County "monitoringcounty", correct?

 

The end result will look like this:

 

PA - Luzerne County (2)

PA - Montgomery County (1)

[brian!]

apologies for the delay in replying - I didn't get an email saying the thread had been replied to.

 

to do this, you'll need to use the COUNT function - but rather than describe its use myself, it would probably be easier for you to take a look at the explanation on the page below and then adapt your code to use count as an additional query.

 

http://www.tizag.com/mysqlTutorial/mysqlcount.php