Pie chart query

Question

narai 0 Newbie Poster

11 Years Ago

Hi, I am facing these problem. I gt 4 category. So for each category, I want to count number of people in each of it.I already have the query but i dont know how to put it in array with $i =$i +1 to count it when no of people increase in the category. I really hope you can help to generate the pie chart.

<?
$result= array("SELECT *  FROM ray, hello,category WHERE 
    ray.hello_id = hello.hello_id AND hello.category_id = category.category_id AND
  category.category_id= '1' ");

    $i =0;
 while($row=mysql_fetch_array($result)){
 $result[$i] = $row[0];
 $i=$i +1;

 }

  <html>

<head>
    <title>hello</title>

    <link class="include" rel="stylesheet" type="text/css" href="jqplot_files/jquery.jqplot.min.css" />
    <link rel="stylesheet" type="text/css" href="jqplot_files/examples.min.css" />
    <link type="text/css" rel="stylesheet" href="jqplot_files/syntaxhighlighter/styles/shCoreDefault.min.css" />
    <link type="text/css" rel="stylesheet" href="jqplot_files/syntaxhighlighter/styles/shThemejqPlot.min.css" />


    <script class="include" type="text/javascript" src="script/jquery-1.6.2.min.js"></script>

    <script type="text/javascript" src="jqplot_files/syntaxhighlighter/scripts/shCore.min.js"></script>
    <script type="text/javascript" src="jqplot_files/syntaxhighlighter/scripts/shBrushJScript.min.js"></script>
    <script type="text/javascript" src="jqplot_files/syntaxhighlighter/scripts/shBrushXml.min.js"></script>

    <script class="include" type="text/javascript" src="jqplot_files/jquery.jqplot.min.js"></script>
    <script class="include" language="javascript" type="text/javascript" src="jqplot_files/plugins/jqplot.pieRenderer.min.js"></script>
    <script class="include" language="javascript" type="text/javascript" src="jqplot_files/plugins/jqplot.donutRenderer.min.js"></script>

    <script type="text/javascript" src="jqplot_files/example.min.js"></script>


    <script type="text/javascript">
        $(document).ready(function(){

            var data2 = [
            [<?php echo $row1['category_name']; ?>,<?php echo $row[0]; ?>],
            [<?php echo $row2['category_name']; ?>, <?php echo $numrows02; ?>], 
            [<?php echo $row3['category_name']; ?>, <?php echo $numrows03; ?>],
            [<?php echo $row4['category_name']; ?>, <?php echo $numrows04; ?>]
          ];
          var plot2 = jQuery.jqplot ('chart2', [data2], 
            {
              seriesDefaults: {
                renderer: jQuery.jqplot.PieRenderer, 
                rendererOptions: {
                  // Turn off filling of slices.
                  fill: false,
                  showDataLabels: true, 
                  // Add a margin to seperate the slices.
                  sliceMargin: 4, 
                  // stroke the slices with a little thicker line.
                  lineWidth: 5
                }
              }, 
              legend: { show:true, location: 'e' }
            }

Edited 11 Years Ago by narai

4 Contributors
13 Replies
194 Views
3 Days Discussion Span
Latest Post 11 Years Ago Latest Post by pritaeas

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

You can change the query to get the count per category:

SELECT category.category_id, COUNT(*) AS usercount
FROM ray, hello, category 
WHERE ray.hello_id = hello.hello_id 
AND hello.category_id = category.category_id 
AND category.category_id= '1'
GROUP BY category.category_id
ORDER BY category.category_id

ajbest 0 Light Poster

11 Years Ago

This will assign an array called categories with the keys being the cat name and the values being the count of records that match that category.

<? 
$result= mysql_query("SELECT *
                FROM ray, hello,category 
                WHERE ray.hello_id = hello.hello_id 
                    AND hello.category_id = category.category_id
                    AND category.category_id= '1' ");
 while($row=mysql_fetch_assoc($result)){
     $categories[$row['category_name']]+=1;
 }
?>

Then when you are converting to your javascript array I would use something closer to this

var data2 = [<? foreach ($categories as $name => $count){
                echo '['.$name.','.$count.'],';
            }?>];

ajbest 0 Light Poster

11 Years Ago

close but....

$result=mysql_query( "SELECT * FROM ray, hello,category WHERE
ray.hello_id = hello.hello_id AND hello.category_id = category.category_id AND
(category.category_id= '1' OR category.category_id= '2' OR category.category_id= '3' OR category.category_id= '4')");

while($row=mysql_fetch_array($result)){
    $categories [$row['category_name']]+=1;
}

?>


<html>
<head>
<title>hello</title>
<link class="include" rel="stylesheet" type="text/css" href="jqplot_files/jquery.jqplot.min.css" />
<link rel="stylesheet" type="text/css" href="jqplot_files/examples.min.css" />
<link type="text/css" rel="stylesheet" href="jqplot_files/syntaxhighlighter/styles/shCoreDefault.min.css" />
<link type="text/css" rel="stylesheet" href="jqplot_files/syntaxhighlighter/styles/shThemejqPlot.min.css" />
<script class="include" type="text/javascript" src="script/jquery-1.6.2.min.js"></script>
<script type="text/javascript" src="jqplot_files/syntaxhighlighter/scripts/shCore.min.js"></script>
<script type="text/javascript" src="jqplot_files/syntaxhighlighter/scripts/shBrushJScript.min.js"></script>
<script type="text/javascript" src="jqplot_files/syntaxhighlighter/scripts/shBrushXml.min.js"></script>
<script class="include" type="text/javascript" src="jqplot_files/jquery.jqplot.min.js"></script>
<script class="include" language="javascript" type="text/javascript" src="jqplot_files/plugins/jqplot.pieRenderer.min.js"></script>
<script class="include" language="javascript" type="text/javascript" src="jqplot_files/plugins/jqplot.donutRenderer.min.js"></script>
<script type="text/javascript" src="jqplot_files/example.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
var data2 = [<? foreach ($categories as $name => $count){
    echo '['.$name.','.$count.'],';
}?>];
var plot2 = jQuery.jqplot ('chart2', [data2],
{
seriesDefaults: {
renderer: jQuery.jqplot.PieRenderer,
rendererOptions: {
// Turn off filling of slices.
fill: false,
showDataLabels: true,
// Add a margin to seperate the slices.
sliceMargin: 4,
// stroke the slices with a little thicker line.
lineWidth: 5
}
},
legend: { show:true, location: 'e' }
}
);
});
</script>
</head>
<body>
<div id="chart2" style="height:300px; width:500px;"></div>
<a href='Back.php'>Back</a>
</body>
</html>
<?php
}
?>

ajbest 0 Light Poster

11 Years Ago

Per the error that you got, you put an extra ) in the code I had supplied that caused the error.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

narai 0 Newbie Poster · Answer 1 · 2013-01-15T02:19:54+00:00

ok after i count each of the category, how can i put it as array to put as pie chart??

narai 0 Newbie Poster · Answer 2 · 2013-01-15T05:49:35+00:00

i got error for this line

 [<? foreach($categories as $name)=> $count]{

Parse error: syntax error, unexpected T_DOUBLE_ARROW in C:\xampp\htdocs\hello.php on line 104

narai 0 Newbie Poster · Answer 3 · 2013-01-15T06:04:02+00:00

Im still confused since i gt 4 category. I alr have the query for each category.So could u please help me with the while loop with each query and how to echo itInline Code Example Here

$result=mysql_query( "SELECT *  FROM ray, hello,category WHERE 
    ray.hello_id = hello.hello_id AND hello.category_id = category.category_id AND
  category.category_id= '1' ");

 $result=mysql_query( "SELECT * FROM ray, hello,category WHERE 
    ray.hello_id = hello.hello_id AND hello.category_id = category.category_id AND
  category.category_id= '2' ");

 $result=mysql_query( "SELECT * FROM ray, hello,category WHERE 
    ray.hello_id = hello.hello_id AND hello.category_id = category.category_id AND
  category.category_id= '3' ");

 $result=mysql_query( "SELECT * FROM ray, hello,category WHERE 
    ray.hello_id = hello.hello_id AND hello.category_id = category.category_id AND
  category.category_id= '4' ");


 while($row=mysql_fetch_array($result)){
$categories [$row['category_name']]+=1;


 }







?>


   <html>

<head>
    <title>hello</title>

    <link class="include" rel="stylesheet" type="text/css" href="jqplot_files/jquery.jqplot.min.css" />
    <link rel="stylesheet" type="text/css" href="jqplot_files/examples.min.css" />
    <link type="text/css" rel="stylesheet" href="jqplot_files/syntaxhighlighter/styles/shCoreDefault.min.css" />
    <link type="text/css" rel="stylesheet" href="jqplot_files/syntaxhighlighter/styles/shThemejqPlot.min.css" />


    <script class="include" type="text/javascript" src="script/jquery-1.6.2.min.js"></script>

    <script type="text/javascript" src="jqplot_files/syntaxhighlighter/scripts/shCore.min.js"></script>
    <script type="text/javascript" src="jqplot_files/syntaxhighlighter/scripts/shBrushJScript.min.js"></script>
    <script type="text/javascript" src="jqplot_files/syntaxhighlighter/scripts/shBrushXml.min.js"></script>

    <script class="include" type="text/javascript" src="jqplot_files/jquery.jqplot.min.js"></script>
    <script class="include" language="javascript" type="text/javascript" src="jqplot_files/plugins/jqplot.pieRenderer.min.js"></script>
    <script class="include" language="javascript" type="text/javascript" src="jqplot_files/plugins/jqplot.donutRenderer.min.js"></script>

    <script type="text/javascript" src="jqplot_files/example.min.js"></script>


    <script type="text/javascript">
        $(document).ready(function(){

            var data2 = [
            [<?php echo $row1[category_name], <?php echo $row1[]; ?>],
             [<?php echo $row2[category_name], <?php echo $row2[]; ?>],
              [<?php echo $row3[category_name], <?php echo $row3[]; ?>],
               [<?php echo $row4[category_name], <?php echo $row4[]; ?>]
               ];


          var plot2 = jQuery.jqplot ('chart2', [data2], 
            {
              seriesDefaults: {
                renderer: jQuery.jqplot.PieRenderer, 
                rendererOptions: {
                  // Turn off filling of slices.
                  fill: false,
                  showDataLabels: true, 
                  // Add a margin to seperate the slices.
                  sliceMargin: 4, 
                  // stroke the slices with a little thicker line.
                  lineWidth: 5
                }
              }, 
              legend: { show:true, location: 'e' }
            }
          );


        });

    </script>


</head>

<body>


    <div id="chart2" style="height:300px; width:500px;"></div>

    <a href='Back.php'>Back</a>

</body>
</html>

            <?php
 }
?>

narai91 0 Newbie Poster · Answer 4 · 2013-01-16T11:01:05+00:00

Ok, i have done the same codes as shown above but there is error:
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\hello.php line 34

In my code, line 34 refers to these code:

 while($row=mysql_fetch_array($result)){
$categories [$row['category_name']]+=1;


 }

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 5 · 2013-01-16T11:36:22+00:00

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\hello.php line 34

That means your query has failed. Use:

$result = mysql_query(...) or die(mysql_error());

to find out why.

narai91 0 Newbie Poster · Answer 6 · 2013-01-16T11:51:12+00:00

sry again..nw i have insert the die mysql error but nw the error shows that there is no database selected when actually i have already include my dbFunctions.php. I hope you could help me with this problem =(

<?php
include("dbFunctions.php");

$result=mysql_query( "SELECT * FROM ray, hello,category WHERE 
    ray.hello_id = hello.hello_id AND hello.category_id = category.category_id AND
  (category.category_id= '1' OR  category.category_id= '2' OR  category.category_id= '3' OR category.category_id= '4')")

or die(mysql_error())
;




 while($row=mysql_fetch_array($result)){
$categories [$row['category_name']]+=1;


 }
 ?>

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 7 · 2013-01-16T12:17:47+00:00

If it says you haven't, you haven't. Most likely the connect fails and thus the select database too. Add checks there too (with the or die construct).

narai91 0 Newbie Poster · Answer 8 · 2013-01-17T11:25:22+00:00

how to add check inside the die construct? im sry im nt really gd at this..could u please help me?

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 9 · 2013-01-17T13:19:31+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

Show dbFunctions.php