Google chart not showing population of Girls via PHP

Am trying to build a Google ColumnChart that will show population of boys and girls in each locality.

The column chart for boys will be in red and that of girls in blue. I created MySQL database and inserted contents as follows:

CREATE TABLE `population` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `locality` varchar(20) NOT NULL,
  `boys` int(11) NOT NULL,
  `girls` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


INSERT INTO `population` VALUES ('1', 'locality1', '10','30');
INSERT INTO `population` VALUES ('2', 'locality2', '24','26');
INSERT INTO `population` VALUES ('3', 'locality3', '29','17');

Here is my issue: when I run the code below. It only display the column chart for boys alone.

If I try to add database rows to display that of girls as well as per this line of code

echo "['".$row['locality']."',".$row['boys'].", ".$row['girls']."],";

it will throw error below

Uncaught (in promise) Error: Row 1 has 3 columns, but must have 2

Below is a working code that display columnchart for boys. How do I show Column chart of Girls also?

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>


<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
  var data = google.visualization.arrayToDataTable([
                  ['locality', 'No. of Boys'],
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', 'graph');
$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

$query = mysqli_query($db, "SELECT * FROM population");
while($row = mysqli_fetch_array($query)){
echo "['".$row['locality']."',".$row['boys']."],";

//echo "['".$row['locality']."',".$row['boys'].", ".$row['girls']."],";


}

?>
]);

// Optional; add a title and set the width and height of the chart
  var options = {'title':'No. of Boys and Girls in a Locality', 'width':800, 'height':400,
 series: {
            0: { color: 'red' },
            1: { color: 'blue' },
          
          }

};

var chart = new google.visualization.ColumnChart(document.getElementById('ColumnChart'));
chart.draw(data, options);
}

</script>

    



    
<div id="ColumnChart"></div>

Answer

The first item in the array must define the columns:

var data = google.visualization.arrayToDataTable([
  ['locality', 'No. of Boys', 'No. of Girls'],
  ...

Then the API should be able to handle the output of this:

echo "['".$row['locality']."',".$row['boys'].", ".$row['girls']."],";

See https://developers.google.com/chart/interactive/docs/gallery/columnchart …

Such a chart can also be constructed differently: https://jsfiddle.net/api/post/library/pure

Advertisement

Answer