So this was previously working using a replace but would a replace empty fields are that not present upon update?
Since moving to the update, the $id is not being read even though it's clearly shown on the statement.
function updateData($table,$id,$data){
$extra = '';
foreach ( $data as $key=>$row )
{
$set[]= $key."='".$row."'";
}
$set = implode(',',$set);
if($id){
$extra = "WHERE `id`='$id'";
}
$statement = "UPDATE `$table` SET ".$set." ". $extra;
print_r($statement);
$this->printDebug('Update Table Data',$statement);
if($stmt = $this->conn->prepare($statement)){
$stmt->execute() or trigger_error($stmt->error, E_USER_ERROR);
$last_id = $stmt->insert_id;
$response = array("message"=>"success","last_id"=>$last_id);
return ($response);
} else {
// die($this->conn->error);
trigger_error($this->conn->error,E_USER_ERROR);
}
}
UPDATE `categories` SET category_status='1',featured='1',category_name='Health & Beauty',parent_id='0',price_low='',price_high='',category_description='',id='1',tags='null',date_added='2022-06-18 22:11:31',last_modified='2022-06-18 22:11:31',meta_type='category',meta_id='0',images='{"path":[""]}'
$insertData = $b->updateData('products',$_POST['id'],$_POST);
CodePudding user response:
It looks like your php function gets values for your table's id
column from two different places.
- the
$id
parameter. - the
$data['id']
element of the$data
array parameter.
And, from the line of SQL you showed us, your function was called with the $id
parameter set to zero: your SQL has no WHERE clause. As you may know, UPDATEs without WHERE clauses try to update all rows in the table !!.
In your case the UPDATE failed because you have SET ... id=1 ...
in the SQL. The id
column is your primary key, so duplicate values are not allowed. (You can't set the id
of every row in the table to 1
.) Therefore your SQL statement failed.
You're lucky. Your table's data could have been obliterated. Be careful with WHERE clauses in UPDATE and DELETE statements, mmmk? If you get them wrong, you trash your data.
To get this working make sure your function's caller gives you the correct $id
value. Or, remove that parameter completely from the function signature, and get $id
from your $data
array.
CodePudding user response:
Thanks for the comments. I have resolved this now by using a different approach to manage additions / updates.