This is the data that is in the database but I've only copy them so this is currently a static one and I can't convert this one into a real-time dynamic json chart.
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script>
let temp = 25; // testdata
const gaugeArr = [
['Label', 'Value'],
['Temperature', temp],
['pH', 7.2],
['DO', 60],
['Turbidity ', 10]
]; // testdata
const lineObj = {"cols":[{"label":"Date Time","type":"datetime"},{"label":"Water Level (ft)","type":"number"}],"rows":[{"c":[{"v":"Date(1628486400000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1628485800000)"},{"v":"33.00"}]},{"c":[{"v":"Date(1628485200000)"},{"v":"25.00"}]},{"c":[{"v":"Date(1628484600000)"},{"v":"33.00"}]},{"c":[{"v":"Date(1628484000000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1628483400000)"},{"v":"33.00"}]},{"c":[{"v":"Date(1628388000000)"},{"v":"29.00"}]},{"c":[{"v":"Date(1628302200000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1628216400000)"},{"v":"36.00"}]},{"c":[{"v":"Date(1628130600000)"},{"v":"31.00"}]},{"c":[{"v":"Date(1628044800000)"},{"v":"30.00"}]},{"c":[{"v":"Date(1627959000000)"},{"v":"27.00"}]},{"c":[{"v":"Date(1627873200000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1627787400000)"},{"v":"25.00"}]},{"c":[{"v":"Date(1627701600000)"},{"v":"27.00"}]},{"c":[{"v":"Date(1627615800000)"},{"v":"27.00"}]},{"c":[{"v":"Date(1627530000000)"},{"v":"36.00"}]},{"c":[{"v":"Date(1627444200000)"},{"v":"30.00"}]},{"c":[{"v":"Date(1627358400000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1627272600000)"},{"v":"36.00"}]},{"c":[{"v":"Date(1627186800000)"},{"v":"32.00"}]},{"c":[{"v":"Date(1627104600000)"},{"v":"38.00"}]},{"c":[{"v":"Date(1627018800000)"},{"v":"33.00"}]},{"c":[{"v":"Date(1626933000000)"},{"v":"37.00"}]},{"c":[{"v":"Date(1626847200000)"},{"v":"35.00"}]},{"c":[{"v":"Date(1626761400000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1626675600000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1626589800000)"},{"v":"29.00"}]},{"c":[{"v":"Date(1626504000000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1626418200000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1626332400000)"},{"v":"37.00"}]},{"c":[{"v":"Date(1626246600000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1626160800000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1626075000000)"},{"v":"32.00"}]},{"c":[{"v":"Date(1625989200000)"},{"v":"34.00"}]},{"c":[{"v":"Date(1625903400000)"},{"v":"31.00"}]},{"c":[{"v":"Date(1625817600000)"},{"v":"34.00"}]},{"c":[{"v":"Date(1625731800000)"},{"v":"37.00"}]},{"c":[{"v":"Date(1625646000000)"},{"v":"31.00"}]},{"c":[{"v":"Date(1625560200000)"},{"v":"36.00"}]},{"c":[{"v":"Date(1625474400000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1625388600000)"},{"v":"27.00"}]},{"c":[{"v":"Date(1625302800000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1625217000000)"},{"v":"38.00"}]},{"c":[{"v":"Date(1625131200000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1625045400000)"},{"v":"33.00"}]},{"c":[{"v":"Date(1624959600000)"},{"v":"31.00"}]},{"c":[{"v":"Date(1624873800000)"},{"v":"38.00"}]},{"c":[{"v":"Date(1624788000000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1624702200000)"},{"v":"32.00"}]},{"c":[{"v":"Date(1624616400000)"},{"v":"30.00"}]},{"c":[{"v":"Date(1624530600000)"},{"v":"27.00"}]},{"c":[{"v":"Date(1624444800000)"},{"v":"29.00"}]},{"c":[{"v":"Date(1624359000000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1624273200000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1624187400000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1624101600000)"},{"v":"38.00"}]},{"c":[{"v":"Date(1624015800000)"},{"v":"25.00"}]},{"c":[{"v":"Date(1623930000000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1623844200000)"},{"v":"37.00"}]},{"c":[{"v":"Date(1623758400000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1623672600000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1623586800000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1623501000000)"},{"v":"25.00"}]},{"c":[{"v":"Date(1623415200000)"},{"v":"32.00"}]},{"c":[{"v":"Date(1623329400000)"},{"v":"34.00"}]},{"c":[{"v":"Date(1623243600000)"},{"v":"32.00"}]},{"c":[{"v":"Date(1623157800000)"},{"v":"25.00"}]},{"c":[{"v":"Date(1623072000000)"},{"v":"31.00"}]},{"c":[{"v":"Date(1622986200000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1622900400000)"},{"v":"37.00"}]},{"c":[{"v":"Date(1622814600000)"},{"v":"30.00"}]},{"c":[{"v":"Date(1622728800000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1622643000000)"},{"v":"38.00"}]},{"c":[{"v":"Date(1622557200000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1622471400000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1622385600000)"},{"v":"31.00"}]},{"c":[{"v":"Date(1622299800000)"},{"v":"34.00"}]},{"c":[{"v":"Date(1622214000000)"},{"v":"37.00"}]},{"c":[{"v":"Date(1622128200000)"},{"v":"33.00"}]},{"c":[{"v":"Date(1622042400000)"},{"v":"25.00"}]},{"c":[{"v":"Date(1621956600000)"},{"v":"27.00"}]},{"c":[{"v":"Date(1621870800000)"},{"v":"35.00"}]},{"c":[{"v":"Date(1621785000000)"},{"v":"30.00"}]},{"c":[{"v":"Date(1621612800000)"},{"v":"25.00"}]},{"c":[{"v":"Date(1621527000000)"},{"v":"35.00"}]},{"c":[{"v":"Date(1621441200000)"},{"v":"29.00"}]},{"c":[{"v":"Date(1621355400000)"},{"v":"38.00"}]},{"c":[{"v":"Date(1621269600000)"},{"v":"36.00"}]},{"c":[{"v":"Date(1621183800000)"},{"v":"32.00"}]},{"c":[{"v":"Date(1621098000000)"},{"v":"35.00"}]},{"c":[{"v":"Date(1621012200000)"},{"v":"35.00"}]},{"c":[{"v":"Date(1620926400000)"},{"v":"32.00"}]},{"c":[{"v":"Date(1620840600000)"},{"v":"35.00"}]},{"c":[{"v":"Date(1620754800000)"},{"v":"36.00"}]},{"c":[{"v":"Date(1620669000000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1620583200000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1620497400000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1620411600000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1620325800000)"},{"v":"35.00"}]},{"c":[{"v":"Date(1620240000000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1620154200000)"},{"v":"36.00"}]},{"c":[{"v":"Date(1620068400000)"},{"v":"25.00"}]},{"c":[{"v":"Date(1619982600000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1619896800000)"},{"v":"34.00"}]},{"c":[{"v":"Date(1619811000000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1619725200000)"},{"v":"27.00"}]},{"c":[{"v":"Date(1619639400000)"},{"v":"25.00"}]},{"c":[{"v":"Date(1619553600000)"},{"v":"37.00"}]},{"c":[{"v":"Date(1619467800000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1619382000000)"},{"v":"33.00"}]},{"c":[{"v":"Date(1619296200000)"},{"v":"38.00"}]},{"c":[{"v":"Date(1619210400000)"},{"v":"25.00"}]},{"c":[{"v":"Date(1619124600000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1619038800000)"},{"v":"27.00"}]},{"c":[{"v":"Date(1618953000000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1618867200000)"},{"v":"38.00"}]},{"c":[{"v":"Date(1618781400000)"},{"v":"32.00"}]},{"c":[{"v":"Date(1618695600000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1618609800000)"},{"v":"33.00"}]},{"c":[{"v":"Date(1618524000000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1618438200000)"},{"v":"34.00"}]},{"c":[{"v":"Date(1618352400000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1618266600000)"},{"v":"31.00"}]},{"c":[{"v":"Date(1618180800000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1618095000000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1618009200000)"},{"v":"29.00"}]},{"c":[{"v":"Date(1617923400000)"},{"v":"32.00"}]},{"c":[{"v":"Date(1617837600000)"},{"v":"27.00"}]},{"c":[{"v":"Date(1617751800000)"},{"v":"28.00"}]},{"c":[{"v":"Date(1617666000000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1617580200000)"},{"v":"29.00"}]},{"c":[{"v":"Date(1617494400000)"},{"v":"40.00"}]},{"c":[{"v":"Date(1617408600000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1617322800000)"},{"v":"32.00"}]},{"c":[{"v":"Date(1617237000000)"},{"v":"34.00"}]},{"c":[{"v":"Date(1617151200000)"},{"v":"29.00"}]},{"c":[{"v":"Date(1617065400000)"},{"v":"35.00"}]},{"c":[{"v":"Date(1616979600000)"},{"v":"34.00"}]},{"c":[{"v":"Date(1616893800000)"},{"v":"26.00"}]},{"c":[{"v":"Date(1616808000000)"},{"v":"33.00"}]},{"c":[{"v":"Date(1616722200000)"},{"v":"27.00"}]},{"c":[{"v":"Date(1616636400000)"},{"v":"39.00"}]},{"c":[{"v":"Date(1616550600000)"},{"v":"34.00"}]}]}
let lineChart, gauge;
const drawChart = () => {
if (!lineChart) lineChart = new google.visualization.LineChart(document.getElementById('linechart_div')); // only do this once
if (!gauge) gauge = new google.visualization.Gauge(document.getElementById('gauge_div')); // only do this once
var gaugeData = google.visualization.arrayToDataTable(gaugeArr);
var lineData = new google.visualization.DataTable(lineObj);
lineChart.draw(lineData, lineOptions);
gauge.draw(gaugeData, gaugeOptions);
}
const getData = () => {
drawChart();
gaugeArr[1][1] = ++temp
setTimeout(getData, 5000);
};
window.addEventListener("load", () => { // when page loads
google.charts.load('current', {
'packages': ['gauge','corechart']
});
google.charts.setOnLoadCallback(getData); // start
})
const lineOptions = {
title: "Sensors Data",
legend: {
position: "bottom"
},
chartArea: {
width: "80%",
height: "70%"
}
};
const gaugeOptions = {
width: 500,
height: 200,
minorTicks: 5,
};
</script>
</head>
<body>
<div id="linechart_div" style="width: 400px; height: 120px,; margin-top:30px"></div>
<div id="gauge_div" style="width: 400px; height: 120px,; margin-top:30px"></div>
</body>
This is the php syntax that I use to get the data from the database and a working one but only if they are the only json chart in that page but what I want to happen is to display them both in one page and also I don't know how can I integrate this in a two combined json table to make them dynamic.
<?php
// For line chart
$con = mysqli_connect('localhost', 'root', '', 'adminpanel');
$sql = 'SELECT temperature, UNIX_TIMESTAMP(CONCAT_WS(" ", sensor_date , sensor_time)) AS datetime
FROM tbl_waterquality ORDER BY sensor_date DESC, sensor_time DESC';
$result = mysqli_query($con, $sql);
$rows = array();
$table = array();
$table['cols'] = array(
array(
'label' => 'Date Time',
'type' => 'datetime'
),
array(
'label' => 'Water Level (ft)',
'type' => 'number'
),
);
while ($row = mysqli_fetch_array($result)) {
$sub_array = array();
$datetime = explode(".", $row["datetime"]);
$sub_array[] = array(
"v" => 'Date(' . $datetime[0] . '000)'
);
$sub_array[] = array(
"v" => $row["temperature"]
);
$rows[] = array(
"c" => $sub_array
);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;
?>
//For Gauge chart
<?php
$con = mysqli_connect('localhost', 'root', '', 'adminpanel');
$sql = 'SELECT * FROM tbl_waterquality ORDER BY id DESC';
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_array($result); // assuming ONE result
$temperature = $row["temperature"];
$pH = $row["pH"];
$DO = $row["DO"];
$turbidity = $row["Turbidity"];
echo <<<EOT
[
["Label", "Value"],
["Temperature", $temperature],
["pH", $pH ],
["DO", $DO ],
["Turbidity", $turbidity ]
]
EOT
?>