The error you are encountering is due to the use of condition as a column name, which is a reserved keyword in SQL. To fix this, you can either rename the column or enclose it in backticks. Here's the corrected SQL query:
CREATE TABLE odontogram (
id INT AUTO_INCREMENT PRIMARY KEY,
tooth_number INT NOT NULL,
`condition` ENUM('decayed', 'missing', 'stains', 'none') NOT NULL,
decay_classification VARCHAR(50),
missing_classification VARCHAR(50),
UNIQUE(tooth_number)
);
Using backticks around condition should resolve the issue.
The error is due to the use of a reserved keyword (condition) in your SQL statement. In SQL, reserved keywords must be escaped to avoid syntax errors.
To escape the keyword condition, you should use backticks around it:
Here's the corrected version of your submit_odontogram.php:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
$servername = "sql308.infinityfree.com";
$username = "if0_36957028";
$password = "gslphdram";
$dbname = "if0_36957028_school";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(json_encode(["error" => "Connection failed: " . $conn->connect_error]));
}
// Collect POST data
$toothConditions = [
'decayed' => explode(',', $_POST['decayed']),
'missing' => explode(',', $_POST['missing']),
'stains' => explode(',', $_POST['stains']),
'none' => explode(',', $_POST['none']),
];
$errors = [];
foreach ($toothConditions as $condition => $teeth) {
foreach ($teeth as $tooth) {
$tooth = intval($tooth);
if ($tooth > 0) {
$sql = "INSERT INTO odontogram (tooth_number, `condition`) VALUES ($tooth, '$condition')
ON DUPLICATE KEY UPDATE `condition`='$condition'";
if (!$conn->query($sql)) {
$errors[] = "Error: " . $sql . "<br>" . $conn->error;
}
}
}
}
$conn->close();
if (empty($errors)) {
echo json_encode(["success" => "Data successfully updated"]);
} else {
echo json_encode(["error" => $errors]);
}
?>
Debugging Output¶
If you want to include debugging output to ensure everything else is working, you can keep the debugging statements as well:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
echo "Debugging Start<br>";
$servername = "sql308.infinityfree.com";
$username = "if0_36957028";
$password = "gslphdram";
$dbname = "if0_36957028_school";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(json_encode(["error" => "Connection failed: " . $conn->connect_error]));
}
echo "Database connection successful<br>";
// Collect POST data
$toothConditions = [
'decayed' => explode(',', $_POST['decayed']),
'missing' => explode(',', $_POST['missing']),
'stains' => explode(',', $_POST['stains']),
'none' => explode(',', $_POST['none']),
];
var_dump($toothConditions);
echo "POST data collected<br>";
$errors = [];
foreach ($toothConditions as $condition => $teeth) {
foreach ($teeth as $tooth) {
$tooth = intval($tooth);
if ($tooth > 0) {
$sql = "INSERT INTO odontogram (tooth_number, `condition`) VALUES ($tooth, '$condition')
ON DUPLICATE KEY UPDATE `condition`='$condition'";
if (!$conn->query($sql)) {
$errors[] = "Error: " . $sql . "<br>" . $conn->error;
}
}
}
}
echo "SQL queries executed<br>";
$conn->close();
if (empty($errors)) {
echo json_encode(["success" => "Data successfully updated"]);
} else {
echo json_encode(["error" => $errors]);
}
echo "Script execution completed<br>";
?>
This should resolve the SQL syntax error and allow your script to insert and update the data correctly.
Your submit_odontogram.php script looks good for inserting the tooth conditions into your MySQL database. To ensure the script handles the AJAX request correctly and provides feedback to the client, it's important to include proper responses and error handling. Here is a revised version of your script with added JSON responses:
<?php
$servername = "sql308.infinityfree.com";
$username = "if0_36957028";
$password = "gslphdram";
$dbname = "if0_36957028_school";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(json_encode(["error" => "Connection failed: " . $conn->connect_error]));
}
// Collect POST data
$toothConditions = [
'decayed' => explode(',', $_POST['decayed']),
'missing' => explode(',', $_POST['missing']),
'stains' => explode(',', $_POST['stains']),
'none' => explode(',', $_POST['none']),
];
$errors = [];
foreach ($toothConditions as $condition => $teeth) {
foreach ($teeth as $tooth) {
$tooth = intval($tooth);
if ($tooth > 0) {
$sql = "INSERT INTO odontogram (tooth_number, condition) VALUES ($tooth, '$condition')
ON DUPLICATE KEY UPDATE condition='$condition'";
if (!$conn->query($sql)) {
$errors[] = "Error: " . $sql . "<br>" . $conn->error;
}
}
}
}
$conn->close();
if (empty($errors)) {
echo json_encode(["success" => "Data successfully updated"]);
} else {
echo json_encode(["error" => $errors]);
}
?>
Explanation:¶
- Connection Check:
-
The script checks the connection to the database. If it fails, it returns a JSON response with the error message.
-
Collect POST Data:
-
The script collects the
decayed,missing,stains, andnonedata from the POST request and explodes them into arrays. -
Insert or Update Database Records:
-
For each tooth condition, the script inserts or updates the database records. If an error occurs during the SQL query, it stores the error message in the
$errorsarray. -
Response Handling:
- After processing all records, the script checks if there were any errors. If not, it returns a success message in JSON format. If there were errors, it returns the error messages in JSON format.
JavaScript Update¶
To ensure proper handling of the JSON response from the server, the JavaScript code should be slightly adjusted:
$('#submit-button').on('click', function(e) {
e.preventDefault();
$.ajax({
url: $('#odontogram-form').attr('action'),
type: $('#odontogram-form').attr('method'),
data: {
decayed: toothConditions.decayed.join(','),
missing: toothConditions.missing.join(','),
stains: toothConditions.stains.join(','),
none: toothConditions.none.join(',')
},
success: function(response) {
if (response.success) {
alert('Summary updated: ' + response.success);
} else if (response.error) {
alert('Error updating summary: ' + response.error);
}
console.log('Response:', response);
},
error: function(xhr, status, error) {
alert('Error updating summary: ' + error);
console.error('Error updating summary:', error);
}
});
});
Explanation:¶
- Success Callback:
-
The
successcallback now checks forresponse.successandresponse.errorto display appropriate messages based on the JSON response from the server. -
Error Callback:
- The
errorcallback remains the same, handling any AJAX request errors.
This setup ensures that your application provides clear feedback to the user about the success or failure of the database operations.
To view the data in a table form and odontogram, you need to create a PHP script that fetches the data from the database and displays it in a structured HTML table. Below is an example of how you can do this:
Fetch and Display Data in Table Form¶
1. Create a PHP Script to Fetch and Display Data¶
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Odontogram Data</title>
<style>
table {
width: 100%;
border-collapse: collapse;
}
table, th, td {
border: 1px solid black;
}
th, td {
padding: 10px;
text-align: left;
}
th {
background-color: #f2f2f2;
}
</style>
</head>
<body>
<h1>Odontogram Data</h1>
<table>
<tr>
<th>Tooth Number</th>
<th>Condition</th>
</tr>
<?php
$servername = "sql308.infinityfree.com";
$username = "if0_36957028";
$password = "gslphdram";
$dbname = "if0_36957028_school";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Fetch data
$sql = "SELECT tooth_number, `condition` FROM odontogram";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while ($row = $result->fetch_assoc()) {
echo "<tr>
<td>" . $row["tooth_number"] . "</td>
<td>" . $row["condition"] . "</td>
</tr>";
}
} else {
echo "<tr><td colspan='2'>No data available</td></tr>";
}
$conn->close();
?>
</table>
</body>
</html>
2. Display Odontogram with Conditions¶
You can integrate this with your previous HTML and JavaScript odontogram to highlight the conditions on the SVG odontogram. Here's how you can do it:
Fetch Conditions and Integrate with SVG¶
```php
Odontogram Data
| Tooth Number | Condition |
|---|---|
| " . $row["tooth_number"] . " | " . $row["condition"] . " |
| No data available | |