Error In Javascript code with php

Question

I wanna make statistics in my website for the last 3 years

I want to show result like this

2016 : 159
2015 : 132
2014 : 200

I try my code (this's)

$date2 = date('Y');
$n = $date2;
for($i=$n-2;$i<=$n;$i++) {
$sql = "SELECT sum(count) AS value_sum FROM statistics where YEAR(st_date) = $i ";
$sql_sel = mysqli_query($conn,$sql);
echo '
 <script>
 var pieData = [
 ';
while($rows = mysqli_fetch_assoc($sql_sel)) {
if($i == $n-2) {
echo '{
 value: '.$rows['value_sum'].',
 color:"#337AB7"
 },';
}
else if($i == $n-1) {
echo '{
 value: '.$rows['value_sum'].',
 color:"#FC8213"
 },';
 }
else if($i == $n) {
 echo '{
 value: '.$rows['value_sum'].',
 color:"#8BC34A"
 },';
}
echo'];
new Chart(document.getElementById("pie").getContext("2d")).Pie(pieData);
</script>';
}}
?>

but this code give me just 1 one row like this

2016 : 159

I wanna see all result, any help ?

• gen_Eric: PROTIP: You can build an array in PHP then use json_encode() to create a JSON string, which can actually be echoed into the JavaScript and parsed as an array/object.
• sayousaad: @RocketHazmat I'm beginner Can you help me ? and I need to change color for Each year

Answer 1

You're creating 3 charts within your for loop. If you allow php to encode the data for you, you can echo the script outside of the PHP like so:

$date2 = date('Y');
$n = $date2;
$data = array();
for($i=$n-2;$i<=$n;$i++) {
 $sql = "SELECT sum(count) AS value_sum FROM statistics where YEAR(st_date) = $i ";
 $sql_sel = mysqli_query($conn,$sql);
 while($rows = mysqli_fetch_assoc($sql_sel)) {
 if($i == $n-2) {
 $data[] = array('value'=> $rows['value_sum'], 'color'=>'#337AB7');
 }
 else if($i == $n-1) {
 $data[] = array('value'=> $rows['value_sum'], 'color'=>'#FC8213');
 }
 else if($i == $n) {
 $data[] = array('value'=> $rows['value_sum'], 'color'=>'#8BC34A');
 }
 }
}
?>
<script>
 var pieData = <?php json_encode($data) ?>;
 new Chart(document.getElementById("pie").getContext("2d")).Pie(pieData);
</script>

• aynber: Does it throw any errors? What doesn't work about it?
• aynber: If you look at the source code, does the script look correct?

Answer 2

This should help you out:

<?php
// Store the array for the pie data within PHP at first
$js_pie_data = [];
// Define colors
$colors = [
 date('Y') => '8BC34A', // current year
 (date('Y') - 1) => 'FC8213', // last year
 (date('Y') - 2) => '337AB7' // 2 years ago
];
$sql = "SELECT
 YEAR(st_date) as `year`, -- get year from DB, makes life simpler :)
 sum(count) AS value_sum
 FROM
 statistics
 WHERE
 YEAR(st_date) >= (YEAR(CURDATE())-2) -- get all records from 2 years ago to today
 GROUP BY
 YEAR(st_date) -- group by year so that our sum() above will work";
$sql_sel = mysqli_query($conn,$sql);
while($rows = mysqli_fetch_assoc($sql_sel))
{
 // keep adding entries to the pie data
 $js_pie_data[] = [
 'value' => $rows['value_sum'],
 'color' => '#'.$colors[$rows['year']] // based on year from DB, pick a color
 ];
}
echo '<script>
var pieData = '.json_encode($js_pie_data).'; // this will output a properly formatted JS array which will be understood by JS with no problem
new Chart(document.getElementById("pie").getContext("2d")).Pie(pieData);
</script>';

• sayousaad: That's perfect, can I make the same for month ? without any problem ?
• MonkeyZeus: @sayousaad I'm not sure what you mean but I'm sure it's possible.
• Jay Blanchard: Why should this "help out" the OP? A good answer will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. ¯\_(ツ)_/¯
• MonkeyZeus: @JayBlanchard Would you like me to comment more thoroughly?
• Jay Blanchard: Not so much comment but explain why you do what you do.

Answer 3

1)You can create array in php side.

2)Then convert it as json string.

3)Define javascript variable with that json string

4)Convert it as object with JSON.parse

5)Insert it in option

 <?php
 $db = Array("1","2","3");
 $out = Array();
 $x = 0;
 foreach($db as $vals){
 if($x == 1){
 $out[] = Array("value"=>$vals,"color"=>"#bfbfbf");
 }
 if($x == 2){
 $out[] = Array("value"=>$vals,"color"=>"#00ffff");
 }
 if($x == 3){
 $out[] = Array("value"=>$vals,"color"=>"#fff00");
 }
 if($x == 3){$x = 0;}
 $x++;
 }
 echo "
 <script language='javascript'> 
 var stat_str = '".json_encode($out)."';
 var stat_obj = JSON.parse(stat_str);
 // then you can insert stat_obj if you need object into stats
 </script>";
 ?>