0

Is there a way to count the duplicate data from mysql and display it to a bar chart, Im trying to make a attendance report using morris bar chart.

here my sample code:

<html >
<head>
     <link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.css">
      <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
      <script src="//cdnjs.cloudflare.com/ajax/libs/raphael/2.1.0/raphael-min.js"></script>
      <script src="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.min.js"></script>

</head>

<body>

<div id="chart"></div>
</body>
</html>

here is my php code:

<?php 

    $connect = mysqli_connect("localhost", "root", "", "sa");
    $query = "SELECT year,  count(*) as course FROM test group by  year,course order by year ASC ";
    $result = mysqli_query($connect, $query);
    $chart_data = '';
    while($row = mysqli_fetch_array($result))
    {
     $chart_data .= "{ year:'".$row["year"]."', course:".$row["course"]."}, ";
    }
    $chart_data = substr($chart_data, 0, -2);
    ?>

and this is my javascript:

<script>
Morris.Bar({
 element : 'chart',
 data:[<?php echo $chart_data; ?>],
 xkey:'year',
 ykeys:['course','course','course','course','course'],
 labels:['BSIT','BSHRM','BSCS','BSTM','ABCOMM'],
 hideHover:'auto',
   xLabelAngle: '60',
  verticalGrid: true,
  resize:true,
   barColors: ['red','blue','green','yellow','black'],
      gridTextSize: 12



});
</script>

this is my database:

enter image description here

UPDATED: and this is my output so far: enter image description here

enter image description here as you can see in my output all courses have same value for example the two 2018-07-12 the output should be based on my database is for BSIT = 3 the rest is zero value same with the other 2018-07-12 the output should be BSHRM =1 and the rest is zero value, is there a way to achieve that?, Hope you can help me.

Bepooh
  • 25
  • 1
  • 10
  • It should be `GROUP BY year, course`. – Barmar Jul 12 '18 at 16:21
  • `SELECT year , course , count(*) as course` you're reusing the same name `course` for the course name and the count. – Barmar Jul 12 '18 at 16:22
  • i tried this code SELECT year , count(*) as course FROM test group by year, course but still don't work. – Bepooh Jul 12 '18 at 16:30
  • `SELECT year, course, count(*) as count` – Barmar Jul 12 '18 at 16:32
  • when i tried your query my bar chart disappear – Bepooh Jul 12 '18 at 16:40
  • 1
    Create a query to obtain duplicate items, then use the data however you please. https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql <- how to query for duplicate values. –  Jul 12 '18 at 17:37

1 Answers1

0

You have two problems with your query:

First, the alias COUNT(*) AS course reuses the column name as the alias. You need to give it a different name.

Second, you left course out of the grouping, so you're combining the counts of all courses in your results.

It should be:

$query = "SELECT year , course ,  count(*) as count FROM test group by  year, course order by year ASC ";

Each course will then be in a different row of the results, you'll need to regroup when you process the results.

You also shouldn't create JSON by concatenating strings. Put the results in an array and use json_encode().

$results = array();
while ($row = mysqli_fetch_assoc($result)) {
    $results[$row['year']]['year'] = $row['year'];
    $results[$row['year']][$row['course']] = $row['count'];
}
$chart_data = json_encode(array_values($results));

This method uses the course names as the keys in the JSON, not course1, course2, etc. So you need to change 

ykeys:['course','course','course','course','course'],

to:

ykeys:['BSIT','BSHRM','BSCS','BSTM','ABCOMM'],

The JSON in $chart_data already includes the square brackets around the array, so you don't need to add it around the echo. Use:

data: <?php echo $chart_data; ?>,
Barmar
  • 741,623
  • 53
  • 500
  • 612