My chart.htm - partial
This file creates a google graph from a database (energy consumption for a car wash)
<script>
$(document).ready(function() {
$('#Zobrazeni').on('change', function() {
var $form = $(this).closest('form');
$form.find('input[type=submit]').click();
});
});
</script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<?php
$actual_link = "https://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
//echo "<BR>" . $actual_link . "<BR>";
$idmycky = substr($actual_link, strrpos($actual_link, '/' )+1)."\n";
if ($idmycky==1){$hypermarket='Liberec';}
elseif ($idmycky==2){$hypermarket='Zličín';}
elseif ($idmycky==3){$hypermarket='Ostrava';}
elseif ($idmycky==4){$hypermarket='Karlovy Vary';}
elseif ($idmycky==5){$hypermarket='Plzeň';}
elseif ($idmycky==6){$hypermarket='Čakovice';}
elseif ($idmycky==7){$hypermarket='Havířov';}
elseif ($idmycky==8){$hypermarket='Brno';}
elseif ($idmycky==9){$hypermarket='Opava';}
else {$hypermarket='Sklad';}
$servername = "upin.profiwh.com";
$username = "db30764_***";
$password = "****************";
$dbname = "db30764_***";
$conn = new mysqli($servername, $username,$password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//printf("Initial character set: %s\n", mysqli_character_set_name($conn));
/* change character set to utf8mb4 */
mysqli_set_charset($conn, "utf8mb4");
//printf("Current character set: %s\n", mysqli_character_set_name($conn));
// define the list of fields
//$fields = array('Hypermarket', 'address', 'city', 'state', 'zip');
//$conditions = array();
// loop through the defined fields
//foreach($fields as $field){
// if the field is set and not empty
// if(isset($_POST[$field]) && $_POST[$field] != '') {
// create a new condition while escaping the value inputed by the user (SQL Injection)
// $conditions[] = "`$field` COLLATE utf8_czech_ci LIKE '%" . mysqli_real_escape_string($conn,$_POST[$field]) . "%'";
// }
//}
// builds the query
$query = "SELECT * FROM zakladni WHERE Hypermarket = '" . $hypermarket . "'";
// if there are conditions defined
//if(count($conditions) > 0) {
// append the conditions
// $query .= "WHERE " . implode (' AND ', $conditions); // you can change to 'OR', but I suggest to apply the filters cumulative
//}
//echo $query;
$result = mysqli_query($conn,$query);
// Close statement
//mysqli_stmt_close($conn);
// close connection
//print_r($result);
?>
<DIV class="col-12" style="text-align:center">
<form id="mycka" name="mycka" method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
<select name="Zobrazeni" id="Zobrazeni">
<option value="dnes" <?php if (empty($_POST['Zobrazeni'])||$_POST['Zobrazeni']=='dnes') {echo " selected";}?>>DNES</option>
<option value="vcera"<?php if (!empty($_POST['Zobrazeni']) && $_POST['Zobrazeni']=='vcera') {echo " selected";}?>>VČERA</option>
<option value="dvadny"<?php if (!empty($_POST['Zobrazeni']) && $_POST['Zobrazeni']=='dvadny') {echo " selected";}?>>POSLEDNÍ DVA DNY</option>
<option value="tyden"<?php if (!empty($_POST['Zobrazeni']) && $_POST['Zobrazeni']=='tyden') {echo " selected";}?>>POSLEDNÍ TÝDEN</option>
<option value="mesic"<?php if (!empty($_POST['Zobrazeni']) && $_POST['Zobrazeni']=='mesic') {echo " selected";}?>>POSLEDNÍ MĚSÍC</option>
<option value="vse"<?php if (!empty($_POST['Zobrazeni']) && $_POST['Zobrazeni']=='vse') {echo " selected";}?>>VŠE</option>
</select>
<input type="submit" name="submit" value="Nastavit" style="display:none"/>
</form>
</div>
<?php
$range= " AND date = '" . date('Y-m-d') . "'";
$kdy = date('j. n. Y');
if (empty($_POST['Zobrazeni'])) {$range= " AND date = '" . date('Y-m-d') . "'"; $kdy = date('j. n. Y');}
elseif (!empty($_POST['Zobrazeni']) && $_POST['Zobrazeni']=='dnes') {$range= " AND date = '" . date('Y-m-d') . "'"; $kdy = date('j. n. Y');}
elseif (!empty($_POST['Zobrazeni']) && $_POST['Zobrazeni']=='vcera') {$range= " AND date = '" . date('Y-m-d', strtotime('-1 day')) . "'"; $kdy = date('j. n. Y', strtotime('-1 day'));}
elseif (!empty($_POST['Zobrazeni']) && $_POST['Zobrazeni']=='dvadny') {$range= " AND date BETWEEN '" . date('Y-m-d', strtotime('-1 day')) . "' AND '" . date('Y-m-d') . "'"; $kdy = date('j. n. Y', strtotime('-1 day')) . " - " . date('j. n. Y');}
elseif (!empty($_POST['Zobrazeni']) && $_POST['Zobrazeni']=='tyden') {$range= " AND date BETWEEN '" . date('Y-m-d', strtotime('-1 week')) . "' AND '" . date('Y-m-d') . "'"; $kdy = date('j. n. Y', strtotime('-1 week')) . " - " . date('j. n. Y');}
elseif (!empty($_POST['Zobrazeni']) && $_POST['Zobrazeni']=='mesic') {$range= " AND date BETWEEN '" . date('Y-m-d', strtotime('-1 month')) . "' AND '" . date('Y-m-d') . "'"; $kdy = date('j. n. Y', strtotime('-1 month')) . " - " . date('j. n. Y');}
else {$range = ""; $kdy = "od počátku do " . date('j. n. Y');}
//if(isset($_POST['submit'])) {
$i = 1;
while($row = mysqli_fetch_array($result)) {
// if ($i>1) {exit;}
//echo $row['EKOD'] . " / " . $row['Energie'] . " / " . $row['Nazev'] . " / " . $row['Poznamka'] . " / ". $row['Jednotka'] . "<br />";
$nazev_grafu = $row['Nazev'] . " - " . $row['EKOD'] . " STAV: " . $row['Poznamka'] . " (" . $kdy . ")";
if ($row['Poznamka']=="Neměříme"){
$nemerime = "<H3 class='m-5' style='text-align: center;'>" . $row['Energie'] . " - NEMĚŘÍME</H3>";
}
else {
$nemerime = "";
}
if ($row['Energie']=="E") {
$barva = "#e2431e";
}
elseif ($row['Energie']=="V") {
$barva = "#3a88fe";
}
else {
$barva = "#96d35f";
}
// SQL dotaz pro výběr dat (přizpůsobte podle své tabulky)
$sql2 = "SELECT * FROM data WHERE EKOD = '" . $row['EKOD'] . "'" . $range;
$result2 = mysqli_query($conn, $sql2);
//print_r($result2);
// Formátování dat pro Google Charts
$rows = array();
$table = array();
$table['cols'] = array(
array('label' => 'Datum', 'type' => 'datetime'),
array('label' => $row['Energie'] . " / " .$row['Jednotka'], 'type' => 'number')
);
while($row = mysqli_fetch_assoc($result2)) {
$datum = $row['date']; // Původní datum
$novy_datum = date('Y-m-d', strtotime('-1 month', strtotime($datum)));
// echo $novy_datum;
$sub_array = array();
$sub_array[] = array("v" => "Date(" . str_replace('-',',',$novy_datum) . "," . str_replace(':',',',$row['time']) . ")");
if ($row['value']==""){
$hod = "-0.001";
}
else {
$hod = $row['value'];
}
$sub_array[] = array("v" => $hod);
$rows[] = array("c" => $sub_array);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
//print_r($jsonTable);
?>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart'], 'language' : 'cs'});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = new google.visualization.DataTable(<?php echo $jsonTable; ?>);
var options
= {
title: '<?php echo $nazev_grafu;?>',
colors: ['<?php echo $barva;?>'],
hAxis: {title: 'Datum', titleTextStyle: {color: '#333'}},
vAxis: {minValue: 0}
};
var chart = new google.visualization.ColumnChart(document.getElementById('chart_div<?php echo $i;?>'));
chart.draw(data, options);
}
</script>
<?php
if (!empty($nemerime)){
echo $nemerime;
}
else { ?>
<div id="chart_div<?php echo $i;?>" style="width: 100%; height: 500px;"></div>
<?php }
echo "<HR>";
$i++;
}
// }
mysqli_close($conn);
?>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
