Short version: I was working on an assignment on my localhost building a poll. I got it working and everything looked good so I uploaded it to my website so I could check it and then submit it. Then the chart wasn't working correctly.
Long version: I put together a poll that draws the info for the poll from a database, iterates the number of votes based on which radio button was selected when submitted, then outputs the name and the number of votes in the order of most votes to least votes. Then the assignment wants some sort of visual representation of the amount of votes, so I used Chart.js in order to make a bar graph. Once I figured out how to integrate it into my site, it worked great initially. All of the selections showed up on the chart correctly. So I uploaded it to my site and then check to make sure it actually worked. The chart no longer works correctly. I'll include a picture of it because that will work so much better than just trying to explain it.
As you can see, the bar graph has the numbers initially going from 0 to 1.0, which I didn't notice initially on my localhost testing ground. But here's the kicker: it started doing the same thing on the localhost, too. I have no idea why or what changed. I thought maybe it had something to do with my setting the "id" column in PHPMyAdmin to primary since that's the only thing I changed after getting everything working, but I removed that after clearing all the votes from the "num" column and it still doesn't work. I sadly don't have a screenshot from that earlier version that worked. But it was the same type of bar graph, with the numbers on the left side going from 1-7 (doing testing I voted for one 7 times), and all of the votes were counted correctly. As you can see from these screenshots, it's not doing that correctly now.
I have no idea what I could possibly have done to cause this. As you can see from the pictures, it also isn't adding the last vote to the chart. For instance, you can see that Dr. Forrester has 1 vote in the list above, but it's not showing up in the chart. If I were to go and vote for something else, the vote for Dr Forrester would show up, but then whatever I voted for next wouldn't show up. So it's always at least one vote behind.
I really don't know what I'm doing wrong. I'm including my code to see if anybody can point out where the problem lies.
Here is my main page:
<?php
include 'includes/db_connection.php';
include 'includes/functions.php';
//Connect to db
$results = getMST3KCharacters();
$conn = connectToDatabase();
if(isset($_POST['submit-button'])) {
$selected_character = $_POST['fav_character'];
incrementCharacterCount($selected_character);
}
?>
<!doctype html>
<html lang="en">
<head>
<title>MST3K Character Poll</title>
<link rel="stylesheet" href="css/style.css" type="text/css">
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
<?php if(!isset($_POST['submit-button'])) : ?>
<div class="main">
<h1 class="orbitron-main">Who is your favorite MST3K character?</h1>
<form method="post" name="mst3k_character">
<?php while ($row = mysqli_fetch_assoc($results)) : ?>
<input type="radio" id="<?php echo $row['name']; ?>" name="fav_character" value="<?php echo $row['name']; ?>">
<label for="<?php echo $row['name']; ?>"><?php echo $row['name']; ?></label><br>
<?php endwhile; ?>
<br><input type="submit" name="submit-button" value="Submit">
</form>
</div>
<?php else : ?>
<div class="main">
<h1 class="message">Thank you for your submission!</h1>
<h2>The results are:</h2>
<?php
$sorted_results = resultsSortedByNum();
while ($row = mysqli_fetch_assoc($sorted_results)) {
$voteCount = $row['num'];
$voteLabel = ($voteCount == 1) ? "vote" : "votes";
echo $row['name'] . ": " . $voteCount . " " . $voteLabel . "<br>";
}
?>
<div>
<canvas id="myChart"></canvas>
</div>
</div>
<?php endif; ?>
<?php
$chartData = extractChartData($results);
echo "<script>";
echo "const characterNames = " . json_encode($chartData['characterNames']) . ";";
echo "const voteCounts = " . json_encode($chartData['voteCounts']) . ";";
echo "</script>";
?>
<?php
mysqli_close($conn);
?>
<script src="js/script.js"></script>
</body>
</html>
Here is my JavaScript file:
const ctx = document.getElementById('myChart');
new Chart(ctx, {
type: 'bar',
data: {
labels: characterNames,
datasets: [{
label: '# of Votes',
data: voteCounts,
borderWidth: 1
}]
},
options: {
scales: {
y: {
beginAtZero: true
}
}
}
});
My functions.php file:
<?php
function getMST3KCharacters() {
$conn = connectToDatabase();
//WRITE DB QUERY
$sql = 'SELECT * FROM mst3k_characters;';
//RUN DB QUERY
$results = mysqli_query($conn, $sql);
if (!$results) {
die("Error in SQL query: " . mysqli_error($conn));
}
if (mysqli_num_rows($results) == 0) {
die("No records found in the database.");
}
mysqli_close($conn);
return $results;
}
function incrementCharacterCount($character_name) {
$conn = connectToDatabase();
$sql = "UPDATE mst3k_characters SET num = num + 1 WHERE name = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "s", $character_name);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
}
function resultsSortedByNum() {
$conn = connectToDatabase();
$sql = "SELECT * FROM mst3k_characters ORDER BY num DESC";
$result = mysqli_query($conn, $sql);
return $result;
}
function extractChartData($results) {
$characterNames = [];
$voteCounts = [];
while ($row = mysqli_fetch_assoc($results)) {
$characterNames[] = $row['name'];
$voteCounts[] = $row['num'];
}
return [
'characterNames' => $characterNames,
'voteCounts' => $voteCounts
];
}
?>
My database connection is handled in its own php file. The function for the connection is:
function connectToDatabase() {
$conn = mysqli_connect(HOST, USER, PASS, DB);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
return $conn;
}
I am new at this, so I realize my code must look awful. But for everything other than the chart, it does work. I really just don't know how to fix the chart. Any help would be greatly appreciated.
At first I thought it was an issue with setting the "id" column on my database as the primary, but that doesn't seem to be the issue since I removed it and I'm still having the same problem. I really don't know what else I could change.
EDIT: I figured out that to change the scale, I needed to alter part of the JavaScript by adding ticks and then stepSize in the options section:
options: {
indexAxis: 'x',
scales: {
y: {
beginAtZero: true,
ticks: {
stepSize: 1
}
}
}
}
Now the main problem is that apparently my database is not updating before the JS pulls the info to generate the chart. Does anybody see where that might be an issue in the code?
EDIT 2 Ok, so I figured out that my localhost (which is on my computer) is for some reason on the wrong time. I've gone into the php.ini file to change the date.timezone, but that doesn't appear to be doing anything. In the error_log I still show the time as being wildly off. As in, by 7 hours. Is there someplace else that I'm supposed to change something?
EDIT 3 Figured out that there were TWO date.timezone in the php.ini file and one was set to a European time. Changed it and now my error_log tells me the time is correct, but the info I'm getting is still one submission behind. Please help! This is driving me crazy!
FINAL EDIT
After having a short meeting with my professor the problem was fixed and I know what the issue was: The line $results = getMST3KCharacters(); needed to be moved below the function call for incrementCharacterCount. So I literally just moved that one line to the bottom of that block of PHP and the chart works totally fine now with the proper data. I just want to make sure that anybody who has a similar issue and stumbles on this post knows it can be something that simple.