php and mysql help - grouping by date

Discussion in 'Web Design and Development' started by twoodcc, Jan 30, 2010.

  1. twoodcc macrumors P6

    twoodcc

    Joined:
    Feb 3, 2005
    Location:
    Right side of wrong
    #1
    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:
    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>";
     
  2. twoodcc thread starter macrumors P6

    twoodcc

    Joined:
    Feb 3, 2005
    Location:
    Right side of wrong
    #2
    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:
    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>";
     
  3. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #3
    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.
     
  4. twoodcc thread starter macrumors P6

    twoodcc

    Joined:
    Feb 3, 2005
    Location:
    Right side of wrong
    #4
    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
     
  5. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #5
    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.
     
  6. twoodcc thread starter macrumors P6

    twoodcc

    Joined:
    Feb 3, 2005
    Location:
    Right side of wrong
    #6
    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
     

Share This Page