How to get WHMCS product price details by query?
If you would like to know the price details of a product in WHMCS by query,
you can use the below query.
First you have to get the currency id. It’s the Id of the currency chosen by the user.
I wrote a function named getCurrencyId to get the currency ID.
function getCurrencyId() { $uid = $_SESSION['uid']; if (empty($uid)) { if (!empty($_SESSION['currency'])) { return $_SESSION['currency']; } else { return 1; } } $userData = Capsule::table('tblclients') ->select('currency') ->where('id', '=', $uid) ->first(); if (!empty($userData)) return $userData->currency; else return 1; }
Assume that we would like to know price details of the product with id = 10.
Then we have to join tables tblproducts and tblpricing on a condition
‘tblproducts.id’, ‘=’, ‘tblpricing.relid’.
$currency_id = getCurrencyId(); $pid = 10; $product_details = Capsule::table('tblproducts') ->join('tblpricing', 'tblproducts.id', '=', 'tblpricing.relid') ->where('tblpricing.type', '=', 'product') ->where('tblproducts.id', $pid) ->where('tblpricing.currency', $currency_id) ->select('tblproducts.*', 'tblpricing.*') ->first();
From the result array $product_details
we can get monthly, quarterly, semiannually, annually, biennially and triennially
as given below. If no price is entered for a particular period, you will get price as -1.
$monthly_price = $product_details->monthly; if($monthly_price != -1 ) { echo "monthly price is" . $monthly_price; } //do -1 check all of the periods. $quarterly_price = $product_details->quarterly; $semiannually_price = $product_details->semiannually; $annually_price = $data->annually; $biennially_price = $data->biennially; $triennially_price = $data->triennially;