Results not showing correctly from database

General PHP Help
#1

Hi,

I am trying to retrieve the sum of my amounts according to its type (income/expense) that is stored in the database from 01/2004 until current month&year.

When I try to display it, it is support to show me the amounts in every month of every year, but instead it only shows me a few of the amounts.
When i try to print whether it is displaying ALL the months and years, it does that currectly.

Here is my code:

global $y, $m;	
	for($y=2004;$y<=date("Y");$y++){	//year
		for($m=1;$m<=12;$m++){	//month
			#echo $m.' - '.$y.'<br />';			
			//INCOME TOTAL
			$total1 = 0;
			$result1 = mysql_query("SELECT sum(ie_amount) as total_amount FROM income_expense WHERE ie_type=0 AND ie_date_entered LIKE '$y-$m%'") or trigger_error("Query: $queryn<br />MySQL Error: " .mysql_error());	// Run the query.
			if ($result1 && mysql_num_rows($result1) > 0) {
			  $query_data = mysql_fetch_array($result1);
			  $total1= (float) $query_data["total_amount"];
			}
			//EXPENSE TOTAL
			$total2 = 0;
			$result2 = mysql_query("SELECT sum(ie_amount) as total_amount FROM income_expense WHERE ie_type=1 AND ie_date_entered LIKE '$y-$m%'") or trigger_error("Query: $queryn<br />MySQL Error: " .mysql_error());	// Run the query.
			if ($result2 && mysql_num_rows($result2) > 0) {
			  $query_data = mysql_fetch_array($result2);
			  $total2= (float) $query_data["total_amount"];
			}
			
			#$m = date("F",strtotime($m));
			echo $m.'-'.$y.' === '.formatCurrency($total1,2).'-'.formatCurrency($total2,2).'<br />';
		}
	}

and there are my results:
(month-year === income-expense)

1-2004 === $52,763.98-$767.00
2-2004 === $0.00-$0.00
3-2004 === $0.00-$0.00
4-2004 === $0.00-$0.00
5-2004 === $0.00-$0.00
6-2004 === $0.00-$0.00
7-2004 === $0.00-$0.00
8-2004 === $0.00-$0.00
9-2004 === $0.00-$0.00
10-2004 === $43,513.98-$0.00
11-2004 === $6,250.00-$0.00
12-2004 === $3,000.00-$767.00
1-2005 === $24,475.00-$32,105.56
2-2005 === $0.00-$0.00
3-2005 === $0.00-$0.00
4-2005 === $0.00-$0.00
5-2005 === $0.00-$0.00
6-2005 === $0.00-$0.00
7-2005 === $0.00-$0.00
8-2005 === $0.00-$0.00
9-2005 === $0.00-$0.00
10-2005 === $3,350.00-$10,230.00
11-2005 === $6,700.00-$0.00
12-2005 === $14,425.00-$21,875.56
1-2006 === $22,100.00-$5,774.31
2-2006 === $0.00-$0.00
3-2006 === $0.00-$0.00
4-2006 === $0.00-$0.00
5-2006 === $0.00-$0.00
6-2006 === $0.00-$0.00
7-2006 === $0.00-$0.00
8-2006 === $0.00-$0.00
9-2006 === $0.00-$0.00
10-2006 === $7,450.00-$0.00
11-2006 === $0.00-$0.00
12-2006 === $14,650.00-$5,774.31
1-2007 === $18,900.00-$29,705.00
2-2007 === $0.00-$0.00
3-2007 === $0.00-$0.00
4-2007 === $0.00-$0.00
5-2007 === $0.00-$0.00
6-2007 === $0.00-$0.00
7-2007 === $0.00-$0.00
8-2007 === $0.00-$0.00
9-2007 === $0.00-$0.00
10-2007 === $7,600.00-$21,550.00
11-2007 === $10,800.00-$1,605.00
12-2007 === $500.00-$6,550.00

As you can see, there are too many zeros. and it looks like a repetion on specific months. i think that is the problem, but i cant put my finger on the solutions.

#2

First of all: why are you making $y and $m global variables? I don’t think it serves a purpose as in the next few lines, you’re re-initializing them.

Second: how are your dates stored in the SQL database? I’m thinking your query doesn’t pick up any of the single-digit months because they’re prepended by a 0. Check if that’s true.

#3

Great, didnt think about that.
It worked, i get to see it all perfectly :)

I have one more question…

Is there such a thing in PHP a Running Total?

Meaning, now I have a subtotal column in my html table. It displays the subtotal of credit minus debit. I now want to display that subtotal in another column, but have it add previous row to the next row.

Here is an example:

Subtotal:
2
-5
9
7
2.5

My running total should look like this:
2
-3
6
13
15.5

Make sense?

How do I go about doing that in PHP? Can I do this by just taking the subtotal instead of quiering the Database?

Thanks again

Sponsor our Newsletter | Privacy Policy | Terms of Service