CODEWITHSUNDEEP
phpmysql
Irwan Setiawan
Irwan Setiawan

Reputation: 11

Showing Data 12 months in PHP

I have the query:

SELECT MONTH(tgl) AS month, SUM(jumlahA) AS A, SUM(jumlahB) AS B
FROM tabel where YEAR(tgl)='2002' GROUP BY MONTH(tgl) order by 1;

The Result is:

month   A       B
----------------------
1       85      0
2       38      0
9       34      23

The problem is how to show the data completely a year (12 months) in PHP where the month has no data put 0 to A and B. Actually I want to create bar graphic with month axis (jan, feb, ..., des)

UPDATE

Here goes some of my PHP code:

echo "<categories>\n"; 
    echo "<category name='Jan' />\n"; 
    echo "<category name='Feb' />\n"; 
    echo "<category name='Mar' />\n"; 
    echo "<category name='Apr' />\n"; 
    echo "<category name='Mei' />\n"; 
    echo "<category name='Jun' />\n"; 
    echo "<category name='Jul' />\n"; 
    echo "<category name='Agu' />\n"; 
    echo "<category name='Sept' />\n"; 
    echo "<category name='Okt' />\n"; 
    echo "<category name='Nop' />\n"; 
    echo "<category name='Des' />\n"; 
echo "</categories>\n";

echo "<dataset seriesname='Hulu' color='AFD8F8'>"; 
$strQuery="SELECT MONTH(tgl) AS month, SUM(jumlahA) AS A, SUM(jumlahB) AS B FROM tabel where YEAR(tgl)='2002' GROUP BY MONTH(tgl) order by 1"; 
$hasil=mysql_query($strQuery); 
while ($data = mysql_fetch_array($hasil)) { 
    echo "<set value='".$data[1]."' />"; 
} 
echo "</dataset>";

Upvotes: 1

Views: 427

Answers (1)

DontVoteMeDown
DontVoteMeDown

Reputation: 21465

Make an array with the same values tha will be returned from MONTH(tgl) AS month. Assuming those values are the same of the categories(if not you may change it), do something like this:

$months = array('Jan'=>null, 'Feb'=>null, 'Mar'=>null, 'Apr'=>null, 'Mei'=>null, 
'Jun'=>null, 'Jul'=>null, 'Agu'=>null, 'Sept'=>null, 'Okt'=>null, 'Nop'=>null, 'Des'=>null);

Nice. Now change your while to this:

while ($data = mysql_fetch_array($hasil)) { 
    $months[$data[0]] = "<set value='".$data[1]."' />"; 
}

Now, in the array you got all the months each one with it respectively value. The months that have no value are NULLs. Now it's easy:

foreach ($months as $month) {
    if ($month) {
        echo $month;
    }
    else {
        echo "<set value='0' />";
    }
}

This came right from my mind, I didn't tested it and I hope it works and helps!

Upvotes: 1

Related Questions