Jump to content

Question about Reporting


Recommended Posts

Hi guys,

I am trying to improve our reporting, but I am not that good at PHP. So I thought this might be the right place to get some help.

What I am trying to do is grab an existing report, like clients.php and and make a copy of it, named monthly.php since I am thinking of pulling it every month, then add the fields I need.

The only bump in the road for me is, I have to pull the data I need from three different tables:




Since I am not so good at PHP, I don't know how to join the tables correctly in the query.




if (!defined("WHMCS"))
die("This file cannot be accessed directly");

$reportdata["title"] = "Monthly";

$filterfields = array("id"=>"ID","userid"=>"User ID","orderid"=>"Order ID","packageid"=>"Product ID","server"=>"Server ID","domain"=>"Domain Name","dedicatedip"=>"Dedicated IP","assignedips"=>"Assigned IPs","firstpaymentamount"=>"First Payment Amount","amount"=>"Recurring Amount","billingcycle"=>"Billing Cycle","nextduedate"=>"Next Due Date","paymentmethod"=>"Payment Method","domainstatus"=>"Status","username"=>"Username","password"=>"Password","notes"=>"Notes","subscriptionid"=>"Subscription ID","suspendreason"=>"Suspend Reason");

$reportdata["description"] = $reportdata["headertext"] = '';

if (!isset($_GET['print'])) {

$reportdata["description"] = "This report can be used to generate a custom export of services by applying up to 5 filters. CSV Export is available via the download link at the bottom of the page.";

$reportdata["headertext"] = '<form method="post" action="reports.php?report='.$report.'">
<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
foreach ($filterfields AS $k=>$v) {
   $reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="'.$k.'" id="fd'.$k.'"';
   if (in_array($k,$incfields)) $reportdata["headertext"] .= ' checked';
   $reportdata["headertext"] .= ' /> <label for="fd'.$k.'">'.$v.'</labe></td>'; $i++;
   if (($i%5)==0) $reportdata["headertext"] .= '</tr><tr>';
$reportdata["headertext"] .= '</tr></table></td></tr>';

for ( $i = 1; $i <= 5; $i ++ ) {
$reportdata["headertext"] .= '<tr><td width="20%" class="fieldlabel">Filter '.$i.'</td><td class="fieldarea"><select name="filterfield['.$i.']"><option value="">None</option>';
foreach ($filterfields AS $k=>$v) {
   $reportdata["headertext"] .= '<option value="'.$k.'"';
   if ($filterfield[$i]==$k) $reportdata["headertext"] .= ' selected';
   $reportdata["headertext"] .= '>'.$v.'</option>';
$reportdata["headertext"] .= '</select> <select name="filtertype['.$i.']"><option>Exact Match</option><option value="like"';
if ($filtertype[$i]=="like") $reportdata["headertext"] .= ' selected';
$reportdata["headertext"] .= '>Containing</option></select> <input type="text" name="filterq['.$i.']" size="30" value="'.$filterq[$i].'" /></td></tr>';
$reportdata["headertext"] .= '</table>
<p align="center"><input type="submit" value="Filter" /></p>


if (isset($incfields)) {

$filters = array();
foreach ($filterfield AS $i=>$val) {
   if ($val) $filters[$val] = ($filtertype[$i]=="like") ? array("sqltype"=>"LIKE","value"=>$filterq[$i]) : $filterq[$i];

$fieldlist = '';
foreach ($incfields AS $fieldname) {
   $reportdata["tableheadings"][] = $filterfields[$fieldname];
   $fieldlist .= $fieldname.',';
$fieldlist = substr($fieldlist,0,-1);

$result = select_query("tblhosting",$fieldlist,$filters);
while ($data = mysql_fetch_assoc($result)) {
   if ($data['password']) $data['password']=decrypt($data['password']);
   $reportdata["tablevalues"][] = $data;





Who can provide me with the correct way to fix that?


Thanks in advance.

Link to comment
Share on other sites

Basic SQL table joining syntax is:


SELECT t1.column, t2.column
FROM MainTable t1
JOIN SecondaryTable t2 ON t2.column = t1.sameColumn


For instance, if you wanted to join tblhosting and tblhostingaddon, you'd


1. Find the common column between them.


It looks like tblhostingaddons.hostingid and tblhosting.id are the common columns.


2. Write the JOIN:

SELECT h.id, h.domain, h.regdate, ha.name as hostingAddon, ha.setupFee as hostingAddonSetupFee
FROM tblhosting h
JOIN tblhostingaddons ha ON ha.hostingid = h.id






There are four main types of joins (in order of my usage, descending):

  1. INNER JOIN - The default in MySQL, this join will ONLY return rows where both t1's and t2's shared column have the same value.
  2. LEFT JOIN - This join will return *all* the rows of t1 and any in t2 that match the shared column's value.
  3. RIGHT JOIN - This join is the opposite of the Left join.
  4. OUTER JOIN - This join will return all the rows of t2 for every row of t1. This only useful in a few cases.

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.

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