PHP MYSQL statement

Hi Guys,

thank you for taking the time to read my question.  basically I have the following query

$queryDebtors  = "SELECT ";
$queryDebtors .= "((t2.de_listed_date)+19000000),";
$queryDebtors .= "count(t2.de_principal),";
$queryDebtors .= "count(t1.tr_account),";
$queryDebtors .= "sum(if(t1.tr_account==16,((t1.tr_to_agency)+(t1.tr_to_client))*-1,0)), ";
$queryDebtors .= "sum(if(t1.tr_account!=16,((t1.tr_to_agency)+(t1.tr_to_client)),0)), ";
$queryDebtors .= "sum(t2.de_principal), ";
$queryDebtors .= "sum(t2.de_paid), ";
$queryDebtors .= "sum(t2.de_owing) ";
$queryDebtors .= " FROM " . $database . ".de_transaction as t1 ";
$queryDebtors .= " LEFT JOIN " . $database . ".debtor as t2 on t1.debtor_rowid = t2.debtor_rowid ";

$queryDebtors .= " WHERE (t2.de_listed_date > " . $searchdate . ")";
$queryDebtors .= " GROUP BY 1 ORDER BY 1 DESC";

but I want to add the following field into it.

if(t1.tr_account=0,sum(t2.de_paid)
WHERE t1.tr_payment_date <= ($today-19000000) and t1.tr_payment_date >= ($monthago-19000000)

Been having a few issues due to the different where clause....is this possible?

thank you in advance for your assistance it is most appreciated!!
mavmanauNetwork Engineer/SysadminAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BardobraveConnect With a Mentor Commented:
If your both WHERE clauses are auto exclusive you should use a if-else statement to select wich one of the two WHERE clauses do you want to use.

You can also use something like:

if(t1.tr_account=0,sum(t2.de_paid)
$queryDebtors .= WHERE (t2.de_listed_date > " . $searchdate . ") AND t1.tr_payment_date <= ($today-19000000) and t1.tr_payment_date >= ($monthago-19000000)"
else
$queryDebtors .= WHERE (t2.de_listed_date > " . $searchdate . ") "

If they arent auto exclusive.
0
 
Loganathan NatarajanConnect With a Mentor LAMP DeveloperCommented:
try this
<?
$queryDebtors  = "SELECT ";
$queryDebtors .= "((t2.de_listed_date)+19000000),";
$queryDebtors .= "count(t2.de_principal),";
$queryDebtors .= "count(t1.tr_account),";
$queryDebtors .= "sum(if(t1.tr_account==16,((t1.tr_to_agency)+(t1.tr_to_client))*-1,0)), ";
$queryDebtors .= "sum(if(t1.tr_account!=16,((t1.tr_to_agency)+(t1.tr_to_client)),0)), ";
$queryDebtors .= "sum(t2.de_principal), ";
$queryDebtors .= "sum(t2.de_paid), ";
$queryDebtors .= "sum(t2.de_owing) ";
$queryDebtors .= " FROM " . $database . ".de_transaction as t1 ";
$queryDebtors .= " LEFT JOIN " . $database . ".debtor as t2 on t1.debtor_rowid = t2.debtor_rowid ";

if(t1.tr_account=0,sum(t2.de_paid) {
	$queryDebtors .= " WHERE t1.tr_payment_date <= ($today-19000000) and t1.tr_payment_date >= ($monthago-19000000)";
} else {
	$queryDebtors .= " WHERE (t2.de_listed_date > " . $searchdate . ")";
}
$queryDebtors .= " GROUP BY 1 ORDER BY 1 DESC";

?>

Open in new window

0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
They are auto exclusive...if by that you mean that for the original code the listed date is greater than the search date, but if it is for the new column I want to add into the query it is only to add up the de_paid items that are made between the two dates.

essentially I want the data to come back in query so I can do an XLS transformation on it,  (that part is all good, just need to add in the extra column).

I tried plugging that code in logudotcom but I got a coding error.

rather than altering the where is statement, is it possible to specifiy in the select clause

if(t1.account=0 and de.listeddate >= $today and de.listeddate <=$monthago,sum(t2.de_paid))

or something like that???  sorry bit rusty on my SQL language
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
mavmanauNetwork Engineer/SysadminAuthor Commented:
oops must have missed the second part of your code there Bardobrave.  having a look at it now.  
0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
The where condition ((tr_paid_date >=$today) and (tr_paid_date <=$monthago))

for the select clause would only be for that line, f(t1.tr_account=0,sum(t2.de_paid) whereas the (listeddate > searchdate) is valid for the rest of the select clause items, or is it not possible to have it using 2 different where clauses in a query?

could I perhaps have a query within a query???
0
 
BardobraveCommented:
A WHERE clause applies to all elements on query.

If you want to query based on two different where clauses and apply one of them to some data and both to some other what you need is to use two different queries, as you are looking for two different sets of results.
0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
actually I have what I need, just need to convert it into the proper php mysql text...i think it is messy but it does work on SQL server query manager.

SUM(CASE WHEN (t1.tr_account = 1) AND (t1.tr_payment_date >= '1101101') AND (t1.tr_payment_date <= '1101130') THEN (t2.de_paid) ELSE 0 END)

does this look ok to you?

$queryDebtors .= "sum(if(t1.tr_account==1 and t1.tr_payment_date >= $monthago and (t1.tr_payment_date <= $today),(t2.de_paid),0)), ";

0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
all sorted.  Thank you for the assist!! couldn't have done it without your first lot of code example...sorta took that and ran with it.
0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
awesome help thank you guys.....and thank you for responding really quickly as well..  Very much appreciated.  Who needs a team when I have this site!!
0
All Courses

From novice to tech pro — start learning today.