Question about Reporting

[BulkWeb]

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:

tblclients

tblhosting

tblhostingaddons

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

 

------------------------------------------------------------------------------------------------------

[i]<?php

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>';
$i=0;
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>
</form>';

}

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

}

?>

[/i]------------------------------------------------------------------------------------------------------

 

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

 

Thanks in advance.

[WHMCS TedX]

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

 

-----

 

BoNus!

 

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.
   

[BulkWeb]

Should I paste it exactly the way you provided it to me? The only line that resembles a mysql query in this file looks like this:

$result = select_query("tblhosting",$fieldlist,$filters);