PHP Mysql Search and Update in nested JSON column

Question

I have Table by name "centers" column by name "teams" :

Schema (MySQL v5.7)

DROP TABLE IF EXISTS `Centers`;

CREATE TABLE IF NOT EXISTS `Centers` (
`id` int(11) NOT NULL ,
`name` VARCHAR(255)  NOT NULL ,
`teams` JSON NOT NULL 
);

INSERT INTO `Centers`
  (`id`, `name`, `teams`)
VALUES
  ('1','Texas','{"teams0":{"name":"Alex","training":"worker","agenda":"https:\/\/example.com\/booking\/alex","terminland_days":"0"},"teams1":{"name":"thomas","training":"worker","agenda":"https:\/\/example.com\/booking\/thomas","terminland_days":"0"}}'),
  ('2','California','{"teams0":{"name":"Dean","training":"worker","agenda":"https:\/\/example.com\/booking\/dean","terminland_days":"0"},"teams1":{"name":"Rose","training":"worker","agenda":"https:\/\/example.com\/booking\/rose","terminland_days":"0"}}');

Query #1

SELECT VERSION();

VERSION()
5.7.22

Query #2

SELECT `id`, `Name`, `teams`
FROM `Centers`;

id	Name	teams
1	Texas	{"teams0": {"name": "Alex", "agenda": "https://example.com/booking/alex", "training": "worker", "terminland_days": "0"}, "teams1": {"name": "thomas", "agenda": "https://example.com/booking/thomas", "training": "worker", "terminland_days": "0"}}
2	California	{"teams0": {"name": "Dean", "agenda": "https://example.com/booking/dean", "training": "worker", "terminland_days": "0"}, "teams1": {"name": "Rose", "agenda": "https://example.com/booking/rose", "training": "worker", "terminland_days": "0"}}

View on DB Fiddle

also I have a Function for get "agenda" value then return "terminland_days" :

function get_single_agents_info($url)
{
$arr = explode('/', $url);
$center_slug = $arr[count($arr) - 2];
$agent_slug = $arr[count($arr) - 1];

$data = array("key" => "11111", "request" => "get_agents_info","office_seo_url" =>$center_slug,"agent_seo_url"=>$agent_slug);
$json = json_encode($data);
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://example.com/booking');
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json'));
curl_setopt($ch, CURLOPT_USERAGENT, 'Atlantomed API');
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $json);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$answer = curl_exec($ch);
curl_close($ch);

$json_answer = json_decode($answer);

if ($json_answer->status == 'OK') {
    foreach ($json_answer->agents as $agent) {
        foreach ($agent->offices as $office) {
            return $office;
            /* print_r ($office);
            result =====>   [next_appointments] => Array ( [0] => stdClass Object ( [remaining_days] => 3 ))   */
        }
    }
}
return null;
}

so in final, I must get "Agenda url" from database and send it to Function, then updating teams column by function return object.

this my wrong php code:

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sql";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = 'SELECT * FROM Centers WHERE teams LIKE "%example.com%"'; /* this wrong */
/* i need select `agenda` in json column */  

$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {

    $url = $row['agenda'];
    $agents_data = get_single_agents_info($url);
    $next_appointments = !empty($agents_data->next_appointments)  ? $agents_data->next_appointments[0] : [];

if (!empty($next_appointments)) {
        $terminland_date = $next_appointments->date;
        $terminland_days = $next_appointments->remaining_days;
        $updatesql = "UPDATE `Centers` SET `terminland_days` = '" . $terminland_days . "' WHERE `agenda` = '" . $row['agenda'] . "'";
        $conn->query($updatesql);
   }
  }
 }

Question: how can update JSON Column teams in mysql ?

Update:

php code:

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sql";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = 'SELECT *, 
   CAST(object_name AS CHAR) object_name,
   CAST(JSON_EXTRACT(teams, CONCAT("$.", object_name, ".name")) AS CHAR) name, 
    CAST(JSON_EXTRACT(teams, CONCAT("$.", object_name, ".agenda")) AS CHAR) agenda, 
   CAST(JSON_EXTRACT(teams, CONCAT("$.", object_name, ".terminland_days")) AS CHAR) terminland_days
   
FROM Centers
JOIN ( SELECT id, JSON_EXTRACT(JSON_KEYS(teams), CONCAT("$[", n, "]")) object_name
   FROM Centers
   JOIN ( SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
          SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) numbers
        ON n < JSON_LENGTH(JSON_KEYS(teams))) paths USING (id)';

$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {

    $url = $row['agenda'];
    $agents_data = get_single_agents_info($url);
    $next_appointments = !empty($agents_data->next_appointments)  ? $agents_data->next_appointments[0] : [];

if (!empty($next_appointments)) {
        $terminland_date = $next_appointments->date;
        $terminland_days = $next_appointments->remaining_days;
  /* here */      
 $updatesql = "UPDATE Centers
 SET teams = JSON_SET(teams, CONCAT('$.', @object_name, '.terminland_days'), @new_value)
 WHERE agenda = @id;"; 
        $conn->query($updatesql);
   }
  }
 }

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Feb 01 '22 at 12:45
Does each `teams` JSON contains strictly 2 objects `'teams0'` and `'teams1'`? Does the structure of object is definite? — Akina, Feb 01 '22 at 12:52
It's still not clear which technology you are struggling with. Is it PHP, mysqli or MySQL? It sounds to me like you are asking how to update it using the SQL query. I don't know what this has to do with mysqli or PHP. — Dharman, Feb 01 '22 at 12:54
@Akina each `teams` JSON contains multiple objects like `teams0` , `teams1` , `teams2` , ... — Amin, Feb 01 '22 at 12:54
What is max. amount of objects? Does the names are always `teamsN` where N are adjacent numbers from 0 to X? — Akina, Feb 01 '22 at 12:57
You are suffering from unnormalized database. If you want to update fields inside the JSON then why isn't this a separate table in MySQL? There should be a separate table with a relation to this one — Dharman, Feb 01 '22 at 12:58

