Solved

PHP MYSQL statement

Posted on 2010-11-30
9
254 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
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 …

829 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