Hi PHP/Sql Guru’s!
I am having difficulty updating values from a PHP for into MYSQL.
my problem is that my WHERE clause picks the variable up with the PK up as a value to - (minus) something.
Thus, I need to ignore the - operator.
My Statement:
Blockquote
$query1 = “UPDATEtblsupplementsSET Supplement_id=’$Supplement_id’, Supplier_ID=’$Supplier_ID’, Description=’$Description’, Cost_excl=’$Cost_excl’, Cost_incl=’$Cost_incl’, Min_levels=’$Min_levels’, Current_stock_levels=’$Current_stock_levels’, Nappi_code=’$Nappi_code’ WHERE Supplement_id =”.$id;
when I run the update from the form I my statement looks like:
Blockquote
UPDATEtblsupplementsSET Supplement_id=‘Supplement-104’, Supplier_ID=‘SUPPLIER G’, Description=‘Vitamin Double C’, Cost_excl=‘363.00’, Cost_incl=‘417.45’, Min_levels=‘2’, Current_stock_levels=‘5’, Nappi_code=’’ WHERE Supplement_id =Supplement-104
with error
Unable to Update! Unknown column ‘Supplement’ in ‘where clause’
If I for example use a LIKE operator in the where clause it works 100%, but as soon as I try and update for example supplement-1 then there are more that one value.
my code in totality:
<?php
//connection to database
include_once 'includes/dbconnect.inc.php';
$msg = "";
$id = isset($_REQUEST['Supplement_id']) ? $_REQUEST['Supplement_id'] : "0";
//Tax
$vat = 1.15;
//Query to update data and show successfull or !
//Button to submit and update data
if(isset($_REQUEST['btnSubmit'])) {
$Supplement_id = $_REQUEST['txtSupplementID'];
$Description = $_REQUEST['txtDescription'];
$Cost_excl = $_REQUEST['txtCost_excl'];
$Cost_incl = $_REQUEST['txtCost_incl'];
$Current_stock_levels = $_REQUEST['txtCurrent_stock_levels'];
$Min_levels = $_REQUEST['txtMin_levels'];
$Supplier_ID = $_REQUEST['txtSupplier_ID'];
$Nappi_code = $_REQUEST['txtNappi_code'];
$query1 = "UPDATE `tblsupplements` SET Supplement_id='$Supplement_id', Supplier_ID='$Supplier_ID', Description='$Description', Cost_excl='$Cost_excl', Cost_incl='$Cost_incl', Min_levels='$Min_levels', Current_stock_levels='$Current_stock_levels', Nappi_code='$Nappi_code' WHERE Supplement_id =".$id;
if(mysqli_query($conn, $query1)){
$msg = "Record Updated!";
} else {
$msg = "Unable to Update! ". $conn->error. " <br><br>".$query1;
}
}
//Query to Prepopulate data from Database into text fields
$query = "SELECT * FROM `tblsupplements` WHERE Supplement_id LIKE '%$id%' ";
$data = mysqli_query($conn, $query);
$rec = mysqli_fetch_array($data);
?>
<body>
<form method="POST">
Supplement ID : <input type="text" value="<?php echo $rec['Supplement_id']; ?>" name="txtSupplementID" readonly="readonly"/><br/>
Description : <input type="text" value="<?php echo $rec['Description']; ?>" name="txtDescription"/><br/>
Current Stock : <input type="text" value="<?php echo $rec['Current_stock_levels']; ?>" name="txtCurrent_stock_levels"/><br/>
Minimum Stock : <input type="text" value="<?php echo $rec['Min_levels']; ?>" name="txtMin_levels"/><br/>
Supplier Of Goods : <input type="text" value="<?php echo $rec['Supplier_ID']; ?>" name="txtSupplier_ID"/><br/>
Nappi Code : <input type="text" value="<?php echo $rec['Nappi_code']; ?>" name="txtNappi_code"/><br/>
Cost Excl Vat : <input type="text" value="<?php echo $rec['Cost_excl']; ?>" name="txtCost_excl"/><br/>
Cost Incl Vat : <input type="text" value="<?php echo $rec['Cost_excl']*$vat; ?>" name="txtCost_incl"/><br/>
<input type="submit" name="btnSubmit" value="Update"/> <br/>
<?php echo $msg; ?>
</form>
<a href='supplementSearch.php'>Back To Supplement Search</a> <br><br>
</body>
</html>