Jump to content

Getting pricing from config options


sixfive

Recommended Posts

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? 

 

Screenshot 2024-11-17 at 7.39.50 pm.png

Screenshot 2024-11-17 at 7.39.10 pm.png

Screenshot 2024-11-17 at 7.39.04 pm.png

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated