External Domain Price List

Chinmi · June 28, 2009

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.

ffs123 · September 21, 2009

Yes, I like to get that feature in WHMCS. I like WHMCS better since it is simple to use. But AWBS has that feature. I hope WHMCS has that feature soon in the future upgrade.

turbex · September 21, 2009

try this query

SELECT dp.extension, p.msetupfee 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

HerrZ · December 16, 2009

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;	
}

NoDoze · December 16, 2009

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.

HerrZ · December 17, 2009

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

call the function

NoDoze · December 17, 2009

{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....

HerrZ · December 18, 2009

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}

davoram · January 19, 2010

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!

HerrZ · January 20, 2010

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.

Grzesiu · February 14, 2010

How can I do array with price of registration, renewal and transfer in one table?

HerrZ · February 15, 2010

all prices should be in the array:

WHERE t.type IN ('domainregister','domaintransfer','domainrenew')

Grzesiu · February 15, 2010

all prices should be in the array:

Display only domainrenew:

mydata: Array
(
[.com] => Array
(
[id] => 1
[tld] => .com
[type] => domainrenew
[code] => USD
[suffix] => USD
[msetupfee] => 10
)
)

HerrZ · February 15, 2010

ah, the array key is overwritten.

in while loop code

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

to

$dataArray[$row['tld'] . '_' . $row['type']]=$row;

HerrZ · February 16, 2010

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'] . '$';	
}

MAXER-peter · March 10, 2010

Thanks for this code.

Can someone advise how to modify the code so it shows the registration price for 2 years for .uk domains?

It currently lists .co.uk as $0.00

I'd like it to say $15.00 / 2 years for .co.uk, .org.uk and .me.uk

DataViking · March 11, 2010

Thank you for the code

newgenservices · June 22, 2010

@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 June 22, 2010 by newgenservices

newgenservices · June 22, 2010

   $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 June 22, 2010 by newgenservices

neocorps · June 29, 2010

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
               )

HerrZ · June 30, 2010

look for tblpricing table in col currency. there is the id of the only relating currency. i think so. i dont know about multi currencies in domainpricing.

please tell me if there is something.

neocorps · June 30, 2010

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

HerrZ · July 1, 2010

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

neocorps · July 2, 2010

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.

Rhyzio · September 9, 2010

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 September 9, 2010 by Rhyzio
forgot a word

External Domain Price List

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Recently Browsing 0 members

Important Information