Custom mysql query

[Gibby13]

So I have Configurable addon for a few products.

It has values like

1000GB

2000GB

3000GB

 

ect...

 

 

I need to be able to query the mysql database and grab this value. I am OK in mysql, so I have been digging around in phphmyadmin, but I can not figure out how it stores this value for a service that a client has.

[laszlof]

Do you mean configurable options? Something like this should work. replace SERVICEID with the service ID from WHMCS, and OPTIONNAME with the name of the configurable option.

 

SELECT 
   pcos.optionname AS config_option_value
FROM 
   tblproductconfigoptionssub pcos,
   tblproductconfigoptions pco,
   tblhostingconfigoptions hco
WHERE 
   pcos.configid = pco.id AND
   hco.configid = pco.id AND
   hco.optionid = pcos.id AND
   pco.optionname = '[b]OPTIONNAME[/b]' AND
   hco.relid=[b]SERVICEID[/b]

[Gibby13]

I think that is close to what I need, but what/where is the SERVICEID?

[Gibby13]

Ok I found the SERVICEID.

 

So now I want to pull this data for each client if their product group id is 3 or 4

[laszlof]

So you want the query to return the client ID, the service ID, and the config option values? Try this:

 

SELECT
 c.id AS userid, h.id AS serviceid, pcos.optionname AS config_option_value 
FROM 
 tblhosting h, tblclients c, tblproducts p, tblproductconfigoptionssub pcos, tblproductconfigoptions pco, tblhostingconfigoptions hco 
WHERE 
 pcos.configid = pco.id AND 
 hco.configid = pco.id AND 
 hco.optionid = pcos.id AND
 hco.relid = h.id AND 
 c.id = h.userid AND
 p.id = h.packageid AND
 p.gid = [b]PRODUCT_GROUP_ID[/b] AND
 pco.optionname = '[b]OPTIONNAME[/b]'

 

EDIT: It would probably be easier if you just explained what you were trying to accomplish.

[Gibby13]

I am writing a bash script to do the following;

First it connects bandwidth monitoring server and pulls the BW metering,

then if a client has a Product that is in the Product Group VPS or Cluster, grab the value of the Bandwidth option and the appliance name

then the script will put the data together.

 

Once I have that done, I am going to convert it to a php script and hopefully be able to have clients see it when logged in

[Gibby13]

So this is what I have so far that works:

 

SELECT
 c.id AS userid, h.id AS serviceid, pcos.optionname AS config_option_value, dedicatedip
FROM
 tblhosting h, tblclients c, tblproducts p, tblproductconfigoptionssub pcos, tblproductconfigoptions pco, tblhostingconfigoptions hco
WHERE
 pcos.configid = pco.id AND
 hco.configid = pco.id AND
 hco.optionid = pcos.id AND
 hco.relid = h.id AND
 c.id = h.userid AND
 p.id = h.packageid AND
 p.gid = 3 AND
 pco.optionname = 'Bandwidth

 

I try to add an OR like this but it doesn't work:

 

SELECT
 c.id AS userid, h.id AS serviceid, pcos.optionname AS config_option_value, dedicatedip
FROM
 tblhosting h, tblclients c, tblproducts p, tblproductconfigoptionssub pcos, tblproductconfigoptions pco, tblhostingconfigoptions hco
WHERE
 pcos.configid = pco.id AND
 hco.configid = pco.id AND
 hco.optionid = pcos.id AND
 hco.relid = h.id AND
 c.id = h.userid AND
 p.id = h.packageid AND
 p.gid = 3 OR
 p.gid = 4 AND
 pco.optionname = 'Bandwidth

 

 

Also my bash code looks like this

 

#!/bin/bash
DATA_TO_GATHER=`mysql -u xxxx --password='xxxxx' -D whmcs -e \
"SELECT
 c.id AS userid, h.id AS serviceid, pcos.optionname AS config_option_value, dedicatedip
FROM
 tblhosting h, tblclients c, tblproducts p, tblproductconfigoptionssub pcos, tblproductconfigoptions pco, tblhostingconfigoptions hco
WHERE
 pcos.configid = pco.id AND
 hco.configid = pco.id AND
 hco.optionid = pcos.id AND
 hco.relid = h.id AND
 c.id = h.userid AND
 p.id = h.packageid AND
 p.gid = 3 AND
 pco.optionname = 'Bandwidth';"`

echo $DATA_TO_GATHER

 

However it outputs like this:

userid serviceid config_option_value dedicatedip 2 26 2000GB Total 123.4.5.6

 

 

For some reason it is all on 1 line instead of 2

[laszlof]

...snip...
 p.id = h.packageid AND
 (p.gid = 3 OR
 p.gid = 4) AND
...snip...

 

For the shell script, use:

 

mysql -u xxxx --password='xxxxx' -D whmcs -BNe "[b]QUERY[/b]"

[Gibby13]

Getting a syntax error with the following:

 

DATA_TO_GATHER=`mysql -u xxxxx --password='xxxxx' -D whmcs -BNe \
"QUERY
 c.id AS userid, h.id AS serviceid, pcos.optionname AS config_option_value, dedicatedip
FROM
 tblhosting h, tblclients c, tblproducts p, tblproductconfigoptionssub pcos, tblproductconfigoptions pco, tblhostingconfigoptions hco
WHERE
 pcos.configid = pco.id AND
 hco.configid = pco.id AND
 hco.optionid = pcos.id AND
 hco.relid = h.id AND
 c.id = h.userid AND
 p.id = h.packageid AND
 (p.gid = 3 OR
 p.gid = 4)
 pco.optionname = 'Bandwidth';"`

[laszlof]

remove QUERY. I just put that there so you knew where to put the existing query.

[Gibby13]

lol, no wonder I could not find that command anywhere

[Gibby13]

Hmm, now I don't get the heading which is fine, but it grabs 2 rows during the query and combines them into 1 row:

 

2 29 1000GB Total 184.170.249.237 4 31 2000GB Total 184.170.249.238

[laszlof]

Thats something in your shell environment, it outputs one per line from mysql.

[Gibby13]

Converted the SQL query to a php script and I am just calling it from my bash script and it is working.

 

Thanks for all the help!