Updating HTML Graph with MySQL Query

问题:

Here is the Before Picture:dfasdfasdfasdf

回答1:

If you use Chart.js, I highly recommend using Ajax to update the Chart.js.
You already get data using Ajax, so it shouldn't be that difficult, I think.

// --- get data using ajax ---
barChart.data = chartdata;
barChart.update();

For Example.

Process Image with TODO list.

For TODO2.

Finally PHP example
This code return these information.
You should be able to debug easily.
1. SQL
2. SQL Parameter
3. result (data)

<?php
header('Content-Type: application/json');
// database
define('DB_HOST', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', 'root');
define('DB_NAME', 'score');

// get connection
$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
if(!$mysqli){
    die("Connection failed: " . $mysqli->error);
}

// loop post parameters and create array for bind parameter.
// Remember, this code use the key as data column name.
// $params[0] = datatype, data[1...] = value(condition)
// $sqlWhere = "WHERE column1 = ? and column2 = ?" 
$params = [];
$sqlWhere = "";
$sqlWhereParam = [];
foreach ($_POST as $key => &$value) {
    if(empty($value)) { continue; };
    // $params[0] = data type. This time, 's:string' only.
    $params[0] .= "s";
    $params[] = &$value;
    $sqlWhereParam[] = $key . " = ?"; 
}
if (count($sqlWhereParam) > 0) {
    $sqlWhere = "where " . implode(" and ", $sqlWhereParam);
}

// initialize a return data.
$data = array();
$sql = "SELECT * FROM counties " . $sqlWhere;
// create a prepared statement
if($stmt = mysqli_prepare($mysqli, $sql)) {
    // bind parameter
    call_user_func_array(array($stmt, 'bind_param'), $params);
    // execute
    if($stmt->execute()) {
        // get result
        $result = $stmt->get_result();
        // fetch 1 row until it's over.
        while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
            $data[] = $row;
        }
        // free result.
        $result->free;
    }
    // close stmt.
    $stmt->close();
}
// close connection
$mysqli->close();

// Prepare response data to confirm.
$response = array(
    "sql"        => $sql,     // SQL
    "usingParam" => $params,  // SQL Parameter
    "data"       => $data     // result
);

// return as json format.
echo json_encode($response);
?>

JavaScript

// Ajax
$.post("http://localhost:8888/UCBerkeleyRAELProject/data.php", param).done(function(res) {
    // see, what happend.
    console.log(res);
    // --- add logic --- (please delete the old one in initialize process.)
    var sample = [];
    var electric =[];
    var mpg = [];
    var urban = [];
    var vmt = [];
    var airtravel = [];
    var renewable = [];
    var conservation = [];
    var heating = [];
    var water = [];
    var cefficiency = [];
    var shiftc = [];
    var healthyd = [];
    for(var i in res.data) {
        sample.push(res.data[i].sample);
        electric.push(parseInt(res.data[i].electric));
        mpg.push(parseInt(res.data[i].mpg));
        urban.push(parseInt(res.data[i].urban));
        vmt.push(parseInt(res.data[i].vmt));
        airtravel.push(parseInt(res.data[i].airtravel));
        renewable.push(parseInt(res.data[i].renewable));
        conservation.push(parseInt(res.data[i].conservation));
        heating.push(parseInt(res.data[i].heating));
        water.push(parseInt(res.data[i].water));
        cefficiency.push(parseInt(res.data[i].cefficiency));
        shiftc.push(parseInt(res.data[i].shiftc));
        healthyd.push(parseInt(res.data[i].healthyd));
    }
    --- omit rest of it.

In your case

// Ajax
$.post("http://localhost:8888/UCBerkeleyRAELProject/data.php", param).done(function(res) {
    // see, what happend.
    console.log(res);
    // --- add logic --- (please delete the old one in initialize process.)
    var sample = [];
    var electric =[];
    var mpg = [];
    var urban = [];
    var vmt = [];
    var airtravel = [];
    var renewable = [];
    var conservation = [];
    var heating = [];
    var water = [];
    var cefficiency = [];
    var shiftc = [];
    var healthyd = [];
    for(var i in res) {
        sample.push(res[i].sample);
        electric.push(parseInt(res[i].electric));
        mpg.push(parseInt(res[i].mpg));
        urban.push(parseInt(res[i].urban));
        vmt.push(parseInt(res[i].vmt));
        airtravel.push(parseInt(res[i].airtravel));
        renewable.push(parseInt(res[i].renewable));
        conservation.push(parseInt(res[i].conservation));
        heating.push(parseInt(res[i].heating));
        water.push(parseInt(res[i].water));
        cefficiency.push(parseInt(res[i].cefficiency));
        shiftc.push(parseInt(res[i].shiftc));
        healthyd.push(parseInt(res[i].healthyd));
    }
    // make sure, these variable is set value.
    console.log(electric);
    --- omit rest of it.

Something like that

$city = $_POST['city'];

$sqlWhere = "";
if (!empty($city)) {
    $sqlWhere = "WHERE sample = '". $city ."'";
}

//query to get data from the table
$query = sprintf("SELECT  sample, electric, mpg, urban, vmt, airtravel, 
renewable, conservation, heating, water, cefficiency, shiftc, healthyd FROM counties %s", $sqlWhere);