Pricing Issue

[EastsideHosting]

Hello,

 

I created a custom script to display my prices on my website using WHMCS.

 

Worked fine up until V4 - The pricing tables changed.

 

How would I get it to work on the new one?

 

V3 Code:

       $plans = mysql_query("SELECT * FROM tblproducts WHERE gid='13' ORDER by id ASC");
       while($data = mysql_fetch_array($plans)) {

 

No idea how to display the data as I've looked and don't see anything that matches to combind the two.

 

Tried these:

 

       $plans = mysql_query("SELECT * FROM tblproducts, tblpricing WHERE gid='13' ORDER by id ASC");

OR

       $plans = mysql_query("SELECT * FROM tblproducts AND tblpricing WHERE gid='13' ORDER by id ASC");

OR

       $plans = mysql_query("SELECT * FROM tblproducts JOIN tblpricing WHERE gid='13' ORDER by id ASC");

 

Could use some kind of solution.

 

Note: Could not post in Tips and Tricks for some reason (move if needed)

[GORF]

Same here.

 

Use this:

$query = 'Select tblproducts.id, tblproducts.type, tblproducts.gid, tblproducts.name, tblproducts.description, tblproducts.hidden, tblproducts.showdomainoptions, tblproducts.welcomeemail, tblproducts.stockcontrol, tblproducts.qty, tblproducts.proratabilling, tblproducts.proratadate, tblproducts.proratachargenextmonth, tblproducts.paytype, tblproducts.subdomain, tblproducts.autosetup, tblproducts.servertype, tblproducts.defaultserver, tblproducts.configoption1, tblproducts.configoption2, tblproducts.configoption3, tblproducts.configoption4, tblproducts.configoption5, tblproducts.configoption6, tblproducts.configoption7, tblproducts.configoption8, tblproducts.configoption9, tblproducts.configoption10, tblproducts.configoption11, tblproducts.configoption12, tblproducts.configoption13, tblproducts.configoption14, tblproducts.configoption15, tblproducts.configoption16, tblproducts.configoption17, tblproducts.configoption18, tblproducts.configoption19, tblproducts.configoption20, tblproducts.configoption21, tblproducts.configoption22, tblproducts.configoption23, tblproducts.configoption24, tblproducts.freedomain, tblproducts.freedomainpaymentterms, tblproducts.freedomaintlds, tblproducts.upgradepackages, tblproducts.configoptionsupgrade, tblproducts.billingcycleupgrade, tblproducts.tax, tblproducts.affiliateonetime, tblproducts.affiliatepaytype, tblproducts.affiliatepayamount, tblproducts.downloads, tblproducts.order, tblpricing.id, tblpricing.type, tblpricing.currency, tblpricing.relid, tblpricing.msetupfee, tblpricing.qsetupfee, tblpricing.ssetupfee, tblpricing.asetupfee, tblpricing.bsetupfee, tblpricing.monthly, tblpricing.quarterly, tblpricing.semiannually, tblpricing.annually, tblpricing.biennially From tblproducts, tblpricing  Where tblproducts.id = tblpricing.relid And tblproducts.gid = 1 And tblpricing.type = "product"';

 

This gets all fields from products and pricing. You can trim out what you don't use.

You may need to change tblproducts.gid near the end of the statement.

 

Note some things changed like annual = v3.8 / annually = v4.

 

Good luck.

Edited May 23, 2009 by TonsOfStores

[EastsideHosting]

Thanks, Usually prefer wild cards but based off your solution I got it working.

 

OLD Code

SELECT * FROM tblproducts WHERE gid='13' ORDER by id ASC

 

New Code

SELECT tblproducts.gid, tblproducts.name, tblproducts.configoption5, tblproducts.configoption6, tblproducts.id, tblpricing.monthly, tblpricing.currency FROM tblproducts, tblpricing WHERE tblproducts.id = tblpricing.relid AND tblproducts.gid='13' AND tblpricing.currency='1' AND tblpricing.type='product' ORDER by tblproducts.id ASC