mysql functions using custom pages

All,

I am working on some custom pages and have the templates and php files working together, however I need to gather some information from the database to display to the client.

In the php file per the example I have included the dbconnect.php, functions.php, and clientareafunctions.php files, but I am not able to find the documentation that shows what functions are those files provide, specifically database calls.

Can someone point me to some documentation or provide some examples?

Thanks!

-Robert

this may help as a reference only as it was the first to hand and is from my old V3

BUT THIS IS ONLY A GUIDE AND WILL NOT WORK ON V4

this is the php page part of the 2 files needed to use it.

<?php

//error_reporting(E_ALL);

define("CLIENTAREA",true);

 

# set a default group id.. can be overriden by GET parameter later

 

if(isset($_GET['gid'])){

$groupid = preg_replace('/[^0-9]/i', '', $_GET['gid']);

}

else {

$groupid = 1;

}

 

# Pull in WHMCS functions

require("dbconnect.php");

require("includes/functions.php");

require("includes/clientareafunctions.php");

require("includes/currencyfunctions.php");

 

# set page url variable

$pageurl2 = $_SERVER['PHP_SELF'];

 

#################################

# ASSIGN ARRAYS TO SMARTY

#################################

 

// internal counter for hosting plans.. used later to display variables and such

$num_plans = 0;

$smartyvalues["num_plans"] = $num_plans;

 

// plan options.. see if there is a way to generate this automatically from the db

$configoptionsarray = array();

$configoptionsarray['configoption3'] = 'Space (MB)';

$configoptionsarray['configoption5'] = 'Bandwidth (MB)';

$configoptionsarray['configoption2'] = 'FTP Accounts';

$configoptionsarray['configoption4'] = 'E-Mail Accounts';

$configoptionsarray['configoption8'] = 'SQL databases';

$configoptionsarray['configoption10'] = 'Subdomains';

$configoptionsarray['configoption12'] = 'Parked Domains';

$configoptionsarray['configoption14'] = 'Addon Domains';

 

// pricing options

$pricingarray = array();

$pricingarray['monthly'] = 'Monthly';

$pricingarray['quarterly'] = 'Quarterly';

$pricingarray['semiannually'] = 'Semi-Annually';

$pricingarray['annually'] = 'Annually';

$pricingarray['biennially'] = 'Bi-Annually';

 

// setup fee array...associate setup fees with product fields

$setuparray['monthly'] = 'msetupfee';

$setuparray['quarterly'] = 'qsetupfee';

$setuparray['semiannually'] = 'ssetupfee';

$setuparray['annually'] = 'asetupfee';

$setuparray['biennially'] = 'bsetupfee';

 

#################################

# END

#################################

 

# Get name of current hosting products group

$sql_curgroup = "SELECT name FROM tblproductgroups WHERE id = '$groupid' LIMIT 1";

$result_curgroup = mysql_query($sql_curgroup);

$curgroup_name = mysql_result($result_curgroup,0,'name');

 

$pagetitle = "Hosting Plans - $curgroup_name";

$pageicon = '';

 

$breadcrumbnav = '<a href="index.php">'.$_LANG['globalsystemname'].'</a>';

$breadcrumbnav .= " > <a href=\"$pageurl2?gid=$groupid\">$pagetitle</a>";

 

initialiseClientArea($pagetitle,$pageicon,$breadcrumbnav);

 

# Assign above config and such arrays to Smarty

$smarty->assign('configoptionsarray', $configoptionsarray);

$smarty->assign('pricingarray', $pricingarray);

$smarty->assign('setuparray', $setuparray);

 

# To assign variables in Smarty use the following syntax.

# This can then be used as {$variablename} in the template

$smartyvalues["pageurl2"] = $pageurl2;

 

# get/set default currency

if($_SESSION['currency'] < 1){

$sql_defcurrency = 'SELECT * FROM `tblcurrencies` WHERE `default` = 1 LIMIT 1';

$result_defcurrency = mysql_query($sql_defcurrency);

$default_currency = mysql_result($result_defcurrency,0,'id');

$_SESSION['currency'] = $default_currency;

}

else {

$default_currency = $_SESSION['currency'];

}

 

# populate global $currency array for formatCurrency function

 

$sql_defcurrency2 = "SELECT * FROM `tblcurrencies` WHERE `id` = $default_currency LIMIT 1";

$result_defcurrency2 = mysql_query($sql_defcurrency2);

$currency_code = mysql_result($result_defcurrency2,0,'code');

