Jump to content

External Domain Price List


Chinmi

Recommended Posts

Hey guys, just wondering if any of you have discovered how to retrieve the domain pricing list from whmcs v4 and display it on your website.

 

I have very very basic knowledge of php/mysql but the way I understand it.....

  • tblpricing: has all the actual pricing details
  • tbldomainpricing: where the domain extensions are listed

 

What would the query string be to retrieve the extension and its prices? I don't have a clue on how to relate them to each other and output the data.

 

Any help would be appreciated.

Link to comment
Share on other sites

  • 2 months later...
  • 2 months later...

some more php

 

function getDomains()
{
	$query = mysql_query("SELECT d.id, d.extension 'tld', t.type, c.code, c.suffix, t.msetupfee FROM tbldomainpricing AS d
		INNER JOIN tblpricing AS t ON t.relid = d.id
		INNER JOIN tblcurrencies AS c ON c.id = t.currency 
	WHERE t.type IN ('domainregister','domaintransfer','domainrenew') ORDER BY d.id ASC");
	$dataArray=array();
	while($row = @mysql_fetch_array($query, MYSQL_ASSOC))
		$dataArray[$row['tld']]=$row;
	return $dataArray;	
}

Link to comment
Share on other sites

function getDomains()

{

$query = mysql_query("SELECT d.id, d.extension 'tld', t.type, c.code, c.suffix, t.msetupfee FROM tbldomainpricing AS d

INNER JOIN tblpricing AS t ON t.relid = d.id

INNER JOIN tblcurrencies AS c ON c.id = t.currency

WHERE t.type IN ('domainregister','domaintransfer','domainrenew') ORDER BY d.id ASC");

$dataArray=array();

while($row = @mysql_fetch_array($query, MYSQL_ASSOC))

$dataArray[$row['tld']]=$row;

return $dataArray;

}

 

I put <?php ?> around that and get nothing....?

Any ideas?

 

Thanks.

Link to comment
Share on other sites

{php}

// Make a MySQL Connection

mysql_connect("localhost", "user", "password") or die(mysql_error());

mysql_select_db("database") or die(mysql_error());

 

function getDomains()

{

$query = mysql_query("SELECT d.id, d.extension 'tld', t.type, c.code, c.suffix, t.msetupfee FROM tbldomainpricing AS d

INNER JOIN tblpricing AS t ON t.relid = d.id

INNER JOIN tblcurrencies AS c ON c.id = t.currency

WHERE t.type IN ('domainregister','domaintransfer','domainrenew') ORDER BY d.id ASC");

$dataArray=array();

while($row = @mysql_fetch_array($query, MYSQL_ASSOC))

$dataArray[$row['tld']]=$row;

return $dataArray;

}

 

 

getDomains();

 

{/php}

 

Still don't get anything to come up....

...call me a newbie....

Link to comment
Share on other sites

you do not need to mysql connect to database INSIDE of WHMCS.

 

anyway here is the version for smarty template:

{php}
// Make a MySQL Connection
mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());

function getDomains()
{
$query = mysql_query("SELECT d.id, d.extension 'tld', t.type, c.code, c.suffix, t.msetupfee FROM tbldomainpricing AS d
INNER JOIN tblpricing AS t ON t.relid = d.id
INNER JOIN tblcurrencies AS c ON c.id = t.currency
WHERE t.type IN ('domainregister','domaintransfer','domainrenew') ORDER BY d.id ASC");
$dataArray=array();
while($row = @mysql_fetch_array($query, MYSQL_ASSOC))
$dataArray[$row['tld']]=$row;
return $dataArray;
}


$mydata = getDomains();
echo "<pre>mydata: " . print_r($mydata,TRUE) . "</pre>";

{/php}

Link to comment
Share on other sites

  • 1 month later...
you do not need to mysql connect to database INSIDE of WHMCS.

 

anyway here is the version for smarty template:

{php}
// Make a MySQL Connection
mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());

function getDomains()
{
$query = mysql_query("SELECT d.id, d.extension 'tld', t.type, c.code, c.suffix, t.msetupfee FROM tbldomainpricing AS d
INNER JOIN tblpricing AS t ON t.relid = d.id
INNER JOIN tblcurrencies AS c ON c.id = t.currency
WHERE t.type IN ('domainregister','domaintransfer','domainrenew') ORDER BY d.id ASC");
$dataArray=array();
while($row = @mysql_fetch_array($query, MYSQL_ASSOC))
$dataArray[$row['tld']]=$row;
return $dataArray;
}


$mydata = getDomains();
echo "<pre>mydata: " . print_r($mydata,TRUE) . "</pre>";

{/php}

 

 

 

 

It work but it displays the info like this...

 

mydata: Array

(

[.com] => Array

(

[id] => 1

[tld] => .com

[type] => domainrenew

 => USD

[suffix] => USD

[msetupfee] => 10

)

 

[.mx] => Array

(

[id] => 2

[tld] => .mx

[type] => domainrenew

[code] => USD

[suffix] => USD

[msetupfee] => 10

)

 

How can the info be display in a table ?

 

 

 

Thanks!:lol:

Link to comment
Share on other sites

the $mydata variable is an array. you can output each of the array keyvalues if you want.

 

First you can assign the php var $mydata to an smartyvar.

$this->assign('tlddata', $mydata ); 

but in case it is still an array. for output of the singe values of the array do sometihng like this.

{$tlddata}

 

output the data:

<table>
<tr><th>TLD</th><th>Price</th></tr>
{foreach key=num item=tld from=$tlddata}
<tr><td>{$tld.tld}</td><td>{$tld.msetupfee} {$tld.suffix}</td></tr>
{/foreach}
</table>

i not tested it, but this should be the right way.

of course you can "SELECT" more DB fielde for more values in the mydata array.

Link to comment
Share on other sites

  • 4 weeks later...

finally:

// Make a MySQL Connection
mysql_connect("localhost", "USER", "PASS") or die(mysql_error());
mysql_select_db("DBNAME") or die(mysql_error());

function getDomains()
{
$query = mysql_query("SELECT d.id, d.extension 'tld', t.type, c.code, c.suffix, t.msetupfee FROM tbldomainpricing AS d
	INNER JOIN tblpricing AS t ON t.relid = d.id
	INNER JOIN tblcurrencies AS c ON c.id = t.currency
	WHERE t.type IN ('domainregister','domaintransfer','domainrenew') ORDER BY d.id ASC");
$dataArray=array();
while($row = @mysql_fetch_array($query, MYSQL_ASSOC)) {
	$dataArray[$row['tld']][$row['type']]=$row;
}
return $dataArray;
}


$mydata = getDomains();
echo "<pre>mydata: " . print_r($mydata,TRUE) . "</pre>";
foreach($mydata as $k => $v) { // $k=TLD
echo "<br />" . $k . ' - Register Price: ' .	$v['domainregister']['msetupfee'] . '$';	
}

Link to comment
Share on other sites

  • 4 weeks later...
  • 3 months later...

@Herrz: Thanks for the code.

 

@spiralhosting and others who may be interested: Try this for all years (make sure to add a condition not to display 0.00 and -1.00)

 

$query = mysql_query("SELECT d.id, d.extension 'tld', t.relid, t.type, c.code, c.suffix, t.msetupfee, t.qsetupfee, t.ssetupfee, t.asetupfee, t.bsetupfee, t.monthly, t.quarterly, t.semiannually, t.annually, t.biennially FROM tbldomainpricing AS d 
           INNER JOIN tblpricing AS t ON t.relid = d.id 
           INNER JOIN tblcurrencies AS c ON c.id = t.currency  
       WHERE t.type IN ('domainregister','domaintransfer','domainrenew') ORDER BY d.id ASC"); 

 

This fetches prices for all 10 years.

Edited by newgenservices
Link to comment
Share on other sites

   $query = mysql_query("SELECT d.id, d.extension 'tld', t.relid, t.type, c.code, c.suffix, t.msetupfee '1yr', t.qsetupfee '2yr', t.ssetupfee '3yr', t.asetupfee '4yr', t.bsetupfee '5yr', t.monthly '6yr', t.quarterly '7yr', t.semiannually '8yr', t.annually '9yr', t.biennially '10yr' FROM tbldomainpricing AS d 
           INNER JOIN tblpricing AS t ON t.relid = d.id 
           INNER JOIN tblcurrencies AS c ON c.id = t.currency  
       WHERE t.type IN ('domainregister','domaintransfer','domainrenew') ORDER BY d.id ASC"); 

 

This would give output in much easier way to read.

 

echo "<pre>".htmlspecialchars(print_r(getDomains(),true))."</pre>"; 

 

@Mods: If possible please merge this PHP code with my previous reply and delete this post. Thanks.

Edited by newgenservices
Link to comment
Share on other sites

finally:

// Make a MySQL Connection
mysql_connect("localhost", "USER", "PASS") or die(mysql_error());
mysql_select_db("DBNAME") or die(mysql_error());

function getDomains()
{
$query = mysql_query("SELECT d.id, d.extension 'tld', t.type, c.code, c.suffix, t.msetupfee FROM tbldomainpricing AS d
	INNER JOIN tblpricing AS t ON t.relid = d.id
	INNER JOIN tblcurrencies AS c ON c.id = t.currency
	WHERE t.type IN ('domainregister','domaintransfer','domainrenew') ORDER BY d.id ASC");
$dataArray=array();
while($row = @mysql_fetch_array($query, MYSQL_ASSOC)) {
	$dataArray[$row['tld']][$row['type']]=$row;
}
return $dataArray;
}


$mydata = getDomains();
echo "<pre>mydata: " . print_r($mydata,TRUE) . "</pre>";
foreach($mydata as $k => $v) { // $k=TLD
echo "<br />" . $k . ' - Register Price: ' .	$v['domainregister']['msetupfee'] . '$';	
}

 

Hi man, I have a few questions about your query..

 

I use MXN as the default currency, its ID in tblcurrencies is 1, USD's id is 3.

 

When I get the $mydata array, I only get USD prices shown, not MXN, and when I debug the page I get $currency = id(1).. which means it is MXN. I don't get where in your query is the input of the current currency of the page.

 

I tried changing t.currency to 1 and it got me MXN currency but msetupfee keeps grabbing the USD values instead of the MXN currency values.

 

I'm clueless where to fix or change your query which actually seems to be correct.

 

you can check http://www.neocorps.com/billing/cart.php?a=add&domain=register in there you'll find the output just below the searchbox in a table.

 

[.com] => Array
       (
           [domainregister] => Array
               (
                   [id] => 1
                   [tld] => .com
                   [type] => domainregister
                   [code] => USD <--- should be MXN
                   [suffix] => USD <--- should be MXN
                   [msetupfee] => 0.00 <---- Should be 139.00
               )

Link to comment
Share on other sites

Well, since I use two currencies, USD and MXN, being MXN the primary, in tblpricing I have 6 records for each domain (domainregister, domaintransfer, domainrenew), and I have two currency id's 1(MXN) and 3(USD) and one relid 1(.com) maybe there is where the confusion starts.

 

When I specifically change the query's "INNER JOIN tblcurrencies AS c ON c.id = t.currency" to "INNER JOIN tblcurrencies AS c ON c.id = 1" I get:

 

[.com] => Array
       (
           [domainregister] => Array
               (
                   [id] => 1
                   [tld] => .com
                   [type] => domainregister
                   [code] => MXN
                   [suffix] => MXN
                   [msetupfee] => 0.00 <---- Should be 139.00
               ) 

 

But as you can see, it keeps grabbing the msetupfee of the USD currency.. which right now I have set to 0.00.

 

The strange thing here is that.. I see nowhere in the query an input of currency so by default, it should grab id # 1, If I debug the page I get on $currency

 

Array (6)
id => 1
code => MXN
prefix => $
suffix => MXN
format => 1
rate => 1.00000

 

So the issue here is the query probably not being thought to handle different currencies, I'll probably will have to look into it a bit more to see what the issue is here. Thanks for the reply though

Link to comment
Share on other sites

you have to set the domain TLD prices for each currency.

setup->domainpricing <"open pricing" for each TLD>

 

and extend your query with

 

AND c.code='MXN'

 

after WHERE clause (before ORDER BY)

 

this should work

Link to comment
Share on other sites

you have to set the domain TLD prices for each currency.

setup->domainpricing <"open pricing" for each TLD>

 

and extend your query with

 

AND c.code='MXN'

 

after WHERE clause (before ORDER BY)

 

this should work

 

Thanks herrZ, will try this tomorrow and post back my result.

Link to comment
Share on other sites

  • 2 months later...

Hey Neocorps, try this: (you can change "p.currency = 1" as needed)

 

function getDomains()

{

$query = mysql_query("SELECT dp.extension'tld', p.msetupfee, p.qsetupfee FROM tblpricing p

INNER JOIN tbldomainpricing dp ON dp.id = p.relid

WHERE

p.type='domainregister' AND

p.currency = 1

ORDER BY dp.order ASC");

$dataArray=array();

while($row = @mysql_fetch_array($query, MYSQL_ASSOC))

$dataArray[$row['tld']]=$row;

return $dataArray;

}

$mydata = getDomains();

Edited by Rhyzio
forgot a word
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