CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.

YesSoftware Forums -> CodeCharge Studio -> Tips & Solutions

 php / mysql join example

Print topic Send  topic

Author Message

Posts: 639
Posted: 06/02/2013, 8:14 AM

*** Notice the declaration of some variables as "%" for use with LIKE to provide wildcard function when there is no value that can be retrieved for it using CCGetParam...

*** Date searches where the date is retrieved from the form, converted to integer and then into DB format are on (date) fields only, not on (datetime) fields

$siteid = CCGetFromGet("siteid");
$sitename = CCGetFromGet("sitename");

$ordertypeid = CCGetParam("s_ordertypeid");
if ($ordertypeid > 0)
$ordertypeid = CCGetParam("s_ordertypeid");
$ordertypeid = "%";

$supplierid = CCGetParam("s_supplierid");
if ($supplierid > 0)
$supplierid = CCGetParam("s_supplierid");
$supplierid = "%";

$accountcodeid = CCGetParam("s_accountcodeid");
if ($accountcodeid > 0)
$accountcodeid = CCGetParam("s_accountcodeid");
$accountcodeid = "%";

$duedate1 = CCGetParam("s_duedate1");
$duedate2 = CCGetParam("s_duedate2");
$duedate1 = strtotime($duedate1);
$duedate2 = strtotime($duedate2);
$duedate1= date("Y/m/d",$duedate1);
$duedate2= date("Y/m/d",$duedate2);
$added1 = CCGetParam("s_added1");
$added2 = CCGetParam("s_added2");
$completed1 = CCGetParam("s_completed1");
$completed2 = CCGetParam("s_completed2");

$constaxincex= CCGetSession("constaxincex");
$constaxcode = CCGetSession("constaxcode");
$constax= $constaxincex."-".$constaxcode;

if($completed1 != NULL and $added1 != NULL){

//echo "1";
//looks ok

$added1 = strtotime($added1);
$added1= date("Y/m/d",$added1);
$added2 = strtotime($added2);
$added2= date("Y/m/d",$added2);

$completed1 = strtotime($completed1);
$completed1 = date("Y/m/d",$completed1);
$completed2 = strtotime($completed2);
$completed2 = date("Y/m/d",$completed2);

$db = new clsDBFM();

$sql = "SELECT sitename AS 'Site Name', idorder as 'Order ID', added AS 'Added', duedate AS 'Due Date',
inprogress AS 'In Progress', completed AS 'Completed', displayname AS 'Supplier', description AS 'Description',
accountcode AS 'Account Code', cost AS 'Cost ($constax)', invoicenumber AS 'Invoice Number', invoicecost AS 'Invoice Cost ($constax)',
ordertype AS 'Order Type'"
." FROM (((orders LEFT JOIN ordertype ON
orders.ordertypeid = ordertype.idordertype) LEFT JOIN supplier ON
orders.supplierid = supplier.idsupplier) LEFT JOIN accountcode ON
orders.accountcodeid = accountcode.idaccountcode) LEFT JOIN site ON orders.siteid = site.idsite"
." WHERE orders.siteid = '$siteid' AND ordertype.idordertype LIKE '$ordertypeid'
AND accountcode.idaccountcode LIKE '$accountcodeid'
AND orders.supplierid LIKE '$supplierid'
AND (duedate >= '$duedate1' AND duedate <= '$duedate2')
AND (added >= '$added1' AND added <= '$added2')
AND (completed1 >= '$completed1' AND completed1 <= '$completed2')
ORDER BY duedate desc";

Central Coast, NSW, Australia.

View profile  Send private message

Add new topic Subscribe to topic   

These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit

Internet Database

Visually create Web enabled database applications in minutes.

Home   |    Search   |    Members   |    Register   |    Login

Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by  and YesSoftware, Inc.