$currency_prefix = mysql_result($result_defcurrency2,0,'prefix');

$currency_suffix = mysql_result($result_defcurrency2,0,'suffix');

$currency_format = mysql_result($result_defcurrency2,0,'format');

$currency = array("code"=>"$currency_code","prefix"=>"$currency_prefix","suffix"=>"$currency_suffix","format"=>"$currency_format");

global $currency;

 

# get an array of group information for the template

# Just it's id and name

 

$whmcs_group_number = 0;

 

$sql_groups = 'SELECT DISTINCT tblproducts.gid, tblproductgroups.id, tblproductgroups.name, tblproductgroups.order, tblproducts.type, tblproductgroups.hidden, tblproducts.hidden FROM tblproductgroups, tblproducts WHERE tblproducts.gid = tblproductgroups.id AND tblproducts.type = \'hostingaccount\' AND tblproducts.hidden != \'on\' AND tblproductgroups.hidden != \'on\' ORDER BY tblproductgroups.order ASC';

$result_groups = mysql_query($sql_groups);

 

while($row = mysql_fetch_assoc($result_groups)){

 

// populate multi-level array with individual hosting group details

foreach ($row as $key => $value){

$whmcs_hosting_groups["$whmcs_group_number"]["$key"] = $value;

}

 

$whmcs_group_number++;

}

 

$smarty->assign('whmcs_hosting_groups', $whmcs_hosting_groups);

 

 

# get a list of plans and store them in an array for Smarty

$sql = "SELECT *, tblproducts.name AS prodname, tblproducts.id AS prodid FROM tblproducts, tblproductgroups, tblpricing WHERE tblpricing.type = 'product' AND tblproducts.id = tblpricing.relid AND tblpricing.currency = '$default_currency' AND tblproducts.gid=tblproductgroups.id AND tblproducts.type='hostingaccount' AND (tblproducts.hidden != 'on' AND tblproductgroups.hidden != 'on') AND tblproducts.gid='$groupid' ORDER BY tblproducts.id ASC";

$result = mysql_query($sql) OR die(mysql_error());

$whmcs_plan_number = 0;

 

while($row = mysql_fetch_assoc($result)){

$whmcs_plans[] = $row; # $data is the array created for use in the Smarty template.

 

// populate multi-level array with individual hosting package details

foreach ($row as $key => $value){

$whmcs_plans_feature["$whmcs_plan_number"]["$key"] = $value;

}

 

$whmcs_plan_number++;

$num_plans++;

}

 

$smartyvalues["num_plans"] = $num_plans;

$smarty->assign('whmcs_plans', $whmcs_plans);

$smarty->assign('whmcs_plans_feature', $whmcs_plans_feature);

 

//debug

print_r($whmcs_plans_feature);

 

# Define the template filename to be used without the .tpl extension

$templatefile = "compare_plans";

 

outputClientArea($templatefile);

?>

Simon,

Thank you very much, that gave me all the information I needed.

-Robert

this may help as a reference only as it was the first to hand and is from my old V3

BUT THIS IS ONLY A GUIDE AND WILL NOT WORK ON V4

this is the php page part of the 2 files needed to use it.

I'm not sure what you are talking about, seeing as it is my Plan Comparison mod that you are showing there, which is designed for v4.

Good point as I used it for our V4 installation, so it definitely works on that version.

-Robert

I did it like that as I am not using it on my V4 at this time so I did not now if it would work as is in v4 and as I did not supply it as a working code what I said was just to cover myself as if it had just been use and stopped his WHMCS from working it would them of been my problem and as I said it is a guide to how to connect to the database and get details from it that is all. At now point was asked for a working code as if I had of been I would of :

A, given ether a link to your mod or given the full code.

B, would of first installed it on my WHMCS v4 and asked if it was for V3 or V4.

So please do not come here and moth of to people how are helping other out as I did not see you helping out on this post.

All I can say is that I am very sorry for trying to help people that post a question have a reply, Say thanks, then say well I have this code installed. May de people should then try to help themselves by first thinking I need some code so where in my own installation could I find what I need I now I have a mod installed that is not encoded I could look at that to find what I need. or is that not how people learn round here.

sorry if I whent off on one but what it is the code that i posted as is would not display any items due to the missing tpl file so I am right that the code will not work actually in v3 or v4.

Simon,

I wasn't trying to upset you man, I was just saying that the code you posted did work for me in V4, nothing more, nothing less.. I do appreciate your help!

-Robert