php - 使用MySQL查询更新HTML图形

Question

这是之前的图片：asdfasdfasdfasdf

Answer 1

如果您使用Chart.js，我强烈建议您使用Ajax更新Chart.js。
您已经使用Ajax获取数据了，所以我认为它并不那么困难。

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

For Example。

具有TODO列表的过程映像。


对于TODO2。


最后是PHP示例
此代码返回这些信息。
您应该能够轻松调试。
1. SQL
2. SQL参数
3.结果（数据）

<?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.

就你而言

// 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.

像这样

$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);