updating mysql json array using php

Question

as the title says, I made a PHP method to update a JSON object from MySQL using a defined key-value and new-value basically the function fetches the JSON column from the database and then looping throw each object in the array and compare it to the item and if the key-value matches the item the key-value will be replaced with the new value. as I think the problem is in the newArray because the function inserts the same old array without any changes!

<?
    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;
                }
            }
        }
    }
?>

method call($productqty and $productPrice are the key-value getting from 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);

}
?>

$newArray = json_encode( $decoded ); - at this stage you have not modified $decoded — Professor Abronsius
– Professor Abronsius, Commented Jul 5, 2020 at 12:49
@ProfessorAbronsius ok, what should I use to store modification thanks for your comment — MrObscure
– MrObscure, Commented Jul 5, 2020 at 13:01

Professor Abronsius · Accepted Answer · 2020-07-05 14:26:43Z

1

I can't be sure if the following is correct as I have no way currently to test the JSON datatype within mysql nor a suitable table with json data so it might be riddled with mistooks.

<?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 */
?>

edited Jul 5, 2020 at 14:26

answered Jul 5, 2020 at 13:12

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MrObscure Over a year ago

thanks a lot but there is no changes, I did understand the method and how it works but I couldn't figure Why the array stills the same, I thought ProductPrice was the problem, but I defined an integer as a new value instead of ProductPrice but the Array is the same nothing changes

Professor Abronsius Over a year ago

as I say I was not sure... have you tried printing the variable in the loop to see the values? If I had suitable data I'd have a better idea. Essentially though you need to modify the entire JSON variable that you pulled from the db initially - only the relevant bit of course before updating the db record

MrObscure Over a year ago

,what do you refer for a suitable data? you said that I want to modify the entire json just FYI want to edit one object

Professor Abronsius Over a year ago

@MrObscure - made a slight change after doing some testing. I modified the way the $decoded variable is updated by using an index. This worked on my test data in a std text column in db - my data will be nothing like yours though I imagine

Collectives™ on Stack Overflow

updating mysql json array using php

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related