Solved

PHP MYSQL statement

Posted on 2010-11-30
9
256 Views
Last Modified: 2012-05-10
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!!
0
Comment
Question by:mavmanau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 19

Accepted Solution

by:
Bardobrave earned 400 total points
ID: 34237643
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
 
LVL 36

Assisted Solution

by:Loganathan Natarajan
Loganathan Natarajan earned 100 total points
ID: 34237646
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
 

Author Comment

by:mavmanau
ID: 34237843
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mavmanau
ID: 34238501
oops must have missed the second part of your code there Bardobrave.  having a look at it now.  
0
 

Author Comment

by:mavmanau
ID: 34238595
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
 
LVL 19

Expert Comment

by:Bardobrave
ID: 34238703
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
 

Author Comment

by:mavmanau
ID: 34239031
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
 

Author Comment

by:mavmanau
ID: 34239158
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
 

Author Closing Comment

by:mavmanau
ID: 34239171
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question