I am making a website where I can enter data for golf course greens, and then analyze it. I am using express in my backend to route to my ejs pages. I am also using a mySQL database to store the data. When creating the charts I used chart.js from w3schools, which got me to create a script within the html code to insert the javascript code. But then realized that I would have to create a database connection in the ejs file (which seems inefficient and unsafe (creating multiple connections to DB)). I then spent ages, creating functions inside my js file with the database and express connections where I do all my other queries. I ended just trying to keep the script from website in the ejs file, and then try and pass the data from my js file into this ejs file to display. This is the reason that I'm asking for help. I can't figure out how to pass this list of data from my js file to my ejs file to use in a script? While, thinking about it. There must be a more efficient way of doing this?
EJS file where I am trying to display the graph:
<body>
<div class="container">
<div class="chart">
<canvas id="myChart" style="width:100%;max-width:700px"></canvas>
</div>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function () {
const scatterGraphData = JSON.parse(document.getElementById('myChart').dataset.scatterData);
new Chart("myChart", {
type: "scatter",
data: {
datasets: [{
pointRadius: 4,
pointBackgroundColor: "rgb(0,0,255)",
data: scatterGraphData
}]
},
options: {
legend: { display: false },
scales: {
xAxes: [{ ticks: { min: 40, max: 160 } }],
yAxes: [{ ticks: { min: 6, max: 16 } }],
}
}
});
});
</script>
</body>
Function which calculates the data:
async function getChartData() {
const now = new Date();
const formattedDate = now.toISOString().split('T')[0];
connection.query("SELECT * FROM sample WHERE Date = ?", [formattedDate], (error, results) => {
if (error) {
console.log("Database Error inside getChartData");
return;
}
if (results.length === 0) {
// No data found today
console.log("Entered in the no data found");
// random test data to see if graph even works
const defaultData = [
{ x: 50, y: 7 },
{ x: 60, y: 8 },
{ x: 70, y: 8 },
{ x: 80, y: 9 },
{ x: 90, y: 9 },
{ x: 100, y: 9 },
{ x: 110, y: 10 },
{ x: 120, y: 11 },
{ x: 130, y: 14 },
{ x: 140, y: 14 },
{ x: 150, y: 15 }
];
return defaultData;
}
// real Data exists to map
let AveragesList = [];
let counter = 0;
const sampleHoles = [2, 12, 16];
for (const holeid of sampleHoles) {
const holeRecord = connection.query("SELECT * FROM holes WHERE Id = ?", [results[0][holeid]]);
const upSlopeData = connection.query("SELECT * FROM upslopespeed WHERE UpSlopeID = ?", [holeRecord[0].UpSlopeSpeedID]);
const downSlopeData = connection.query("SELECT * FROM downslopespeed WHERE DownSlopeID = ?", [holeRecord[0].DownSlopeSpeedID]);
const backLeftSpeed = (upSlopeData[0].BackLeft * 2) / (upSlopeData[0].BackLeft + downSlopeData[0].BackLeft);
const backCentreSpeed = (upSlopeData[0].BackCentre * 2) / (upSlopeData[0].BackCentre + downSlopeData[0].BackCentre);
const backRightSpeed = (upSlopeData[0].BackRight * 2) / (upSlopeData[0].BackRight + downSlopeData[0].BackRight);
const AverageSpeed = (backLeftSpeed + backCentreSpeed + backRightSpeed) / 3;
AveragesList[counter] = AverageSpeed;
counter++;
}
const xyValues = [
{x:2, y:AveragesList[0]},
{x:12, y:AveragesList[1]},
{x:16, y:AveragesList[2]}
];
return xyValues;
});
}
This is function that is called when the page is to be loaded:
function DashboardDetermine(req, res) {
if (req.session.user.isAdmin === 1) {
// ADMIN
// get all employees to list on admin dashboard
connection.query("SELECT * FROM user WHERE admin = 0", async (error, results) => {
if (error) {
console.error('Error executing query:', error);
req.flash('error_msg', 'An error occurred while loading users.');
res.redirect('/dashboard');
return;
}
const scatterGraphData = await JSON.stringify(getChartData());
console.log(scatterGraphData);
// Pass the results to the template
res.render('dashboardAdmin', { user: results, scatterGraphData });
});
} else {
// EMPLOYEE
res.render("dashboard");
}
}
ChatGPT suggested to format it as a JSON and then pass that when rendering the file. Not sure if this changed anything. Before I just passed the getChartData() directly. Like this res.render('dashboardAdmin', { user: results, scatterData: getChartData()}); Which didn't work.
On a day which is empty the getChartData() function should just return the fixed data which I took from the w3schools website in their example just to eliminate sources of error, it seems to get data from the getChartData() call, but the JSON file from the console.log returns {}. Which causes the chart to not appear.
This is why I am wondering whether the move the scatter graph script into the same into this JS file so I can have the functions enter the data there?
Hope this not to confusing to understand, many thanks for any help!
