Getting pricing from config options

[sixfive]

I've a need to know the config option price on a product where it is charged based on a quantity. In our case it's number of seats @$9.40/seat/month. 

The Data Feeds return a $0 value for us, because the pricing table has no values https://docs.whmcs.com/system/data-feeds/#Product_Pricing_and_Currency so I am working through how to get this in our own module. 

What I have so far,  by passing in the productID is this: 

SELECT 
    tblproductconfigoptions.id AS configoptionid,
    tblproductconfigoptions.optionname,
    tblpricing.monthly AS monthly_price
FROM 
    tblproductconfigoptions
JOIN 
    tblproductconfiglinks ON tblproductconfigoptions.gid = tblproductconfiglinks.gid
JOIN 
    tblpricing ON tblproductconfigoptions.id = tblpricing.relid
WHERE 
    tblproductconfiglinks.pid = 62
    AND tblpricing.currency = 1
    AND tblpricing.type = 'configoptions';

This is returning 0 in monthly, because the rows returned are NOT the rows where I can see 9.40. 
 

When I look for the 9.40 monthly as follows

SELECT currency,id,relid,monthly FROM `tblpricing`
where monthly = 9.40;

It shows me the rows are actually id's that are very different, and the relid is nothing like what I am expecting. 

The relid = 17 on the admin UI (or cid) 

The naming matches on cid = 17 in tblproductconfigoptions (see the URL in the popup) 

But the pricing shows up in relid = 85

What's going on here? 

 

[RadWebHosting]

While I don’t pretend to understand your end goal, maybe I can some advice regarding cid/relid.

Yes, the CID is your configurable option, “Number of Seats”. Configurable Options provide multiple “Option Type” which can then lead to numerous options per configurable option, for example when the Option Type is “Dropdown” there is no limit to the number of options. This is why the relid is required, since the price value can be different per Option within a Configurable Option.

Thus the relid is what actually is assigned this value (9.40). The cid is simply an instrument that enables a product to be eligible for the relid to be applied.

It’s more apparent when dealing with previous example of Dropdown, but even with type of “Quantity” the Option (looks like “Seats” from attached image) will be assigned a relid.