0

I have this billing table from wherein I get records as per the report requirement.

The array I get is like this: 

Array(
[0] => stdClass Object
    (
        [bid] => 3
        [uid] => 2
        [total_inc] => 100
        [total_exp] => 55
        [mon] => 1
        [year] => 2012
        [mstdatereg] => 2012-03-14
    )

[1] => stdClass Object
    (
        [bid] => 2
        [uid] => 3
        [total_inc] => 85
        [total_exp] => 45
        [mon] => 1
        [year] => 2012
        [mstdatereg] => 2012-03-14
    )

[2] => stdClass Object
    (
        [bid] => 1
        [uid] => 8
        [total_inc] => 130
        [total_exp] => 75
        [mon] => 1
        [year] => 2012
        [mstdatereg] => 2012-03-14
    )

[3] => stdClass Object
    (
        [bid] => 5
        [uid] => 25
        [total_inc] => 130
        [total_exp] => 65
        [mon] => 2
        [year] => 2012
        [mstdatereg] => 2012-03-14
    )

[4] => stdClass Object
    (
        [bid] => 4
        [uid] => 27
        [total_inc] => 75
        [total_exp] => 50
        [mon] => 2
        [year] => 2012
        [mstdatereg] => 2012-03-14
    )

[5] => stdClass Object
    (
        [bid] => 10
        [uid] => 3
        [total_inc] => 180
        [total_exp] => 100
        [mon] => 3
        [year] => 2012
        [mstdatereg] => 2012-04-05
    )

[6] => stdClass Object
    (
        [bid] => 6
        [uid] => 12
        [total_inc] => 60
        [total_exp] => 35
        [mon] => 3
        [year] => 2012
        [mstdatereg] => 2012-03-14
    )

[7] => stdClass Object
    (
        [bid] => 7
        [uid] => 22
        [total_inc] => 160
        [total_exp] => 90
        [mon] => 3
        [year] => 2012
        [mstdatereg] => 2012-03-14
    )

[8] => stdClass Object
    (
        [bid] => 9
        [uid] => 3
        [total_inc] => 115
        [total_exp] => 70
        [mon] => 4
        [year] => 2012
        [mstdatereg] => 2012-03-16
    )
)

What I have done through looping is the result which is like this:

        January, 2012
        ==========
        Income  Expense
    2   100     55
    3   85      45
    8   130     75
    ---------------------------------
Total   315     175

        February, 2012
        ===========
        Income  Expense
    25  130     65
    27  75      50
    ---------------------------------
Total   205     115

        March, 2012
        Income  Expense
    3   180     100
    12  60      35
    22  160     90
    ---------------------------------
Total   400     225

        April, 2012
        Income  Expense
    3   115     70
    ---------------------------------
Total   115     70

Net Total Income: 1035
Net Total Expense: 585code here

However what I want is

Sr.No   Member  1, 2012     2, 2012     3, 2012     4, 2012     Total
                Inc|Exp     Inc|Exp     Inc|Exp     Inc|Exp     Inc|Exp 
=======================================================================
1       2       100|55                                          100|55
2       3       85|45                   180|100     115|70      380|215
3       8       130|75                                          130|75  
4       25                  130|65                              130|65
5       27                  75|50                               75|50
6       12                              60|35                   60|35
7       22                              160|90                  160|90  
=======================================================================
Total           315|175     205|115     400|225     115|70      1035|585

Prior to posting, I searched for such problem and this is what I found: Building a "crosstab" or "pivot" table from an array in php

I tried to make it work according to my requirement but cud not do so.

Been trying for many days now. Any help will be appreciated.

Community
  • 1
  • 1
Phoenix Marino
  • 1

1 Answers1

0

I think you'll have better luck using SQL to do most of this number crunching for you.

Relatively simple process, then:

  1. Determine the months (columns) you want to represent in the table.
  2. Use GROUP BY to break your data into rows by Sr. No (or whatever you want to use)
  3. Use WHERE clause to cut out data that's not in the date range you've chosen (i.e. if Report is only for Feb & March, don't include Jan)
  4. Totals column is sum of Income or Expense for given row (remember, rows are grouped using criteria you chose earlier)
  5. Individual month column is sum of IF(Date is in this column's month, Amount, 0)
  6. Totals row is calculated in your PHP

You can generate the month column code programmatically.

Below's an example of some code to generate the query for a given set of month-year combos (untested). It's trivial to get it into a table from here (you just need to calculate the totals row in PHP).

$qstr = 'SELECT bid, uid, ';
$reportMonths = array(1 => 2012, 2 => 2012, 3 => 2012, 4 => 2012);

foreach ($reportMonths as $mon => $year) {
    $qstr .= "SUM(IF(mon=$mon AND year=$year),total_inc,0)) as $mon-inc, SUM(IF(mon=$mon AND year=$year,total_exp,0)) as $mon-exp, ";
}

$qstr .= 'SUM(total_inc) as total-inc, SUM(total_exp) as total-exp from bill_mst WHERE ';

foreach ($reportMonths as $mon => $year) {
    $qstr .= '(mon=$mon AND year=$year) OR ';
}
// chop off last or
$qstr = substr($qstr, 0, -3);
$qstr .= 'GROUP BY bid, uid ASC';

$res = mysql_query($qstr);
untitled90
  • 98
  • 5