I have an HTML table with an Edit button that can be clicked which will allow a user to edit any <td>
in the row. Once a user hits save, it then uses Ajax to send the values to update.php
with all of the row info that is put into an update query. However, when the update query runs, all of the values except for 4 columns (species, container, mill, uom), get entered into the table in the DB either as 0
or are just blank. The column types where this is happening in the table are mostly float(8)
with some being nvarchar(255)
.
So it seems like the values are passing into update.php
just fine so I am guessing that something might be wrong with my update query? Any advice would absolutely be appreciated on how I might be able to fix my update query issue to stop updating values to 0/blank to the actual values that are entered.
Javascript and Ajax (the names in quotes after the 'case' are the classes for the corresponding row:
var isValid = true;
var errors = '';
var elements = tds;
var dict = {};
var selected;
elements.each(function (index, element) {
var type = $(this).attr('class');
var value = (element.tagName == 'INPUT') ? $(this).val() : $(this).text();
switch (type) {
case "species":
if (value) {
dict["Species"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Species
";
}
break;
case "container":
if (value) {
dict["Container"] = value;
break;
}
else {
isValid = false;
errors += "Please enter Yes or No
";
}
break;
case "supp-name":
if (value) {
dict["Supplier Name"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Supplier Name
";
}
break;
case "supp-sku":
if (value) {
dict["Supplier SKU"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Supplier SKU
";
}
break;
case "newsku":
if (value) {
dict["Current SKU"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Current SKU
";
}
break;
case "mill":
if (value) {
dict["Mill"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Mill
";
}
break;
case "lead-time":
if (value) {
dict["Lead Time"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Lead Time
";
}
break;
case "less-than-unit-cost":
if (value) {
dict["Less Than Unit Cost"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Less Than Unit Cost
";
}
break;
case "unit-cost":
if (value) {
dict["Unit Cost"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Unit Cost
";
}
break;
case "mill-direct-cost":
if (value) {
dict["Mill Direct Cost"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Mill Direct Cost
";
}
break;
case "unit-quantity":
if (value) {
dict["Unit Quantity"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Unit Quantity
";
}
break;
case "bundle-cost":
if (value) {
dict["Bundle Cost"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid number
";
}
break;
case "mixed-unit-price":
if (value) {
dict["6+ Mixed Unit Price"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid number
";
}
break;
case "uom":
if (value) {
dict["UOM"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid UOM
";
}
break;
case "mill-loc":
if (value) {
dict["Mill Location"] = value;
break;
}
else {
isValid = false;
errors += "Please enter a valid Mill Location
";
}
break;
case "id":
dict["ID"] = value;
break;
case "dropdown-select":
var $row = $(this).parents('tr');
selected = $row.find($(".selected")).val();
console.log(selected);
break;
}
})
if (isValid) {
console.log(dict);
console.log(selected);
$this.val('Edit');
tds.prop('contenteditable', false);
var request = $.ajax({
type: "POST",
url: "update.php",
data: dict, selected,
success: function(data){
console.log(dict);
console.log(selected);
}
});
request.done(function (response, textStatus, jqXHR){
if(JSON.parse(response) == true){
console.log("row updated");
} else {
console.log("row failed to updated");
console.log(response);
console.log(textStatus);
console.log(jqXHR);
}
});
}
Variables that bring in the value on update.php
:
$species = $_POST['Species'];
$container = $_POST['Container'];
$supp_name = $_POST['Supplier Name'];
$supp_sku = $_POST['Supplier SKU'];
$current_sku = $_POST['Current SKU'];
$mill = $_POST['Mill'];
$lead_time = $_POST['Lead Time'];
$less_than_unit_cost = $_POST['Less Than Unit Cost'];
$unit_cost = $_POST['Unit Cost'];
$mill_direct_cost = $_POST['Mill Direct Cost'];
$unit_quantity = $_POST['Unit Quantity'];
$bundle_cost = $_POST['Bundle Cost'];
$mixed_unit_price = $_POST['6+ Mixed Unit Price'];
$uom = $_POST['UOM'];
$mill_loc = $_POST['Mill Location'];
$id = $_POST['ID'];
$selected = $_POST['selected'];
Update query in update.php
:
$host="xxxxxxx";
$dbName="xxxx";
$dbUser="xxxxxxxxxx";
$dbPass="xxxxxxxx";
$pdo = new PDO("sqlsrv:Server=$host;Database=$dbName", $dbUser, $dbPass);
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$sql = "UPDATE Example_Table SET [Species] = '$species',
[Container] = '$container',
[Supplier Name] = '$supp_name',
[Supplier SKU] = '$supp_sku',
[Current SKU] = '$current_sku',
[Mill] = '$mill',
[Lead Time] = '$lead_time',
[Less Than Unit Cost] = '$less_than_unit_cost',
[Unit Cost] = '$unit_cost',
[Mill Direct Cost] = '$mill_direct_cost',
[Unit Quantity] = '$unit_quantity',
[Bundle Cost] = '$bundle_cost',
[6+ Mixed Unit Price] = '$mixed_unit_price',
[UOM] = '$uom',
[Mill Location] = '$mill_loc'
WHERE ID = $id";
$stmt = $pdo->prepare($sql);
$result = $stmt->execute();
echo json_encode($result);