Display data in tabular format by year and month

[mythri]

Hi,

 

I want to display my data in this format

 

Expense data i am getting like this

 <td><?php
	$in = "SELECT sales_invoice.invoice_id as INID, DATE_FORMAT(sales_invoice.date_invoiced,'%M') AS month, sales_invoice_line_items.invoice_id, SUM(sales_invoice_line_items.sub_total) AS Total, SUM(sales_invoice_line_items.tax_amount) AS total_tax FROM sales_invoice INNER JOIN sales_invoice_line_items ON sales_invoice.invoice_id=sales_invoice_line_items.invoice_id GROUP BY sales_invoice.invoice_id, DATE_FORMAT(sales_invoice.date_invoiced, '%Y-%m')";
	
	$in1 = mysql_query($in) or die (mysql_error());
	$total=0;
	$tax =0;
	while($income = mysql_fetch_array($in1))
	{
		
		$total +=$income['Total']; 
		$tax +=$income['total_tax'];
		echo $total - $tax; }
    ?>
    
    </td>

And Profit i am getting like this

<td><?php
	$in = "SELECT purchase_invoice.invoice_id as INID, DATE_FORMAT(purchase_invoice.date_invoiced,'%M') AS month, purchase_invoice_line_items.invoice_id, SUM(purchase_invoice_line_items.sub_total) AS Total, SUM(purchase_invoice_line_items.tax_amount) AS total_tax FROM purchase_invoice INNER JOIN purchase_invoice_line_items ON purchase_invoice.invoice_id=purchase_invoice_line_items.invoice_id GROUP BY purchase_invoice.invoice_id, DATE_FORMAT(purchase_invoice.date_invoiced, '%Y-%m')";
	
	$in1 = mysql_query($in) or die (mysql_error());
	$total=0;
	$tax =0;
	while($income = mysql_fetch_array($in1))
	{
		
		$total +=$income['Total']; 
		$tax +=$income['total_tax'];
		echo $total - $tax; }
    ?>
    
    </td>

I dont know how to display data like this. Do i need to change the the table structure in my database? Please suggest

[Barand]

store the data in arrays as you process the query results

 

expense[year][month] = exp_total

income[year][month] = inc_total

 

Then it's a matter of looping through the arrays to output. Because you want Apr as month 0 and March as month 11 then the month index in the arrays will be

 

(month + 8 ) % 12

 

and you will have to check the month to see if it goes in this year or the next

Edited December 1, 2014 by Barand

[mythri]

@ Barand, Sorry for asking the samething again. But i tried , i did not get which result i have to store as array and how to loop it. 

[Barand]

As you want to output income and expenditure totals then those might be good totals to store, don't you think?

 

Something like this

$mysqli = new mysqli(HOST,USERNAME,PASSWORD,'test');

$inSql = "SELECT 
        , YEAR(si.date_invoiced) as year
        , MONTH(si.date_invoiced) AS month
        , SUM(li.sub_total) AS total
        , SUM(li.tax_amount) AS total_tax 
        FROM sales_invoice si
        INNER JOIN sales_invoice_line_items li
            ON si.invoice_id = li.invoice_id 
        GROUP BY year, month";
        
$exSql = "SELECT 
        , YEAR(pi.date_invoiced) AS year
        , MONTH(pi.date_invoiced) AS month
        , SUM(li.sub_total) AS total
        , SUM(li.tax_amount) AS total_tax 
        FROM purchase_invoice pi
        INNER JOIN purchase_invoice_line_items li
            ON pi.invoice_id = li.invoice_id 
        GROUP BY year, month";

$data = array();
// GET INCOME TOTALS
$res = $db->query($inSql);
while (list($y, $m, $tot, $tax) = $res->fetch_row()) {
    if ($m < 4) $y--;   // jan, feb, mar belong in previous year
    $m = ($m +  % 12; // adjust month number
    $data[$m][$y]['i'] += $tot - $tax;
}

// GET EXPEND TOTALS
$res = $db->query($exSql);
while (list($y, $m, $tot, $tax) = $res->fetch_row()) {
    if ($m < 4) $y--;   // jan, feb, mar belong in previous year
    $m = ($m +  % 12; // adjust month number
    $data[$m][$y]['x'] += $tot - $tax;
}

// LOOP THROUGH DATA ARRAY AND OUTPUT TOTALS
$mnames = array('Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
$totals = array();
foreach ($data as $m => $mdata) {
    echo "<tr><td>{$mnames[$m]}</td>";
    foreach ($mdata as $y => $ydata) {
        echo "<td>{$ydata[i]}</td><td>{$ydata[x]}</td>";
        $totals[$y]['i'] += $ydata['i'];
        $totals[$y]['x'] += $ydata['x'];
    }
    echo "</tr>\n";
}

// echo total row here from $totals array