How to create a dynamic number of datasets based on DB values

Question

My goal is to render a graph in PHP using CanvasJS but with multiple datasets dynamically generated based on the values in a DB. Generating this graph with only 1 dataset was working just fine, making it "dynamic" seems to be a challenge.

For 1 dataset I used the code below:

        while ($row = $stm->fetch(PDO::FETCH_ASSOC)) {   
            $score = calculateScore($row['difficulty'],$row['length']);
             array_push($dataPoint, array("x"=> $i , "y"=> $score)); 
            $i++;
        }

Where $dataPoint is send to the JS code

<script type="text/javascript">

    $(function () {
        var chart = new CanvasJS.Chart("chartContainer", {
            data: [
            {
                type: "line",

                dataPoints: <?php echo json_encode($dataPoint, JSON_NUMERIC_CHECK); ?>
            }
            ]
        });
        chart.render();
    });
</script>

So far, so good, so, next step is to extend this to multiple datasets. In this case, for each user in the view "completedgames" we will create a new dataset $datapoint and each $datapoint will be added to the overal dataset $datapoints as shown below:

    while($rowUsers = $stmUsers->fetch(PDO::FETCH_ASSOC)){
        
        $name = $rowUsers['username'];           

        $stm = $pdo->prepare('SELECT * FROM public."completedgames" WHERE username = :username');
        $stm->bindParam(":username", $name, PDO::PARAM_INT);
        $stm->execute();   
        
        while ($row = $stm->fetch(PDO::FETCH_ASSOC)) {   
            $score = calculateScore($row['difficulty'],$row['length']);
             array_push($dataPoint, array("x"=> $i , "y"=> $score)); 
            $i++;
        }
        array_push($dataPoints, $dataPoint);  
        $dataPoint = array();
    }  

Where $dataPoints is the value now send to the same JS code as shown in above. Unfortunately this is where it goes wrong. data array_push() function doesn't feel right but I have no idea what the alternative is.

So, I hope this is sufficient information, all information is welcome and thanks in advance!

Answer 1

Using James' input I was able to make my Graph with multiple lines. Just needed to reset the counter and $dataPoint array: here is the working code:

    while($rowUsers = $stmUsers->fetch(PDO::FETCH_ASSOC)){
        
        $name = $rowUsers['username'];           

        $stm = $pdo->prepare('SELECT * FROM public."completedgames" WHERE username = :username');
        $stm->bindParam(":username", $name, PDO::PARAM_INT);
        $stm->execute();   
        
        while ($row = $stm->fetch(PDO::FETCH_ASSOC)) {   
            $score = calculateScore($row['difficulty'],$row['length']);
             array_push($dataPoint, array("x"=> $i , "y"=> $score)); 
             json_encode($dataPoint, JSON_NUMERIC_CHECK);
            $i++;
        }
        //array_push($dataPoints, $dataPoint);  
       // $dataPoint = array();
        
        array_push($charts, array("type" => "line", "dataPoints" => $dataPoint));
        $dataPoint = array();
        $i = 0;
    }  

Thanks for all your inputs!