PHP and Charts.js struggling with rendering the data correctly

HI Guys

some assistance would be appreciated.
I am struggling in getting my chart to show properly it does render but i am not getting the bars on my barchart. i have tried several other options in selecting additional iformation from my SQL but still the rendering i get back is not right. I want to select all the types of defects listed (there are multiples of each) so i group them. i need the bar chart to show IE… electrical 50 calls logged, plumbing 25 calls logged etc.

any help is appreciated

//Database select query (types of defects logged, ie.. Plumbing,electrical etc
<?php
header('Content-Type: application/json');
include ("dbconnection.php");
$sql = sprintf("SELECT type FROM defects  group by type");
$result = mysqli_query($link6,$sql);
$data = array();
foreach ($result as $row){
    $data[] =$row;
   
}
 
print json_encode($data);
?>
**//making the chart**
<script>
    $(document).ready(function(){
  $.ajax({
    url: "http://localhost/loginandout/admindatacharts.php",
    method: "GET",
    success: function(data) {
      console.log(data);
     
      var type = [];
      for(var i in data) {
       
        type.push(data[i].type);
        
      }

      var chartdata = {
          
        labels: type,
          
        datasets : [
          {
            label: 'Defects Logged',
            backgroundColor: 'rgba(200, 200, 200, 0.75)',
            borderColor: 'rgba(200, 200, 200, 0.75)',
            hoverBackgroundColor: 'rgba(200, 200, 200, 1)',
            hoverBorderColor: 'rgba(200, 200, 200, 1)',
            data: type
          }
        ]
      };

      var ctx = $("#mycanvas");

      var barGraph = new Chart(ctx, {
        type: 'bar',
        data: chartdata
      });
    },
    error: function(data) {
      console.log(data);
    }
  });
});
    </script>

Is this all the same file? Then your AJAX call also receives the script-part after the JSON. Just have a look in your webdeveloper console (e.g. key F12).

If that’s two seperate files, this does not seem like a PHP problem, so you should provide a working HTML/JS example, that includes the data and the full sourcecode with at least the library you use.

you’re not catching this inside of a javascript variable. PHP is spitting the data out into the file, but that just puts something into your code that looks like:

<script>'
{[{ "x": 120, "y": 1, "label": "ta" ..... },{....},{....}]}
</script>

but what is javascript meant to do with that?

Don’t you need a different query, then? You can play with this kind of thing in phpmyadmin before committing to the PHP code. I think you’d need something like

SELECT type, count(type) as typecount from defects group by type

Once you have that working, you can add a JOIN to get the defect type name from whatever table you store it in.

@chorn, @m_hutley, I suspect the PHP code in in the separate file and the OP just hasn’t mastered the forum formatting at the moment. The PHP outputs the json-encoded array to return it to the JS.

oh okay. it’s 4 am for me, brain not fully engaged clearly :stuck_out_tongue:

What is type? My brain (though admittedly addled) tells me it can’t be both of these things… did you mean data: data?

type is the table name in my database ie… type = Plumbing

Right, and so if you look at your code…

        datasets : [
          {
            label: 'Defects Logged',
            backgroundColor: 'rgba(200, 200, 200, 0.75)',
            borderColor: 'rgba(200, 200, 200, 0.75)',
            hoverBackgroundColor: 'rgba(200, 200, 200, 1)',
            hoverBorderColor: 'rgba(200, 200, 200, 1)',
            data: type
          }
        ]

… how does giving your chart the data = [“Plumbing”,“Electrical”,“Other Stuff”] tell it what to plot? It needs the numbers, not the words.

My query is giving me this output which the data is correct
[{“type”:“Building”,“dtype”:“Floor Damaged”,“count()":“13”},{“type”:“Carpentry”,“dtype”:“Broken Cupboard”,"count()”:“13”},{“type”:“Electrical”,“dtype”:“No Lights”,“count()":“30”},{“type”:“Mechanical”,“dtype”:“No Air Flow”,"count()”:“7”},{“type”:“Plumbing”,“dtype”:“Blocked Toilet”,“count()":“33”},{“type”:“Temperatures”,“dtype”:“Temperature To Hot”,"count()”:“16”}]

but cant get the chart to draw the bar lines ie… building has 13 items, electrical has 30 etc…

yes it is seperate files, one php for the query and the one php with my html and JS

:slight_smile: was also thinking that, but how will i translate the “words” into numbers , i was hoping the count(*) in my SQL query could be used

Do the same thing you did for the words (type), except for numbers. Build a new variable. (And if you’re clever, use the same for loop you’ve already got to fill it up)

It will be slightly more complex because your query is returning a value called count() [you should really rename that return field], as @droopsnoot pointed out

Thanks for all your help and pointing me towards the solution

working Great

thanks again

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.