Need help with mysql data to cake chart

[silverviper]

Hi

I have some kind of contact form that collects

name
adress
zip code
city

and stores it in a mysql database.

I need to count the occurance of each zip code in order to calculate
the percentage each zip code represents. Then store the querry result in an array

the four digit index represents the zip code, and the key represents the occurrance of each zip code

array( '4512' => 4, '2356' => 9, '9785' => 15, '0145' => 26 );


it's been a few years since i've done any php. any help is much apprechiated.

thx

Silverviper

[silverviper]

I've tried the following

$query = "SELECT pstnr,COUNT(pstnr) AS count FROM addresses GROUP BY pstnr";
$result = mysqli_query($dbc, $query);
    
$zipcounts = array();
    
while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
    $zipcounts[$row['pstnr']] = $row['count'];
}

give me this error

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result

[silverviper]

found a typo

$query = "SELECT pstnr,COUNT(pstnr) AS count FROM addresses GROUP BY pstnr";
$result = mysqli_query($dbc, $query);
    
$zipcounts = array();
    
while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
    $zipcounts[$row['pstnr']] = $row['count'];
}

corrected code

$query = "SELECT pstnr,COUNT(pstnr) AS count FROM addresses GROUP BY pstnr";
$result = mysqli_query($dbc, $query);
    
$zipcounts = array();
    
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $zipcounts[$row['pstnr']] = $row['count'];
}

[AnthonySterling]

Your query failed - try executing it outside of php or see the contents of msqli_error_*.

[silverviper]

Hi

I got it working, the code follows for those who are interessted

<?

include 'includes/config.php';
include 'includes/functions.php';

connectDB($host, $dbUser, $dbPass, $dbName);
    $results = queryDB("SELECT pststed,COUNT(pststed) AS zipcount FROM konkurranse GROUP BY pststed"); 
closeDB(); 

$pststedcounts = array();   
$psttot = 0;   
while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) {
   $pststedcounts[trim($row['pststed'])] = $row['zipcount'];
   $psttot = $psttot + $row['zipcount'];
} 
?>
<table border="0">
  <tr>
<?


foreach( $pststedcounts as $key => $value){
    $width = ($value * 100 / $psttot)*30;
    if($key==""){
        $key="???";
    }
    print "<tr><td width='130'>$key</td>";
    print "<td align='right'>".$value*100/$psttot."%</td>";
    print '<td><img src="img/graph.jpg" width="'.$width.'" height="14"></td></tr>';
}



?>
  </tr>
</table>

I usually use 3 functions when doing mysql stuff, probably slows down my code, but speed isn't very important in this case.

My results are shown in a bar chart for now, i'll replace the bar chart with a Pie chart later.