php and mysql help - grouping by date

[twoodcc]

so i'm making my own financial site for my own purposes, and i use php and mysql. now, i've made a page that groups transactions by category, year and month. and i echo those out. and it works, but i want to break it up by each month, so it's not just a continuous table of data.

any ideas? also, i would like to show a percentage for each each category, but i can only get the percentage of all transactions, and not the percentage of that month.

here is part of the code:

$result1 = mysql_query("SELECT date, category, type, amount, sum(amount)
						   FROM transactions 
						   WHERE username = '$username' and category != 'transfer' and type != 'income'
						   GROUP by category, year(date), month(date)
						   ORDER by date desc");
	
	echo "<h3>Transactions</h3>";
	echo "<table border='1'>
	<tr>
	<td align='center'><b>Date</b></td>
	<td align='center'><b>Category</b></td>
	<td align='center'><b>Amount</b></td>
	<td align='center'><b>Percent</b></td>
	</tr>";
	echo $sum;
	while($row = mysql_fetch_array($result1))
	{
		
		echo "<tr>";
		echo "<td>" . $row['date'] . "</td>";
		echo "<td>" . $row['category'] . "</td>";
		echo "<td>" . number_format($row['sum(amount)'], 2) . "</td>";
		//$sum += $row['sum(amount)'];
		$cat = ($row['sum(amount)']/$sum)*100;
		echo "<td>" . number_format($cat, 2) . "</td>";
		echo "</tr>";
		

	}
	
	echo "</table>";

 

[twoodcc]

well i figured out a quick and dirty way to fix the percentages, but still not sure how to separate the big table by month.

here is the updated code:

$result1 = mysql_query("SELECT date, category, type, amount, sum(amount)
						   FROM transactions 
						   WHERE username = '$username' and category != 'transfer' and type != 'income'
						   GROUP by category, year(date), month(date)
						   ORDER by date desc");
	
	echo "<h3>Transactions</h3>";
	echo "<table border='1'>
	<tr>
	<td align='center'><b>Date</b></td>
	<td align='center'><b>Category</b></td>
	<td align='center'><b>Amount</b></td>
	<td align='center'><b>Percent</b></td>
	</tr>";
	echo $sum[0];
	$count = sizeof($sum);
	/*foreach ($sum as $s)
	{
		
	}*/
	while($row = mysql_fetch_array($result1))
	{
		
		echo "<tr>";
		echo "<td>" . $row['date'] . "</td>";
		echo "<td>" . $row['category'] . "</td>";
		echo "<td>" . number_format($row['sum(amount)'], 2) . "</td>";
		echo "<b>";
		for($i=0; $i < $count; $i++)
		{
		$cat = ($row['sum(amount)']/$sum[$i])*100;
			
			$i+=29;
			
		echo "<td>" . number_format($cat, 2) . "</b></td>";
		echo "</tr>";
		}
	}
	
	echo "</table>";

 

[angelwatt]

You could use multiple queries.

Another route, keep track of the current month that you're looking at (as you're looping through the query results). When it changes, you know you're on a new one. I used that for something once before. Can't remember what exactly though so can't go look. May have been something from the forums, but not sure.

 

[twoodcc]

You could use multiple queries.

Another route, keep track of the current month that you're looking at (as you're looping through the query results). When it changes, you know you're on a new one. I used that for something once before. Can't remember what exactly though so can't go look. May have been something from the forums, but not sure.

thanks for the reply. how would multiple queries work? the number of months that go by will only continue to grow, so i couldn't have a query for each month.

i'll try to figure out how to keep track of the current month. let me know if you can think of a way

 

[angelwatt]

thanks for the reply. how would multiple queries work? the number of months that go by will only continue to grow, so i couldn't have a query for each month.

i'll try to figure out how to keep track of the current month. let me know if you can think of a way

Well, the multiple query thing was more an idea for the percentage question you first had, but you got that figured out. As far as tracking the current month, just use a variable $currMonth and inside your while loop do an if statement that looks at the month portion of your date (maybe by using a regex or create a function to check it) and output a new header row for the separate month, or if you're creating separate tables it'll take more code rearranging.

 

[twoodcc]

Well, the multiple query thing was more an idea for the percentage question you first had, but you got that figured out. As far as tracking the current month, just use a variable $currMonth and inside your while loop do an if statement that looks at the month portion of your date (maybe by using a regex or create a function to check it) and output a new header row for the separate month, or if you're creating separate tables it'll take more code rearranging.

thanks. i was able to add a 'MONTH(date)' to the select statement in mysql to get the month, and used an if statement along with it. now it works! thanks for the help