使用MySQL查询更新HTML图形 [英] Updating HTML Graph with MySQL Query

问题描述

这是之前的图片:dfasdfasdfasdf

Here is the Before Picture:dfasdfasdfasdf

推荐答案

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

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

例如.

带有TODO列表的过程映像.

Process Image with TODO list.

对于TODO2.

最后一个PHP示例
此代码返回这些信息.
您应该能够轻松进行调试.
1. SQL
2. SQL参数
3.结果(数据)

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

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

这篇关于使用MySQL查询更新HTML图形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！