Viewing Extensions

[FrenchMarie]

Good morning all,

I come to you today because I want to have the categories and domain extensions on a personalized page.

I took the elements of the domainregister.tpl file, but unable to load the list of domain categories and the list of extensions.

A big thank you to you in advance for any response that may put us on the path

[brian!]

On 28/10/2020 at 11:56, FrenchMarie said:

I took the elements of the domainregister.tpl file, but unable to load the list of domain categories and the list of extensions.

that's because the template only contains the code required to display the variables that are passed to it by WHMCS... if you just copied the code to a new page it wouldn't work because the variables will be missing.

On 28/10/2020 at 11:56, FrenchMarie said:

A big thank you to you in advance for any response that may put us on the path

to get the pricing array (or similar) used in the domainregister page available to your page, you could use the GetTLDPricing API function inside a hook.

if you just want a list of categories / TLDs, then perhaps the thread below will help...

... and don't forget there is a separate domain pricing page available @ /domain/pricing that will show the TLD pricing, including the first category a TLD is within...

[Remitur]

There's also this other topic, about retrieving domain extension and related prices:

 

[FrenchMarie]

Hello

Many thanks for your feedback, before I use the following code in the page php file and it will work perfectly.

$currencyid = $whmcs->get_req_var('currency');
if (!$currencyid) {
    $currencyid = $whmcs->get_req_var('currencyid');
}
if (!is_numeric($currencyid)) {
    $currency = array();
} else {
    $currency = getCurrency('', $currencyid);
}

if (!$currency || !is_array($currency) || !isset($currency['id'])) {
    $currency = getCurrency();
}
$currencyid = $currency['id'];

$categories = array();
$tldcategories = DB::table('tbltld_category_pivot as p')
    ->join('tbltld_categories as c', 'c.id', '=', 'p.category_id')
    ->join('tbltlds as t', 't.id', '=', 'p.tld_id')
    ->join('tbldomainpricing as d', DB::raw("binary d.extension"), '=', DB::raw("binary CONCAT('.', t.tld)"))
    ->orderBy('c.is_primary', 'desc')
    ->orderBy('c.display_order', 'asc')
    ->orderBy('t.tld', 'asc')
    ->select(['c.id', 'c.category', 't.tld'])
    ->get();
foreach ($tldcategories as $category) {
    if (!isset($categories[$category->id])) {
        $cat = new stdClass();
        $cat->id = $category->id;
        $cat->category = $category->category;
        $cat->topLevelDomains = array();
        $categories[$category->id] = $cat;
    }
    $categories[$category->id]->topLevelDomains[] = $category->tld;
}

$tldpricelist = DB::table('tbldomainpricing as d')
    ->join('tblpricing as p', 'p.relid', '=', 'd.id')
    ->where('p.currency', '=', $currencyid)
    ->whereIn('p.type', ['domainregister', 'domaintransfer', 'domainrenew'])
    ->orderBy('d.order')
    ->select(['d.extension as tld', 'd.group',
        DB::raw("SUM(IF(type='domainregister', msetupfee, NULL)) AS register"),
        DB::raw("SUM(IF(type='domaintransfer', msetupfee, NULL)) AS transfer"),
        DB::raw("SUM(IF(type='domainrenew', msetupfee, NULL)) AS renew")])
    ->groupBy('d.extension')
    ->get();

$tldpricing = array();
foreach ($tldpricelist as $tld) {
    $tldpricing[substr($tld->tld, 1)] = $tld;
}

$ca->setTemplate('domain-name');
$ca->assign('tldcategories', $categories);
$ca->assign('tldpricing', $tldpricing);
$ca->assign('tldpricelist', $tldpricelist);
$ca->assign('currency', $currency);

