I tried getting span tag element but when i using it to mysql query Im not getting the result that i want, But if i set manually the specific date in php $varibale i get the data the only problem is the ';
?>
and when i tried to use $sample in this
$query = " SELECT * FROM tblblotter WHERE reportDate LIKE '%".$sample."%' ";
it does not work
but if i try
$query = " SELECT * FROM tblblotter WHERE reportDate LIKE '%".'04/07/2022'."%' ";
it will show the result
All I wanted to do is to get specific date chartjs using bar and display the data in table when the user click the specific bar
<?php
$username="root";
$password= "";
$database="micra";
try {
$pdo = new PDO("mysql:host=localhost;database=$database,",$username,$password);
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {
die("ERROR:Could not connect".$e->getMessage());
}
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
* {
margin: 0;
padding: 0;
font-family: sans-serif;
}
.chartMenu {
width: 100vw;
height: 40px;
background: #1A1A1A;
color: rgba(255, 26, 104, 1);
}
.chartMenu p {
padding: 10px;
font-size: 20px;
}
.chartCard {
width: 100vw;
height: calc(100vh - 40px);
background: rgba(255, 26, 104, 0.2);
display: flex;
align-items: center;
justify-content: center;
}
.chartBox {
width: 700px;
padding: 10px;
border-radius: 20px;
border: solid 3px rgba(255, 26, 104, 1);
background: white;
}
#barModal.hide{
z-index:-1;
opacity: 0;
transition:opacity 0.2s;
}
#barModal{
z-index:1;
opacity: 100;
transition:opacity 0.2s;
align-items: center;
text-align: center;
}
.modal-background{
position: fixed;
margin: 0;
padding: 0;
left: 0;
top: 0;
height: 100vh;
width: 100vw;
background-color:rgba(0,0,0,0.5);
}
.modal{
margin-top:10%;
margin-left:30%;
width: 700px;
height: 400px;
background-color: white;
display: flex;
flex-flow:column;
}
.modal-header,.modal-body,.modal-footer{
border:1px solid#666;
border-top:1px;
border-left:1px;
border-right:1px;
padding: 20px;
}
.modal-body{
flex:1 1 auto;
}
.btn{
border-radius: 5px;
padding: 10px;
font-weight: bold;
border: 0;
cursor: pointer;
color: white;
}
.btn.danger{
background-color: rgba(255,26,104,1);
border: 1px solid rgba(255,26,104,1);
}
</style>
</head>
<body>
<div class="chartMenu">
</div>
<div class="chartCard">
<div class="chartBox">
<LABEL>START DATE:</LABEL> <input type="date" onchange="stratDateFilter(this)" id="startDate" value="" min="2022-01-01">
<LABEL>END DATE:</LABEL> <input type="date" onchange="endDateFilter(this)" id="endDate" value="" min="2022-04-02">
<canvas id="myChart"></canvas>
</div>
</div>
<?php
try {
$sql = "SELECT COUNT(*), reportDate FROM micra.tblblotter GROUP BY reportDate";
$result = $pdo ->query($sql);
if ($result ->rowCount()>0) {
while ($row = $result ->fetch()) {
$dateArray[] = $row["reportDate"];
$orderPrice[] = $row["COUNT(*)"];
}
unset($result);
}else{
echo "No result in Db";
}
} catch(PDOExeption $e){
die("Error");
unset($pdo);
}
?>
<div class="container">
<div id="barModal" class="modal-background">
<div class="modal">
<div class="modal-header">
<h3><b>Selected Date: <span class="label"></span></b></h3>
<h3><b><span id="DateSelected"></span></b></h3>
<?php
$sample = '<span id="DateSelected"></span>';
?>
</div>
<h3><b>Total number of reports that day: <span id="DateSelected" class="value"></span></b></h3>
<div class="modal-body">
<div class="table table-responsive">
<?php
echo $sample;
$username = "root";
$password = "";
$database = "micra";
$mysqli = new mysqli("localhost", $username, $password, $database);
$query = " SELECT * FROM `tblblotter` WHERE reportDate LIKE '%".<script>."%' ";
echo '<table border="0" cellspacing="2" cellpadding="2">
<tr>
<td> <font face="Arial">Date reported</font> </td>
<td> <font face="Arial">Time reported</font> </td>
<td> <font face="Arial">Location of incident</font> </td>
<td> <font face="Arial">Complainant first name</font> </td>
<td> <font face="Arial">Complainant last name</font> </td>
</tr>';
if ($result = $mysqli->query($query)) {
while ($row = $result->fetch_assoc()) {
$field1name = $row["reportDate"];
$field2name = $row["reportTime"];
$field3name = $row["locationofincident"];
$field4name = $row["complainantfirstname"];
$field5name = $row["complainantlastname"];
echo '<tr>
<td>'.$field1name.'</td>
<td>'.$field2name.'</td>
<td>'.$field3name.'</td>
<td>'.$field4name.'</td>
<td>'.$field5name.'</td>
</tr>';
}
$result->free();
}
?>
</div>
<div class="modal-footer">
<button onclick="modalClose()" class="btn danger">Close</button>
<button class="btn danger">Print</button>
</div>
</div>
</div>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chartjs-adapter-date-fns/dist/chartjs-adapter-date-fns.bundle.min.js"></script>
<script>
const dateArrayJS= <?php echo json_encode($dateArray); ?>;
const dateChartJS = dateArrayJS.map((day,index)=>{
let dayjs = new Date(day);
return dayjs.setHours(0,0,0,0);
});
// setup
const data = {
labels: dateChartJS,
datasets: [{
label: 'Total Number of report',
data: <?php echo json_encode($orderPrice); ?>,
backgroundColor: [
'rgba(255, 26, 104, 0.2)',
],
borderColor: [
'rgba(255, 26, 104, 1)',
],
borderWidth: 1
}]
};
// config
const config = {
type: 'bar',
data,
options: {
scales: {
x:{
min:'2022-04-01',
max:'2022-04-30',
type:'time',
time:{
unit:'day'
}
},
y: {
title: {
display: true,
text: 'Number of Submitted Report'
},
beginAtZero: true
}
}
}
};
const ctx =document.getElementById('myChart');
const myChart = new Chart(
ctx,
config
);
const barModal = document.getElementById('barModal');
function modalClose(){
barModal.classList.toggle('hide');
}
function clickHandler(click){
const points = myChart.getElementsAtEventForMode(click,'nearest',{
intersect:true},true);
if(points[0]){
const firstPoint = points[0];
var timestamps = myChart.data.labels[firstPoint.index];
var dates2 = new Date(timestamps);
var dd = String(dates2.getDate()).padStart(2, '0');
var mm = String(dates2.getMonth() + 1).padStart(2, '0'); //January is 0!
var yyyy = dates2.getFullYear();
var newDate = mm + '/' + dd + '/' + yyyy;
var newdate= (dates2.getMonth() + 1) + '/' + dates2.getDate() + '/' + dates2.getFullYear();
var values = myChart.data.datasets[firstPoint.datasetIndex].data[firstPoint.index];
console.log(document.getElementById("DateSelected").innerHTML = newDate);
console.log(points[0]);
var datadisplay = document.querySelectorAll('.label');
datadisplay.forEach(label =>{
label.innerText = newDate;
})
var datavaluedisplay = document.querySelectorAll('.value');
datavaluedisplay.forEach(value =>{
value.innerText =values;
})
barModal.classList.toggle('hide');
}
myChart.update();
}
ctx.onclick =clickHandler;
function stratDateFilter(date){
const startDate = new Date(date.value);
var minToDate = document.getElementById("startDate").value;
document.getElementById("endDate").setAttribute("min", minToDate);
myChart.config.options.scales.x.min = startDate.setHours(0,0,0,0);
myChart.update();
}
function endDateFilter(date){
const endDate = new Date(date.value);
myChart.config.options.scales.x.max = endDate.setHours(0,0,0,0);
myChart.update();
}
</script>
</body>
</html>
