How to loop inside javascript using php variable

Question

I have found this great resource at Chart.js - Getting data from database using mysql and php but I'm a little bit needing help to retrieve those records from database. This is what I have done already following the guide above.

save the php in separate file api.php and call the the api.php using JSON. But i needed to make the

labels : ["Jan","Feb","March","April","May"]

and

data : [randomScalingFactor(),randomScalingFactor(),randomScalingFactor()]

section dynamically retrieve data from the sql database. for the label i needed to set values that is entered date to the database and for the data i needed to set value that is entered to the database respectively. I would appreciate if any one can guide me with this. Thanks!

api.php

<?php

$id    = $_REQUEST['ID'];
$item1 = $_REQUEST['item'];

$sql = "SELECT * FROM item WHERE item_sup_company = '$id' AND subcategory = '$item1' ORDER BY item_id DESC";

$ser      = new DBConnection();
$serchRes = $ser->executeQuery($sql);
$result   = mysql_fetch_object($serchRes);



$arrLabels   = $result->date;
$arrdata     = $result->item_price;
$arrDatasets = array(
    'label' => "My First dataset",
    'fillColor' => "rgba(220,220,220,0.2)",
    'strokeColor' => "rgba(220,220,220,1)",
    'pointColor' => "rgba(220,220,220,1)",
    'pointStrokeColor' => "#fff",
    'pointHighlightFill' => "#fff",
    'pointHighlightStroke' => "rgba(220,220,220,1)",
    'data' => $arrdata
);

$arrReturn = array(
    array(
        'labels' => $arrLabels,
        'datasets' => $arrDatasets
    )
);

print(json_encode($arrReturn));
?>

index.html

<script type="text/javascript">
    $.ajax({
        type: 'POST',
        url: '../include/chart-api.php',
        success: function(data) {
            lineChartData = data;
            var myLine = new Chart(document.getElementById("canvas").getContext("2d")).Line(lineChartData);

            var ctx = document.getElementById("canvas").getContext("2d");
            window.myLine = new Chart(ctx).Line(lineChartData, {
                responsive: true
            });
        }
    });

    var randomScalingFactor = function() {
        return Math.round(Math.random() * 1000)
    };

    window.onload = function() {
        var chart1 = document.getElementById("line-chart").getContext("2d");
        window.myLine = new Chart(chart1).Line(lineChartData, {
            responsive: true
        });

    };                                          
</script>
<div class="canvas-wrapper">
  <canvas class="main-chart" id="line-chart" height="200" width="600"></canvas>
</div>

Now i have reached this step and i needed some body to help me this. Already retrieve the records set values in the graph. However, the graph showing me only one record for all the values.

Here is how i done it.

<?php


$sqlchart = "SELECT * FROM item WHERE item_sup_company = '$id' AND subcategory = '$item1' ORDER BY item_id DESC";


$chartres = new DBConnection();
$chartr   = $chartres->executeQuery($sqlchart);

?><?php

while ($chartrows = mysql_fetch_object($chartr)) {

    $monthdate = strtotime($chartrows->date);
    $todate    = date("M", $monthdate);

    $arrLabels1 = $todate;

    $arrdata1 = $chartrows->item_price;
    echo $arrLabels1 . ' ' . $arrdata1 . "<br/>";

    //$arrDatasets = array('label' => "My First dataset",'fillColor' => "rgba(220,220,220,0.2)", 'strokeColor' => "rgba(220,220,220,1)", 'pointColor' => "rgba(220,220,220,1)", 'pointStrokeColor' => "#fff", 'pointHighlightFill' => "#fff", 'pointHighlightStroke' => "rgba(220,220,220,1)", 'data' => $arrdata);

    //$arrReturn = array(array('labels' => $arrLabels, 'datasets' => $arrDatasets));

    //print (json_encode($arrReturn));

?>

<script type="text/javascript">
    var randomScalingFactor = function() {
        return Math.round(Math.random() * 10)
    };

    var lineChartData = {

        labels : ["<?php echo $arrLabels1; ?>","<?php echo $arrLabels1; ?>","<?php echo $arrLabels1; ?>","<?php echo $arrLabels1; ?>","<?php echo $arrLabels1; ?>","<?php echo $arrLabels1; ?>"],
        datasets: [

            {
                label: "My Second dataset",
                fillColor: "rgba(48, 164, 255, 0.2)",
                strokeColor: "rgba(48, 164, 255, 1)",
                pointColor: "rgba(48, 164, 255, 1)",
                pointStrokeColor: "#fff",
                pointHighlightFill: "#fff",
                pointHighlightStroke: "rgba(48, 164, 255, 1)",
                data : [<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>]
            }

        ]

    }

    window.onload = function() {
        var chart1 = document.getElementById("line-chart").getContext("2d");
        window.myLine = new Chart(chart1).Line(lineChartData, {
            responsive: true
        });

    };  
</script>
<?php  
}
?>

score 0 · Accepted Answer · edited May 23 '17 at 10:27

The reason is because you're repeating the same variable within your data and labels:

data : [<?php echo $arrdata1; ?>,<?php echo $arrdata1; ?>, ...
labels : ["<?php echo $arrLabels1; ?>","<?php echo $arrLabels1; ?>", ...

Try getting all the data from the database into a PHP array first, then output that array into JSON, (where JavaScript will be able to understand it properly).

For example:

$arrdata = array();
$arrLabels = array();

while ($chartrows = mysql_fetch_object($chartr)) {

    $monthdate = strtotime($chartrows->date);
    $todate = date("M", $monthdate);

    $arrLabels[] = $todate;
    $arrdata[] = $chartrows->item_price;

}

Now move your output outside of the while loop, you're only generating one chart.

So in your JavaScript (as noted in the previous sentence, this should be outside of the while loop).

var lineChartData = {

    label : <?php json_encode($arrLabels); ?>,

    datasets: [

        {
            label: "My Second dataset",
            fillColor: "rgba(48, 164, 255, 0.2)",
            strokeColor: "rgba(48, 164, 255, 1)",
            pointColor: "rgba(48, 164, 255, 1)",
            pointStrokeColor: "#fff",
            pointHighlightFill: "#fff",
            pointHighlightStroke: "rgba(48, 164, 255, 1)",

            data : <?php json_encode($arrdata); ?>
        }

    ]

};

Also I noticed that you're not sanitizing your query:

$id    = $_REQUEST['ID'];
$item1 = $_REQUEST['item'];

$sql = "SELECT * FROM item WHERE item_sup_company = '$id' AND subcategory = '$item1' ORDER BY item_id DESC";

You should be preparing/executing or at the very least escaping the data within the query. Please take a look at this.

How to loop inside javascript using php variable

1 Answers1