CodeCharge Studio
search Register Login  

Web Reports

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

YesSoftware Forums -> CodeCharge Studio -> Tips & Solutions

 php / mysql join example

Print topic Send  topic

Author Message
MichaelMcDonald

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");
else
$ordertypeid = "%";

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

$accountcodeid = CCGetParam("s_accountcodeid");
if ($accountcodeid > 0)
$accountcodeid = CCGetParam("s_accountcodeid");
else
$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";
$db->query($sql);

}
_________________
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 http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


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