Akina · Accepted Answer · 2022-02-01T15:50:55.520

1

I must get "Agenda url" from database and send it to Function

SELECT id, 
       name, 
       CAST(object_name AS CHAR) object_name,
       CAST(JSON_EXTRACT(teams, CONCAT('$.', object_name, '.name')) AS CHAR) name, 
       CAST(JSON_EXTRACT(teams, CONCAT('$.', object_name, '.agenda')) AS CHAR) agenda, 
       CAST(JSON_EXTRACT(teams, CONCAT('$.', object_name, '.training')) AS CHAR) training, 
       CAST(JSON_EXTRACT(teams, CONCAT('$.', object_name, '.terminland_days')) AS CHAR) terminland_days
FROM Centers
JOIN ( SELECT id, JSON_EXTRACT(JSON_KEYS(teams), CONCAT('$[', n, ']')) object_name
       FROM Centers
       JOIN ( SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
              SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) numbers
            ON n < JSON_LENGTH(JSON_KEYS(teams))) paths USING (id)

fiddle with steps description.

Obtained values should be provided into your function.

then updating teams column by function return object.

Then use id, object_name and the value returned by the function and update JSON value using JSON_SET() function.

PS. CAST(.. AS CHAR) is added because the returned value datatype is binary string, and it is displayes in HEX codes. Maybe in your code they should be excess - remove them in this case. Also you may add JSON_UNQUOTE() if needed.

can you help about JSON_SET() – Amin Ebrahimzadeh

UPDATE Centers
SET teams = JSON_SET(teams, CONCAT('$.', @object_name, '.terminland_days'), @new_value)
WHERE id = @id;

where:

@id - the value in id column of the row to be updated
@object_name the name of object in this row
@new_value - the value to be inserted (returned by function)

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=42d1b79da5e01a737feba8d9661fbd1c

edited Feb 01 '22 at 15:50

answered Feb 01 '22 at 13:17

Akina

39,301
5
14
25

wow, Thank you @Akina . can you help about `JSON_SET()` – Amin Feb 01 '22 at 14:14
thank you for help me, but you used STATIC var for `@object_name` , maybe i do not underestand as clear – Amin Feb 01 '22 at 16:05
Mr @Akina i updated question post, please see latest line in php code box. where `$updatesql = "UPDATE Centers SET teams = JSON_SET(teams, CONCAT('$.', @object_name, '.terminland_days'), @new_value) WHERE agenda = @id;";` – Amin Feb 01 '22 at 16:36
1

@AminEbrahimzadeh *you used STATIC var for @object_name* See above: Obtained values should be provided into your function. ... Then use id, object_name and the value returned by the function and update JSON value using JSON_SET() function. I.e. you use the values obtained in 1st query as parameter values in 2nd. – Akina Feb 01 '22 at 16:49
i sent sql query to Function `$agents_data = get_single_agents_info($url);` and get value.(until here is ok) - but i do not underestand `@object_name` , how get this and use in Update function? can you send to me your email? i want send to you ftp access. so thank you – Amin Feb 01 '22 at 17:02
1

@AminEbrahimzadeh Where you have taken the value for `$url` which is sent to the function? From 1st query. But the same query returns more columns... some of those columns values should be used in 2nd query in addition to the value calculated by your functoin. – Akina Feb 01 '22 at 17:08
yes i know, but i have problem to underestand `@object_name` how get this?(i means like `teams0` or `temas1` in Update function, also how set `where` ? again thanks – Amin Feb 01 '22 at 17:46
@AminEbrahimzadeh ```$updatesql = "UPDATE Centers SET teams = JSON_SET(teams, '$.$object_name.terminland_days'), '$new_value') WHERE agenda = $id;";``` – Akina Feb 01 '22 at 18:21
hmm, i get it, must firstly make Var for `object_name` like this `$objectvar = $row['object_name'];` - it is ok. but still problem on `where` , how can clause `where` inside json column? i want `agenda` in `where` like this (i get var $url as well) => `... where agenda = $url;` – Amin Feb 02 '22 at 07:17
1

@Amin Not needed, your row to be updated is completely defined by `WHERE id = $id`, and object within the value to be updated is defined by its path `'$.$object_name.terminland_days'`. – Akina Feb 02 '22 at 07:21
thank you, now function run without any error, but not make update column! do you have any idea? – Amin Feb 02 '22 at 07:54
StackOverflow-driven development at its best. The OP "cannot" change the DB structure so someone else has to write a code to deal with it. – Your Common Sense Feb 02 '22 at 08:55
@YourCommonSense i do not understand what you said! – Amin Feb 02 '22 at 09:21
@Akina i found. so thank you – Amin Feb 02 '22 at 09:49
@Akina I have problem in PHP while. when in column exist multiple `teams` loop do wrong. please see image. https://imgur.com/a/fcoHE9m – Amin Feb 09 '22 at 10:45
@Amin PHP and HTML is not my field of interest. – Akina Feb 09 '22 at 11:53

PHP Mysql Search and Update in nested JSON column

1 Answers1