i have designed system whereby i use a form to input the service details into the service table and i have deployed the system on a live server after testing it on a localhost server and everything was working fine. but now once on the live server it is refusing to save the service details, giving an error occured error. i will give the server side and client side code and the database as they are and someone please help me. the following is the server side code for saving the service.
<?php
if(isset($_GET['id']) && $_GET['id'] > 0){
$qry = $conn->query("SELECT * from `service` where id = '{$_GET['id']}' ");
if($qry->num_rows > 0){
foreach($qry->fetch_assoc() as $k => $v){
$$k=stripslashes($v);
}
}
}
// Fetch vehicles data
$vehicles_query = $conn->query("SELECT license_plate, vehicle_code FROM vehicles");
$vehicles = [];
if($vehicles_query){
while($row = $vehicles_query->fetch_assoc()){
$vehicles[] = $row;
}l
}
$issues_query = $conn->query("SELECT id, description FROM issues WHERE status = 1");
$issues = [];
if($issues_query){
while($row = $issues_query->fetch_assoc()){
$issues[] = $row;
}
}
?>
<div class="card card-outline card-info">
<div class="card-header">
<h3 class="card-title"><?php echo isset($id) ? "Update ": "Create New " ?> Service</h3>
</div>
<div class="card-body">
<form action="" id="service-form">
<input type="hidden" name ="id" value="<?php echo isset($id) ? $id : '' ?>">
<div class="form-group">
<label for="date_created" class="control-label">Date Created</label>
<input name="date_created" id="date_created" type="text" class="form-control rounded-0" value="<?php echo isset($date_created) ? $date_created : date('Y-m-d H:i:s'); ?>" readonly>
</div>
<div class="form-group">
<label for="license_plate_dropdown" class="control-label">License Plate</label>
<select name="license_plate_dropdown" id="license_plate_dropdown" class="form-control rounded-0" required>
<option value="">Select a License Plate</option>
<?php foreach($vehicles as $vehicle): ?>
<option value="<?php echo $vehicle['license_plate']; ?>"
data-vehicle-code="<?php echo $vehicle['vehicle_code']; ?>"
<?php echo isset($license_plate) && $license_plate == $vehicle['license_plate'] ? 'selected' : ''; ?>>
<?php echo $vehicle['license_plate']; ?>
</option>
<?php endforeach; ?>
</select>
</div>
<div class="form-group">
<label for="vehicle_code" class="control-label">Vehicle Code</label>
<input name="vehicle_code" id="vehicle_code" type="text" class="form-control rounded-0" value="<?php echo isset($vehicle_code) ? $vehicle_code : ''; ?>">
</div>
<div class="form-group">
<label for="last_odometer" class="control-label">Mileage(km)</label>
<input name="last_odometer" id="last_odometer" type="text" class="form-control rounded-0" value="<?php echo isset($last_odometer) ? $last_odometer : ''; ?>" >
</div>
<div class="form-group">
<label for="total_costs" class="control-label">Total Cost(MZN)</label>
<input name="total_costs" id="total_costs" type="currency" class="form-control rounded-0" value="<?php echo isset($total_costs) ? $total_costs : ''; ?>" >
</div>
<div class="form-group">
<label for="service_prov" class="control-label">Work Done</label>
<textarea name="service_prov" id="" cols="30" rows="2" class="form-control form no-resize summernote"><?php echo isset($service_prov) ? html_entity_decode(stripslashes($service_prov)) : ''; ?></textarea>
</div>
<div class="form-group">
<label for="contact" class="control-label">Comments</label>
<input name="contact" id="contact" type="text" class="form-control rounded-0" value="<?php echo isset($contact) ? $contact : ''; ?>" >
</div>
<div class="form-group">
<label for="mileage" class="control-label">Job Number</label>
<input name="mileage" id="mileage" type="text" class="form-control rounded-0" value="<?php echo isset($mileage) ? $mileage : ''; ?>" >
</div>
<div class="form-group">
<label for="next_date" class="control-label">Next Date</label>
<input name="next_date" id="next_date" type="date" class="form-control rounded-0" value="<?php echo isset($next_date) ? $next_date : ''; ?>" required>
</div>
<select name="issue_id" id="issue_id" class="form-control rounded-0" required>
<option value="" selected disabled>Select an Issue</option>
<option value="null">None</option>
<?php foreach($issues as $issue): ?>
<option value="<?php echo $issue['id']; ?>">
<?php echo $issue['description']; ?>
</option>
<?php endforeach; ?>
</select>
<label for="service_classes">Select Service Class:</label>
<select id="service_classes" name="service_classes[]" multiple>
<option value="A">Class A</option>
<option value="B">Class B</option>
<option value="C">Class C</option>
<option value="D">Class D</option>
</select>
<div id="checklistContainer">
</div>
<div class="form-group">
<label for="checked_by" class="control-label">Checked By</label>
<input name="checked_by" id="checked_by" type="text" class="form-control rounded-0" value="<?php echo isset($checked_by) ? $checked_by : ''; ?>" required>
</div>
<div class="form-group">
<label for="driver_name" class="control-label">Driver's Name</label>
<input name="driver_name" id="driver_name" type="text" class="form-control rounded-0" value="<?php echo isset($driver_name) ? $driver_name : ''; ?>" >
</div>
<div class="form-group">
<label for="report" class="control-label">Description Of Issue</label>
<textarea name="report" id="" cols="30" rows="2" class="form-control form no-resize summernote"><?php echo isset($report) ? html_entity_decode(stripslashes($report)) : ''; ?></textarea>
</div>
<div class="form-group">
<label for="mechanic_name" class="control-label">Mechanic's Name</label>
<select name="mechanic_name" id="mechanic_name" class="form-control rounded-0">
<option value="">-- Select Mechanic --</option>
<?php
$mechanics = $conn->query("SELECT name FROM mechanics_list ORDER BY name ASC");
while ($mechanic = $mechanics->fetch_assoc()) {
$selected = (isset($mechanic_name) && $mechanic_name == $mechanic['name']) ? 'selected' : '';
echo "<option value='" . $mechanic['name'] . "' $selected>" . $mechanic['name'] . "</option>";
}
?>
</select>
</div>
<div class="form-group">
<label for="status" class="control-label">Status</label>
<select name="status" id="status" class="custom-select selevt">
<option value="1" <?php echo isset($status) && $status == 1 ? 'selected' : '' ?>>Active</option>
<option value="0" <?php echo isset($status) && $status == 0 ? 'selected' : '' ?>>In-Progress</option>
</select>
</div>
</form>
</div>
<div class="card-footer">
<button class="btn btn-flat btn-primary" form="service-form">Save</button>
<a class="btn btn-flat btn-default" href="?page=service">Cancel</a>
</div>
</div>
<script>
$(document).ready(function(){
$('#license_plate_dropdown').change(function(){
var vehicleCode = $('option:selected', this).data('vehicle-code');
$('#vehicle_code').val(vehicleCode);
});
$('#service_classes').change(function() {
updateChecklist();
});
$('#service-form').submit(function(e){
var dateCreated = new Date($('#date_created').val());
var nextDate = new Date($('#next_date').val());
if(nextDate <= dateCreated) {
alert('Next Date must be after the Date Created');
e.preventDefault();
return false;
}
e.preventDefault();
var _this = $(this)
$('.err-msg').remove();
start_loader();
$.ajax({
url:_base_url_+"classes/Master.php?f=save_service",
data: new FormData($(this)[0]),
cache: false,
contentType: false,
processData: false,
method: 'POST',
type: 'POST',
dataType: 'json',
error:err=>{
console.log(err)
alert_toast("An error occurred",'error');
end_loader();
},
success:function(resp){
if(typeof resp =='object' && resp.status == 'success'){
location.href = "./?page=service";
}else if(resp.status == 'failed' && !!resp.msg){
var el = $('<div>')
el.addClass("alert alert-danger err-msg").text(resp.msg)
_this.prepend(el)
el.show('slow')
$("html, body").animate({ scrollTop: _this.closest('.card').offset().top }, "fast");
end_loader()
}else{
alert_toast("An error occurred",'error');
end_loader();
console.log(resp)
}
}
})
});
function populateSavedClasses() {
let serviceId = $('#service_id').val();
$.ajax({
url: _base_url_ + "classes/Master.php?f=fetch_saved_checklist&id=" + serviceId,
method: 'GET',
dataType: 'json',
success: function(savedClasses) {
// Pre-select the saved classes in the dropdown
$('#service_classes').val(savedClasses);
updateChecklist(savedClasses);
},
error: function(err) {
console.error("Error fetching saved classes/checklist items: ", err);
}
});
}
function updateChecklist(savedClasses = []) {
let selectedClasses = $('#service_classes').val().join(',');
$.ajax({
url: _base_url_ + "classes/Master.php?f=fetch_checklist&classes=" + selectedClasses,
method: 'GET',
dataType: 'json',
success: function(data) {
let checklistHtml = "";
data.forEach(item => {
let checkedAttr = (savedClasses.includes(item.id.toString())) ? 'checked' : '';
checklistHtml += `<input type="checkbox" name="checklist_items[]" value="${item.id}" ${checkedAttr}> ${item.point_description}<br>`;
});
$('#checklistContainer').html(checklistHtml);
},
error: function(err) {
console.error("Error fetching checklist: ", err);
}
});
}
if($('#service_id').val()) {
populateSavedClasses();
}
$('.summernote').summernote({
height: 200,
toolbar: [
[ 'style', [ 'style' ] ],
[ 'font', [ 'bold', 'italic', 'underline', 'strikethrough', 'superscript', 'subscript', 'clear'] ],
[ 'fontname', [ 'fontname' ] ],
[ 'fontsize', [ 'fontsize' ] ],
[ 'color', [ 'color' ] ],
[ 'para', [ 'ol', 'ul', 'paragraph', 'height' ] ],
[ 'table', [ 'table' ] ],
[ 'view', [ 'undo', 'redo', 'fullscreen', 'codeview', 'help' ] ]
]
});
});
</script>
function save_service(){
extract($_POST);
// Convert 'null' string to actual null for issue_id
$issue_id = ($issue_id === 'null') ? null : $issue_id;
// Correctly assign license_plate from the license_plate_dropdown
if (isset($license_plate_dropdown)) {
$license_plate = $license_plate_dropdown;
}
$data = "";
foreach ($_POST as $k => $v) {
// Exclude 'id' and 'license_plate_dropdown' from the direct database insertion
if (!in_array($k, array('id', 'license_plate_dropdown', 'issue_id'))) {
// Escape string values only
if (is_string($v)) {
if (!empty($data)) $data .= ",";
$data .= " `{$k}`='" . $this->conn->real_escape_string($v) . "' ";
}
}
}
// Include license_plate into the data to be saved
if (isset($license_plate)) {
if (!empty($data)) $data .= ",";
$data .= " `license_plate`='" . $this->conn->real_escape_string($license_plate) . "' "; // Escape value
}
// Include issue_id into the data to be saved, if provided
if (!empty($issue_id)) {
if (!empty($data)) $data .= ",";
$data .= " `issue_id`='{$issue_id}' ";
}
// Check for conflicts using the id
$check = (!empty($id)) ? $this->conn->query("SELECT * FROM `service` where `id` = '{$id}' and id != {$id} ")->num_rows : 0;
if ($this->capture_err()) {
return $this->capture_err();
}
if ($check > 0) {
$resp['status'] = 'failed';
$resp['msg'] = "Service record already exists.";
return json_encode($resp);
exit;
}
if (empty($id)) {
$sql = "INSERT INTO `service` SET {$data} ";
$save = $this->conn->query($sql);
} else {
$sql = "UPDATE `service` SET {$data} WHERE id = '{$id}' ";
$save = $this->conn->query($sql);
}
if ($save) {
$resp['status'] = 'success';
if (empty($id)) {
$this->settings->set_flashdata('success', "New service record successfully saved.");
} else {
$this->settings->set_flashdata('success', "Service record successfully updated.");
}
} else {
$resp['status'] = 'failed';
$resp['err'] = $this->conn->error . "[{$sql}]";
}
return json_encode($resp);
}
so the first code block is the form input and the second is the server side save function
i tried changing the column names of the database table, tried recreating the table adding the issue_id as the foreign key from the issues table but still didn’t work