Operator in WHERE clause from variable

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:

$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;

when I run the update from the form I my statement looks like:

UPDATE tblsupplements SET 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:

//connection to database
include_once 'includes/dbconnect.inc.php';
$msg = "";

$id = isset($_REQUEST['Supplement_id']) ? $_REQUEST['Supplement_id'] : "0";
$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);

<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; ?>
<a href='supplementSearch.php'>Back To Supplement Search</a> <br><br>

In database designs, identifiers should be auto-increment integer primary index values from the parent/defining table. This will use the least amount of storage space and result in the fastest queries. This alone would ‘correct’ the current error since the code-pattern you copied assumes the id value is an integer. By using name-number string based id values, you are creating more work for yourself and then you must treat those values as strings everywhere, which is the current issue causing the error. It’s not being treated as a string in the WHERE clause.

Before you go any further, do NOT put external, unknown, dynamic data values directly into an sql query statement, since this will allow any sql special characters in the data, such as a quote, to break the sql query syntax, which is how sql injection is accomplished. Use a prepared query instead, with a place-holder in the sql query statement for each data value, then supply the actual data when the query gets executed.

1 Like

Thank you PHDR, this will steer me in the right direction

Sponsor our Newsletter | Privacy Policy | Terms of Service