PHP Grouping data to years

[Danny620]

How can i group the data by year to build the graph

 

I need the data to be like so

 

2012

0 - total customers

0 - total customers

0 - total customers

0 - total customers

0 - total customers

9 - total customers

5 - total customers

3 - total customers

5 - total customers

 

however i only have this data

 

total_customers 	month 	year
1 	Aug 	2011
9 	Oct 	2011
8 	Nov 	2011
4 	Dec 	2011
4 	Jan 	2012

 

so i need to make it show 0 for the months i dont have data for

 

<div id="dash_chart" class="portlet x9">

		<div class="portlet-header">
			<h4>Customer Growth</h4>

			<ul class="portlet-tab-nav">
				<li class="portlet-tab-nav-active"><a href="#tab1" rel="tooltip" title="Customers">Customers </a></li>				
				<li class=""><a href="#tab2" rel="tooltip" title="Sales over last 48 hours.">Sales </a></li>
			</ul>
		</div> <!-- .portlet-header -->

		<div class="portlet-content">				
			<div id="tab1" class="portlet-tab-content portlet-tab-content-active">
                
                <?php 

			echo '<table class="stats" title="area" width="100%" cellpadding="0" cellspacing="0">
			<caption>Customer Base Growth</caption>
			<thead>
                <tr>';

			$monthNames = Array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

			$i = 0;

			foreach ($monthNames as $month){

			echo '<th>'.$month.'</th>'."\n";

			}

			echo '</tr>
				  </thead>';

                      $q = "SELECT count(cus_id) as total_customers, DATE_FORMAT(sign_date, '%b') as month, YEAR(sign_date) as year FROM customers 
				  GROUP BY YEAR(sign_date), MONTH(sign_date) ORDER BY sign_date ASC";
			$r = @mysqli_query ($dbc, $q);

			$data = array();

			if (mysqli_affected_rows($dbc) >= 1) {

				echo '<tbody>						
						<tr>
  							<th>2011</th>';

				while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {


				}

			}

						echo '</tr>';

						?>	
                            <tr>
							<th>2012</th>
							<td>3</td>
							<td>4</td>
							<td>2</td>
							<td>0</td>
							<td>0</td>
                                <td>0</td>
							<td>0</td>
							<td>3</td>
							<td>5</td>
                                <td>3</td>
                                <td>9</td>									
						</tr>							
					</tbody>
				</table>

[Psycho]

Not tested, so there may be some typos

 

<?php 

$monthNames = Array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
$table_head = "<th>Year</th>";
foreach ($monthNames as $month)
{
    $table_head .= "<th>{$month}</th>\n";
}

$query = "SELECT count(cus_id) as total_customers, DATE_FORMAT(sign_date, '%b') as month, YEAR(sign_date) as year
          FROM customers
          GROUP BY YEAR(sign_date), MONTH(sign_date)
          ORDER BY sign_date ASC";
$result = @mysqli_query ($dbc, $q);

$output = '';
$current_year = false;
if (mysqli_affected_rows($dbc) >= 1)
{
    while($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
    {
        if($current_year != $row['year'])
        {
            $current_year = $row['year'];
        }
        
        $output .= "<tr>\n";
        $output .= "<th>{$current_year}</th>\n";
        foreach($monthNames as $current_month)
        {
            if($current_month == $row['month'] && $current_year == $row['year'])
            {
                $count = $row['total_customers'];
                $row = mysqli_fetch_array($r, MYSQLI_ASSOC);
            }
            else
            {
                $count = '0';
            }
            $output .= "<td>{$count}</td>\n";
        }
        $output .= "</tr>\n";
    }
}
else
{
    $output = "<tr><td colspan='13'>There were no results.</td></tr>\n"
}

?>	
    <div id="dash_chart" class="portlet x9">
        <div class="portlet-header">
            <h4>Customer Growth</h4>
            <ul class="portlet-tab-nav">
                <li class="portlet-tab-nav-active"><a href="#tab1" rel="tooltip" title="Customers">Customers </a></li>				
                <li class=""><a href="#tab2" rel="tooltip" title="Sales over last 48 hours.">Sales </a></li>
            </ul>
        </div> <!-- .portlet-header -->

        <div class="portlet-content">				
            <div id="tab1" class="portlet-tab-content portlet-tab-content-active">
                <table class="stats" title="area" width="100%" cellpadding="0" cellspacing="0">
		    <caption>Customer Base Growth</caption>
                <thead>
                    <tr><?php echo $table_head; ?></tr>
			<tbody>
                    <?php echo $output; ?>
                </tbody>
                </table>