Since the update I have the error:

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'sitename.tbltld_category_pivot' doesn't exist in /home/sitename/public_html/vendor/illuminate/database/Connection.php:331
Stack trace:
#0 /home/sitename/public_html/vendor/illuminate/database/Connection.php(331): PDO->prepare('select `c`.`id`...')
#1 /home/sitename/public_html/vendor/illuminate/database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database\{closure}('select `c`.`id`...', Array)
#2 /home/sitename/public_html/vendor/illuminate/database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('select `c`.`id`...', Array, Object(Closure))
#3 /home/sitename/public_html/vendor/illuminate/database/Connection.php(339): Illuminate\Database\Connection->run('select `c`.`id`...', Array, Object(Closure))
#4 /home/sitename/public_html/vendor/illuminate/database/Query/Builder.php(2202): Illuminate\Database\Connection->select('select `c`.`id`...', Array, true)
#5 /home/sitename/public_html/vendor/illuminate/database/Query/Builder.php(2190): Illuminate\Database\Query\Builder->runSelect()
#6 /home/sitename/public_html/vendor/illuminate/database/Query/Builder.php(2685): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
#7 /home/sitename/public_html/vendor/illuminate/database/Query/Builder.php(2191): Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure))
#8 /home/sitename/public_html/domain-name.php(42): Illuminate\Database\Query\Builder->get()
#9 {main}
Next Illuminate\Database\QueryException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'clubhost_v7.tbltld_category_pivot' doesn't exist (SQL: select `c`.`id`, `c`.`category`, `t`.`tld` from `tbltld_category_pivot` as `p` inner join `tbltld_categories` as `c` on `c`.`id` = `p`.`category_id` inner join `tbltlds` as `t` on `t`.`id` = `p`.`tld_id` inner join `tbldomainpricing` as `d` on binary d.extension = binary CONCAT('.', t.tld) order by `c`.`is_primary` desc, `c`.`display_order` asc, `t`.`tld` asc) in /home/sitename/public_html/vendor/illuminate/database/Connection.php:671
Stack trace:
#0 /home/sitename/public_html/vendor/illuminate/database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('select `c`.`id`...', Array, Object(Closure))
#1 /home/sitename/public_html/vendor/illuminate/database/Connection.php(339): Illuminate\Database\Connection->run('select `c`.`id`...', Array, Object(Closure))
#2 /home/sitename/public_html/vendor/illuminate/database/Query/Builder.php(2202): Illuminate\Database\Connection->select('select `c`.`id`...', Array, true)
#3 /home/sitename/public_html/vendor/illuminate/database/Query/Builder.php(2190): Illuminate\Database\Query\Builder->runSelect()
#4 /home/sitename/public_html/vendor/illuminate/database/Query/Builder.php(2685): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
#5 /home/sitename/public_html/vendor/illuminate/database/Query/Builder.php(2191): Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure))
#6 /home/sitename/public_html/domain-name.php(42): Illuminate\Database\Query\Builder->get()
#7 {main}

cordially

[brian!]

20 hours ago, FrenchMarie said:

Many thanks for your feedback, before I use the following code in the page php file and it will work perfectly.

I assume you were using a version before v7.10  when this code worked ? because when you update to v7.10 or later, the three tbltld database tables are removed - that's why your queries are causing errors as they are referencing tables that no longer exist in the database.

[FrenchMarie]

Hello and thank you for your feedback,

Yes exactly, a real shame because the result was impeccable.

Too bad that nobody can rewrite this part of the code in order to be able to exploit it again.

cordially

[brian!]

On 04/11/2020 at 15:04, FrenchMarie said:

Too bad that nobody can rewrite this part of the code in order to be able to exploit it again.

it occurs to me that there is another way if you want to go down that road of continuing to use the hook - recreate the 3 tbltld database tables that it refers to. 💡

the sql queries that creates the tables, and fills them with data might still exist in your /resources/sql/install directory...

if they don't, then they would exist in a v7.9.2 (or earlier) zip file.

I think i'd be tempted to change the table names, just in case a future updater doesn't like them being there and removes them again - it shouldn't remove renamed tables... and then adjust your hook to use the renamed tables.

as long as you're not updating categories.json often, then I wouldn't have thought there would be too many differences between the tables and the json file... and if you are updating categories, then just remember to update them in the json and edit the database accordingly at the same time.

[FrenchMarie]

Hello,

Many thanks to you, indeed, once the data bases are in place, it works again.

cordially