I'm working on a project where I'm using Handsontable to manage data, and I'm facing an issue with updating and inserting records into a MySQL database using PHP. The problem is that only the date field is being saved, and no updates occur when changing or entering values in the Handsontable.
<?php
include 'db.php';
// Assuming your table name is 'bill_details'
$tableName = 'bill_details';
// Get the changes sent from Handsontable
$changes = json_decode(file_get_contents("php://input"), true);
if (isset($changes['changes']) && is_array($changes['changes'])) {
try {
foreach ($changes['changes'] as $change) {
// Check if the date and id keys are defined in $change
if (isset($change[3]) && isset($change[0])) {
// Handle the case where the value is an integer
if (is_int($change[3])) {
echo json_encode(['success' => false, 'message' => 'Error: Invalid data format for value ' . $change[3]]);
continue; // Skip processing this row if the data format is invalid
}
// Check the date format and convert if necessary
if (strpos($change[3], '/') !== false) {
// Convert the date format to 'Y-m-d'
$date = DateTime::createFromFormat('d/m/Y', $change[3]);
if ($date !== false) {
// Date conversion successful
$formattedDate = $date->format('Y-m-d');
// Check if a record with the given date and id already exists
$checkQuery = "SELECT COUNT(*) FROM $tableName WHERE bill_date = '$formattedDate' AND bill_id = '{$change[0]}'";
$checkResult = $con->query($checkQuery);
if ($checkResult === false) {
// Handle the case where the check query fails
echo json_encode(['success' => false, 'message' => 'Error checking record: ' . $con->error]);
continue; // Skip processing this row if the check query fails
}
$rowCount = $checkResult->fetch_assoc()['COUNT(*)'];
if ($rowCount > 0) {
// Update the existing record
$main_cat = isset($change[4]) ? $change[4] : 'DEFAULT_VALUE';
$sub_cat = isset($change[5]) ? $change[5] : 'DEFAULT_VALUE';
$job = isset($change[6]) ? $change[6] : 'DEFAULT_VALUE';
$so = isset($change[7]) ? $change[7] : null;
$wo = isset($change[8]) ? $change[8] : 'DEFAULT_VALUE';
$company = isset($change[9]) ? $change[9] : 'DEFAULT_VALUE';
$division = isset($change[10]) ? $change[10] : 'DEFAULT_VALUE';
$sub_div = isset($change[11]) ? $change[11] : 'DEFAULT_VALUE';
$amount = isset($change[12]) ? $change[12] : 'DEFAULT_VALUE';
$remarks = isset($change[13]) ? $change[13] : 'DEFAULT_VALUE';
// Update query without bind parameters
$updateQuery = "UPDATE $tableName SET main_cat = '$main_cat', sub_cat = '$sub_cat', job = '$job', so = '$so', wo = '$wo', company = '$company', division = '$division', sub_div = '$sub_div', amount = '$amount', remarks = '$remarks' WHERE bill_date = '$formattedDate' ";
$updateResult = $con->query($updateQuery);
if ($updateResult === false) {
// Handle the case where the update query fails
echo json_encode(['success' => false, 'message' => 'Error updating record: ' . $con->error]);
}
} else {
// Insert a new record
$main_cat = isset($change[4]) ? $change[4] : 'DEFAULT_VALUE';
$sub_cat = isset($change[5]) ? $change[5] : 'DEFAULT_VALUE';
$job = isset($change[6]) ? $change[6] : 'DEFAULT_VALUE';
$so = isset($change[7]) ? $change[7] : null;
$wo = isset($change[8]) ? $change[8] : 'DEFAULT_VALUE';
$company = isset($change[9]) ? $change[9] : 'DEFAULT_VALUE';
$division = isset($change[10]) ? $change[10] : 'DEFAULT_VALUE';
$sub_div = isset($change[11]) ? $change[11] : 'DEFAULT_VALUE';
$amount = isset($change[12]) ? $change[12] : 'DEFAULT_VALUE';
$remarks = isset($change[13]) ? $change[13] : 'DEFAULT_VALUE';
// Insert query without bind parameters
$insertQuery = "INSERT INTO $tableName (bill_id, bill_date, main_cat, sub_cat, job, so, wo, company, division, sub_div, amount, remarks, bill_status, emp_id) VALUES ('$nullValue', '$formattedDate', '$main_cat', '$sub_cat', '$job', '$so', '$wo', '$company', '$division', '$sub_div', '$amount', '$remarks', '$zeroValue', '$nullValue')";
$insertResult = $con->query($insertQuery);
if ($insertResult === false) {
// Handle the case where the insert query fails
echo json_encode(['success' => false, 'message' => 'Error inserting record: ' . $con->error]);
}
}
} else {
// Handle the case where date conversion fails
echo json_encode(['success' => false, 'message' => 'Error converting date format for value ' . $change[3]]);
// Log or handle the error appropriately
continue; // Skip processing this row if date conversion fails
}
} else {
// The date is already in 'Y-m-d' format, no conversion needed
$formattedDate = $change[3];
}
} else {
// Handle the case where the date or id key is not defined in $change
echo json_encode(['success' => false, 'message' => 'Error: Date or ID key not defined in $change']);
continue; // Skip processing this row if the date or id key is not defined
}
}
echo json_encode(['success' => true, 'message' => 'Data saved successfully']);
} catch (Exception $e) {
echo json_encode(['success' => false, 'message' => 'Exception: ' . $e->getMessage()]);
}
} else {
echo json_encode(['success' => false, 'message' => 'No changes to save']);
}
?>
is need the full code mailus :[email protected]
handson table code is
<?php include 'header.php';
include 'db.php';
$con1=new mysqli($servername,$username,$password,$database);
?>
<div class="page-content-wrapper">
<div class="page-content">
<div class="page-bar"></div>
<div class="row">
<div class="col-md-12 col-sm-12">
<div class="card card-box">
<div class="card-head">
<header>Petty Cash Claim- Request</header>
</div>
<div class="card-body" id="bar-parent2">
<form action="entry-action.php" method="POST" enctype="multipart/form-data" id="myForm">
<div class="row">
<div class="col-md-6 col-sm-6">
<div class="form-group">
<label>Title</label>
<input type="text" class="form-control" placeholder="Enter ..." name="title"
id="title">
</div>
<div class="form-group">
<label>Submit To</label>
<select class="form-control" name="submit_to" id="submit_to">
<option value="001">option 1</option>
<option value="002">option 2</option>
<option value="003">option 3</option>
<option value="004">option 4</option>
<option value="005">option 5</option>
</select>
</div>
</div>
<?php $qry1 = "SELECT currency FROM currencies ORDER BY currency ASC";
$result1= $con1->query($qry1);
if($result1->num_rows>0){
$options= mysqli_fetch_all($result1,MYSQLI_ASSOC);
}
?>
<div class="col-md-6 col-sm-6">
<div class="form-group">
<label>Currency</label>
<select class="form-control" name="currency" id="currency">
<option>Select Currency</option>
<?php foreach ($options as $option) {
echo "<option>" . $option['currency'] . "</option>";
} ?>
</select>
</div>
<div class="form-group">
<label>Supporting docs</label>
<input type="file" multiple class="form-control" placeholder="Enter ..."
name="files[]" id="file_upload">
</div>
<button type="button" class="btn btn-info" id="submitBtn">Submit</button>
</div>
</div>
<div class="card-body">
<div class="table-scrollable">
<table class="table" id="example1"></table>
</div>
</div>
</form>
<div class="col-md-12">
<div class="row">
<div class="col-sm-4"></div>
<div class="col-sm-4">
<div class="panel">
<header class="panel-heading panel-heading-blue">
Total Amount
</header>
<div class="panel-body">
<b><span id="column-sum">0 </span> /-</b></div>
</div>
</div>
<div class="col-sm-4"></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<?php
$duty_rows = array();
$duty_json = json_encode($duty_rows);
?>
<?php include('data1.php'); ?>
<script>
document.addEventListener("DOMContentLoaded", function () {
var jsondata = <?php echo $jsonstring; ?>;
var jsondatadiv = <?php echo $jsonstring_div; ?>;
var jsondatasub = <?php echo $jsonstring_sdiv; ?>;
var $container = $("#example1");
var hot;
$container.handsontable({
height: 450,
minCols: 12,
colWidths: [80, 100, 100, 100, 100, 90, 90, 100, 90, 90, 90, 110],
colHeaders: ["Sheet #", "Date", "Main Cat", "Sub Cat", "Job", "SO", "WO", "Company", "Division", "Sub div",
"Amount", "Remarks"
],
minSpareRows: 1,
manualRowMove: true,
licenseKey: "non-commercial-and-evaluation",
stretchH: 'all',
columns: [
{ data: '0' },
{ data: '1', type: 'date' },
{ data: '2' },
{ data: '3' },
{ data: '4' },
{ data: '5' },
{ data: '6' },
{ data: '7', type: 'dropdown', source: jsondata },
{ data: '8', type: 'dropdown', source: jsondatadiv },
{ data: '9', type: 'dropdown', source: jsondatasub },
{ data: '10' },
{ data: '11' },
],
rowHeight: function (row) {
return 10;
},
defaultRowHeight: 10,
afterChange: function (change, source) {
updateSum();
var rc = $("#rc").val();
if (rc === "" && source != 'loadData') {
alert("not saved");
}
if (source == 'loadData') {
return;
}
$.ajax({
url: 'hot.php',
contentType: 'application/json', // Add this line
type: "POST",
data: JSON.stringify({ changes: change }), // Convert data to JSON
success: function (data) {
console.log(JSON.stringify({ changes: change }));
// alert("Data transferred successfully!");
// alert ('data');
},
error: function (error) {
console.error("Error during data transfer:", error);
alert("Error during data transfer. Please check the console for details.");
}
});
},
});
hot = $container.handsontable('getInstance');
const amountColumnIndex = 10;
function updateSum() {
if (hot) {
let sum = 0;
hot.getData().forEach(row => {
const cellValue = parseFloat(row[amountColumnIndex]);
if (!isNaN(cellValue)) {
sum += cellValue;
}
});
document.getElementById('column-sum').textContent = sum;
}
}
$("#submitBtn").on("click", function () {
var formData = new FormData($("#myForm")[0]);
$.ajax({
url: 'entry-action.php',
type: "POST",
data: formData,
contentType: false,
processData: false,
success: function (data) {
//console.log("Data transferred successfully:", data);
//alert(data);
},
error: function (error) {
console.error("Error during data transfer:", error);
alert("Error during data transfer. Please check the console for details.");
}
});
});
});
</script>
The value submission id done and wecan see it on developer tools network option
I need to implement an auto-save feature for the data in each column of the rows in the Handsontable. The goal is to automatically save any updates made to the table data to the database when I click on another column in the Handsontable." the objective is to enable automatic saving of changes made to the table data in the database as soon as a user clicks on another column in the Handsontable. This means that any modifications or updates made to the data in the current column should trigger an immediate save operation to persist the changes in the underlying database. This can enhance the user experience by ensuring that data is consistently saved in real-time without requiring explicit user actions.
type here