How to show multidimensional data in my CanvasJS chart?

Question

<?php
$db->setQuery("SELECT MartabehName, COUNT(MartabehCode) FROM `#__dimteacher` GROUP BY MartabehName");
$countMartabehName = $db->loadObjectList(); 

foreach($countMartabehName as $row) {
    foreach($row as $key => $data) {
        echo $data."<br />";
    }
}
foreach($countMartabehName as $row) {

$dataPoints = array(
    array("label"=> $row->MartabehName , "y"=> $row->$key)
);
      }

?>
<script>
    my chart code from https://canvasjs.com/
    .
    .
    ,
    .
    .
</script>

<script src="https://canvasjs.com/assets/script/canvasjs.min.js">
</script>

How can I show all array (datax1,.... datax4) in chart?

It is only displaying the last array member in chart (datax4).

Answer 1

Your original coding attempt is repeatedly overwriting the $dataPoints variable inside of the loop -- this is why you only see the last iteration's data.

To create the desired multidimensional array directly from the query's result set, here is the way to use column aliases and Joomla's query building syntax.

$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select("MartabehName AS label, COUNT(MartabehName) AS y")
    ->from("#__dimteacher")
    ->group("MartabehName");
// echo $query->dump();
$db->setQuery($query);
$dataPoints = $db->loadAssocList();

Now your $dataPoints variable is instantly ready to deliver to CanvasJS without any manual looping/preparation.

See the result set building method loadAssocList() in the Joomla docs.

You can even ORDER BY if you like: https://joomla.stackexchange.com/a/23704/12352

As for how you pass your result set to CanvasJS, I don't use CanvasJS, but I'd be very likely to use json instead of feeding it an html table.

let chart = new CanvasJS.Chart("chartContainer", {
    data: [
      {
        dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
      }
    ]
});

chart.render();

Answer 2

solved by changing

foreach($countMartabehName as $row) {
    $dataPoints = array(
        array("label"=> $row->MartabehName , "y"=> $row->$key)
    );
}

to

$dataPoints = array();
foreach($countMartabehName as $row){
    array_push($dataPoints, array("label"=> $row->MartabehName , "y"=> $row->$key));
}

it is not need when we use @mickmackusa cods in first answer