正如标题所说的，我制作了一个PHP方法，使用定义的key-value和new-value从MySQL更新JSON对象。基本上，函数从数据库中获取JSON列，然后循环抛出数组中的每个对象，并将其与item进行比较，如果key-value匹配item，则将用new-value替换key-value。 因为我认为问题出在newarray中，因为该函数插入了相同的旧数组，而没有做任何更改！
<?
public function AlterJSON($billingID,$item,$key,$newValue){
if($this->connected === true){
try{
$ColumnJ = $this->connection->prepare("SELECT `items` FROM `bills` WHERE id=:id");
$ColumnJ->bindParam(":id",$billingID);
$ColumnJ->execute();
$fetched = $ColumnJ->fetchColumn();
$decoded = json_decode($fetched);
foreach($decoded as $product){
if($product->$key == $item){
$product->$key = $newValue;
$newArray = json_encode($decoded);
$alterSQL = $this->connection->prepare('UPDATE `bills` SET items=:newArray WHERE id=:id');
$alterSQL->bindParam(':newArray',$newArray);
$alterSQL->bindParam(':id',$billingID);
$alterSQL->execute();
}
}
}
catch(PDOException $e){
if($this->errors === true){
return $this->error($e->getMessage());
}else{
return false;
}
}
}
}
?>
方法调用($ProductQTY和$ProductPrice是从json获取的键值）
<?php
require("Core.php");
// Calling DbConnect Object //
$dbConnection = new dbConnect('127.0.0.1','root','0928065698ko','project-db');
// Billing $_PSOT //
$ProductQty = $_POST['qty-input'];
$ProductPrice = $_POST['price-input'];
$BillingTotal = $_POST['total-input'];
$BillID = $_POST['Billid'];
// Billing Requset //
if(isset($ProductPrice) && isset($ProductQty) && isset($BillingTotal)){
$dbConnection->AlterJSON($BillID,$ProductPrice,'price',$ProductPrice);
}
?>
我不能确定以下内容是否正确，因为我目前没有办法在mysql中测试JSON数据类型，也没有一个合适的带有JSON数据的表，所以它可能会充满错误。
<?php
/*
It is better to test that these POST variables are set than try to assign
as variables later not knowing if they are or are not set. `isset` will
accept multiple arguments.
*/
if( isset(
$_POST['qty-input'],
$_POST['price-input'],
$_POST['total-input'],
$_POST['Billid']
) ){
# no need to include this if the variables are not set
require('Core.php');
$dbConnection = new dbConnect('127.0.0.1','root','0928065698ko','project-db');
$ProductQty = $_POST['qty-input']; # this does not get used
$ProductPrice = $_POST['price-input'];
$BillingTotal = $_POST['total-input']; # this does not get used
$BillID = $_POST['Billid'];
$dbConnection->AlterJSON( $BillID, $ProductPrice, 'price', $ProductPrice );
}
?>
<?php
/* previous methods */
public function AlterJSON( $bid=false, $item=false, $key=false, $value=false ){
try{
if( $bid && $item && $key && $value && $this->connected === true ){
$stmt=$this->connection->prepare('select `items` from `bills` where `id`=:id');
$stmt->execute( [ ':id' => $bid ] );
$fetched = $stmt->fetchColumn();
$decoded = json_decode( $fetched );
$stmt=$this->connection->prepare('update `bills` set `items`=:json where id=:id');
foreach( $decoded as $index => $product ){
if( $product->$key == $item ){
$decoded[ $index ]->$key = $value;
$json = json_encode( $decoded );
$stmt->execute([
':json' => $json,
':id' => $bid
]);
}
}
return true;
}
return false;
} catch( PDOException $e ){
return $this->errors === true ? $this->error( $e->getMessage() ) : false;
}
}
/* more methods */
?>
