How to generate dynamic data from Google Chart API using PHP and MySQL?

Question

I'm running into an issue and I need help. I'm able to show a horizontal column chart from the Google API with dynamic data from a MySQL table on page load. But when I click a button and the data is being posted to the page, the chart does not print on the HTML even tho I'm able to see in the response tab in the console that the script has been generated correctly. However, when I look in the HTML in the inspector, I can see that the JavaScript appears greyed-out on the page as if it was commented-out. I've been scratching my head for hours. Serverside I'm using PHP. Clientside I'm using Javascript/jQuery + HTML. How can I fix this? If you have a better way to generate a chart from PHP to HTML, I'm opened to suggestions.

graph.html

<div id="chart_div1"></div>

jquery

$(document).on('click', '.view_sales_year_btn', function () {

    //collect variables
    var sales_year = $("#sales_year").find(":selected").val();

    var valuesf = "user_id=" + encodeURIComponent(user_id)
        + "&sales_year=" + encodeURIComponent(sales_year);

    if (sales_year !== '' || sales_year !== null && valuesf !== '') {

        $.ajax({
            type: "POST",
            url: "graph.php",
            dataType: 'json',
            data: valuesf,
            success: function (data) {

                //DISPLAY SALES GRAPH'
                $('#chart_div1').html(data.sales_graph);

            }
        });
    }
});

graph.php

//collect variables         
$user_id = mysqli_real_escape_string($conn,$_POST['user_id']);

if(isset($_POST['sales_year'])){
    //post year    
    $sales_year=mysqli_real_escape_string($conn,$_POST['sales_year']);
}else{
    $sales_year = date('Y');
}

//set title for graph 
$graph_title ='My Sales '.$sales_year;

//cleaning status 
$c_status = 'Booked';

//select all completed cleanings from user_schedule 
$user_schedule = $conn->prepare('SELECT cleaner_id, 
                                MONTH(cleaning_date) AS sales_month,
                                YEAR(cleaning_date) AS sales_year,
                                SUM(cleaning_price) AS total_sales
                    FROM user_schedule 
                    WHERE YEAR(cleaning_date)=?
                    AND cleaner_id=?
                    AND cleaning_status=?
                    GROUP BY DATE_FORMAT(cleaning_date, "%m-%Y")');
$user_schedule->bind_param('sss',$sales_year,$user_id,$c_status);
$user_schedule->execute();
$user_result = $user_schedule->get_result();

//iteration
if($user_result->num_rows>0){
    //declare variable 
    $my_sales = '';
    $my_sales .='<script type="text/javascript">';
    $my_sales .=" google.load('visualization', '1', {packages:['corechart']});
        google.setOnLoadCallback(drawChart1);
        function drawChart1(){
        var data = google.visualization.arrayToDataTable([
            ['Month', 'Sales'],";
    while($sales = $user_result->fetch_assoc()){
        //get month number
        $monthNum =$sales['sales_month'];

        //convert monh number to month name 
        $dateObj   = DateTime::createFromFormat('!m', $monthNum);
        $month = $dateObj->format('F'); // March

        //get first 3 characters from month 
        $this_month = substr($month, 0, 3);

        //select all completed cleanings from user_schedule 
        $total_month_sales = (int)$sales['total_sales']*0.35;

        //echo month with total sales 
        $my_sales .="['".$this_month."',  ".$total_month_sales."],";
    }
    $my_sales .= "]);

        var options = {
            title: '".$graph_title."',
            hAxis: {title: 'Amounts may differ from Stripe', titleTextStyle: {color: 'blue'}}
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div1'));
        chart.draw(data, options);
        }

        $(window).resize(function(){
            drawChart1();
        });
    </script>";

}else{
    $my_sales = '<h4 class="text-center alert alert-warning">You Have No Sales For '.$sales_year.' Yet.</h4>';
}

//return answer to json
$result = array('message' => 'success','sales_graph'=>$my_sales);
header('Content-Type: application/json');
echo json_encode($result);

Answer 1

Just adding a bunch of Javascript code to your page will not actually execute that code. Essentially your AJAX call is returning some stuff, and your JS then inserts that into a div, and ... that's all. The browser won't attempt to run that stuff. Even if you update your JS to attempt to run the stuff you've just inserted, it won't work because the browser doesn't even know that new code exists - it wasn't there on page load, when the browser parses the page. You'd have to first eval() that new code, which introduces a new set of problems. This question is a good reference and describes some of them.

Stepping back though, there is a better approach.

Judging by your JS, your front end page has a year selector, so you can choose eg 2019 and click your "graph" button, and see a chart of your 2019 sales. So what you're really looking for from the AJAX is the graph data. If you change the year and click the button again, you just need a fresh set of data - you don't need to load the Google Charts JS again, and initialise a new graph.

Your front end page can include all the set up and initialisation of your Google chart, but without any data. Without data you can't draw it yet of course, so you have to trigger the actual drawing some other way, once you've loaded your data.

PHP

So, first of all, update your PHP, and remove all the Javascript. You want only an array of month/sales values back:

$my_sales = [];
while ($sales = $user_result->fetch_assoc()) {
    // ... your code to set these variables up
    $my_sales[$this_month] = $total_month_sales;
}

Remove the <script> tags, all the Google Charts related JS, drawChart1, etc - the only thing your AJAX should return is JSON data. Using your existing PHP approach:

$result = array('message' => 'success', 'sales_graph' => $my_sales);
header('Content-Type: application/json');
echo json_encode($result);

HTML

Next, add the Charts JS stuff you just removed from the PHP to your front end in graph.html:

google.load('visualization', '1', {packages: ['corechart']});

// Don't want to draw the chart on load any more, you will manually
// draw it once you have some data - so remove this line entirely
// google.setOnLoadCallback(drawChart1);

function drawChart1() {
    // ...
}

$(window).resize(function() {
   // ...
}

Now, for the function that actually draws the chart, you need to be able to give it a set of data to draw - ie it needs to accept a parameter:

// values will be the JSON data we got back from the AJAX call
function drawChart1(values) {

There are still some hoops to jump through to get the JSON data we get back from the AJAX in the right format:

// Convert the JSON values to an array, as Google's DataTable
// requires that.
array = [];
for (var i in values) {
    array.push([i, values[i]]);
}

var data = new google.visualization.DataTable();
data.addColumn('string', 'Month');
data.addColumn('number', 'Sales');
data.addRows(array);

So, now you have your Google Chart all set up and ready. To draw a chart, you need to get some data, and pass it to drawChart1() as a parameter. You can do that from your AJAX success handler:

success: function(response) {
    // response is the JSON data your PHP sends us.  Pass the 
    // 'sales_graph' element of it it to the function and have it
    // drawn!  You can also test that response.message is 'success'
    drawChart1(values.sales_graph);
}

There are a few moving parts here and I've jumped around a bit, but it is much clearer to see it all together, working, in action - here's a working JSFiddle. Note it uses JSFiddles echo to simulate your AJAX call - basically I am faking your AJAX request and supplying some dummy data to get back.