I'm trying to understand how can I replace or edit a key-value inside a JSON array
Basically i have a table called Bills
this table contains a couple of column among them, there is a JSON-type column which contains an Array for the products
or items
in the current bill
the array format is this:-
Array ( [0] => Array
( [items] => [{"0": "1", "1": "o4", "2": "Efexor", "3": "Eklil", "4": "1", "5": "30", "6": "15", "7": "Phezer", "8": "Venlafaxine", "9": "", "10": "0", "11": "2020-03-25", "12": "2021-08-25", "13": "1", "14": "0", "15": "0", "16": "caps", "17": "..\\upload\\Efexor.jpg",
"id": "1", "qty": "15", "Sale": "1", "name": "Efexor", "bonus": "0", "price": "30", "company": "Phezer", "draftid": "1", "discount": "0", "imageurl": "..\\upload\\Efexor.jpg", "ownedcom": "Eklil", "packsize": "0", "codenumber": "o4", "chemicalcom": "Venlafaxine", "dosage-form": "caps", "concentration": "", "expirationdate": "2021-08-25", "productiondate": "2020-03-25"},
so, as you can see the array contains items information like (name company..etc)
what I'm trying to do is replace a specific key-value
like the Price
key
What I have tried:
i tried to use some of JSON query like (JSON_search, JSON_replace,...others) so i created a class method to handle it :-
public function AlterJSON($item,$key,$newValue){
if($this->connected === true){
try{
$JsonItems = $this->connection->prepare("UPDATE `bills`
SET `items` = JSON_REPLACE(`items`, '$.name', ':value')
WHERE JSON_CONTAINS(`items`, ':item', '$.name');");
$JsonItems->bindParam(":item",$item);
$JsonItems->bindParam(":value",$newValue);
$JsonItems->execute();
echo 'Value for The'.$key. 'Changed to'. $newValue;
}
catch(PDOException $e){
if($this->errors === true){
return $this->error($e->getMessage());
}else{
return false;
}
}
}
}