Solved

PHP MYSQL statement

Posted on 2010-11-30
9
252 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

776 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