[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP MYSQL statement

Posted on 2010-11-30
9
Medium Priority
?
277 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
  • 6
  • 2
9 Comments
 
LVL 19

Accepted Solution

by:
Bardobrave earned 1600 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 400 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month20 days, 12 hours left to enroll

865 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