更新存储在数据库中的JSON数据

Question

I have JSON data that is stored in a variable which is a set of permissions for a particular user. The JSON data however, is not stored in a file, it's just in a database column.

I've tried to look for a method of updating a permission (which is either true or false) but I've had no luck thus far. At the moment, I have something to the effect of;

The raw JSON data...

{
    "permissions": {
        "permission_1": true,
        "permission_2": false
    }
}

Getting it out of the database...

$permissions = json_decode($data, true);

How do I (using PHP) update the JSON data to say, update permission 2 to true? I'm using json_decode() when it comes out of the database and putting it into an array but that's only for using it. After that I'm lost as to how to update the value.

Answer 1

In order:

• Extract column data from the database.

  "SELECT column FROM table WHERE id = 1 LIMIT 1"       
  

• JSON_decode data into a set of PHP values/objects/arrays/whatever.

  $array = json_decode($OutputRow['column'],true);
  

• Update the value(s) you need to update.

  $array['permissions']['permission2'] = true;
  

• Recompile into a JSON string using JSON_encode.

  $data = json_encode($array);
  

• Update the database (SQL?) with the new JSON_encoded string.

  "UPDATE table SET column = :data WHERE id = 1 LIMIT 1"
  

My examples use PDO and MySQL by reference but does not go into any details about database interaction as you've not given any details as to how you're reaching your database. It's only a ballpark rough example of how to do the points listed.