PHP-Sandbox - PHP script help - support array in array

[sam8881998]

Dear all,

Please help.
I am trying to make the script to read the array in array json file but unsuccessfull and update to MySQL table. Please advise where is broken:

Millions thanks.

---

json file

{
"details":[
{
"number": "2",
"name": "Name 2",
"items":[
{
"title": "manager"
},
{
"title": "Student"
}
]
},
{
"number": "1000",
"name": "Name 1000",
"items":[
{
"title": "department head"
},
{
"title": "Supervisor"
}
]
}
]
}

---

PHP Script:

getConnection('ConnectMysqltrackandtraceDB'); $payload = $request->getPayload(); $id = $request->get('id'); // returns a path or query parameter $arguments = $request->getArguments(); // returns all available arguments as array $user = $context ->getUser(); if ($user !== null) { // User is authenticated, you can access user properties $userId = $user->getname(); // Access more user properties as needed } else { // User is not authenticated // Handle the case when no user is authenticated } if ($payload->get('details') != null) { foreach ($payload->get('details') as $item) { $connection->insert('tt_db.test_items', [ 'number' => $item->get('number'), 'name' => $item->get('name'), 'user' => $userId, ]); foreach ($payload->get('items') as $title) { $connection->insert('tt_db.test_items', [ 'title' => $title->get('number'), ]); }} } return $response->build(200, [], [ 'id' => $id, 'uriFragments' => $arguments, 'payload' => $payload, ]); -------

[chriskapp]

Hi @sam8881998 could you format the PHP code by adding ```php at the start and ``` at the end of your code block

[sam8881998]

<?php

$connection = $connector->getConnection('ConnectMysqltrackandtraceDB');
$payload = $request->getPayload();
$id = $request->get('id'); // returns a path or query parameter
$arguments = $request->getArguments(); // returns all available arguments as array


$user = $context ->getUser();

if ($user !== null) {
    // User is authenticated, you can access user properties
    $userId = $user->getname();
 
    // Access more user properties as needed
} else {
    // User is not authenticated
    // Handle the case when no user is authenticated
}

if ($payload->get('details') != null) {

	foreach ($payload->get('details') as $item) {
	  $connection->insert('tt_db.test_items', [
	    'number' => $item->get('number'),
	    'name' => $item->get('name'),
	    'user' => $userId,
	  ]);
    foreach ($payload->get('items') as $title) {
	  $connection->insert('tt_db.test_items', [
	    'title' => $title->get('number'),
	  ]);      
	}}
}
return $response->build(200, [], [
    'id' => $id,
    'uriFragments' => $arguments,
    'payload' => $payload,
]);

[chriskapp]

so I have adjusted the action a little bit but it should work now s.

<?php

$connection = $connector->getConnection('ConnectMysqltrackandtraceDB');
$payload = $request->getPayload();
$id = $request->get('id'); // returns a path or query parameter
$arguments = $request->getArguments(); // returns all available arguments as array

$user = $context ->getUser();

if (!$user->isAnonymous()) {
    // User is authenticated, you can access user properties
    $userId = $user->getName();

    // Access more user properties as needed
} else {
    // User is not authenticated
    // Handle the case when no user is authenticated
    $userId = 'unknown';
}

$details = $payload->get('details');
if (is_array($details)) {
    foreach ($details as $detail) {
        $connection->insert('test_items', [
            'number' => $detail->get('number'),
            'name' => $detail->get('name'),
            'user' => $userId,
        ]);

        $items = $detail->get('items');
        if (is_array($items)) {
            foreach ($items as $item) {
                $connection->insert('test_items', [
                    'title' => $item->get('number'),
                ]);
            }
        }
    }
}

return $response->build(200, [], [
    'id' => $id,
    'uriFragments' => $arguments,
    'payload' => $payload,
]);

[sam8881998]

Dear Chris,

Thank you so much for your reply.
I tested and if the json file is changed to below, and I need to store the record like:

Table structure: Autonumber (managed by database automatically), Number, Name, Title

I would like to have the below Stored Table Data result:
10001, 50,Name 50, Null
10002, 2,Name 2, manager
10003, 2,Name 2, Student
10004, 1000,Name 1000, department head
10005, 1000,Name 1000, Supervisor

I changed a little bit coding (I was adding the indicator but still not working and below is the latest version) but get two Null records for name 2 and Name 1000.
Would you please provide guidance how to get the above expected result and thanks in advance.

Samuel

Json file:
{
"details":[
{
"number": "50",
"name" : "Name 50"
},
{
"number": "2",
"name": "Name 2",
"items":[
{
"title": "manager"
},
{
"title": "Student"
}
]
},

{
  "number": "1000",
  "name": "Name 1000",
  "items":[
    {
        "title": "department head"
    },
    {
        "title": "Supervisor"
    }
    ]
}    
]

}

PHP Coding I changed:

<?php

$connection = $connector->getConnection('ConnectMysqltrackandtraceDB');
$payload = $request->getPayload();
$id = $request->get('id'); // returns a path or query parameter
$arguments = $request->getArguments(); // returns all available arguments as array

$user = $context ->getUser();

if (!$user->isAnonymous()) {
    // User is authenticated, you can access user properties
    $userId = $user->getName();

    // Access more user properties as needed
} else {
    // User is not authenticated
    // Handle the case when no user is authenticated
    $userId = 'unknown';
}

$details = $payload->get('details');
if (is_array($details)) {
    foreach ($details as $detail) {
        $connection->insert('test_items', [
            'number' => $detail->get('number'),
            'name' => $detail->get('name'),
        ]);

        $items = $detail->get('items');
        if (is_array($items)) {
            foreach ($items as $item) {
                $connection->insert('test_items', [
                    'number' => $detail->get('number'),
                    'name' => $detail->get('name'),
                    'title' => $item->get('title'),
                ]);
            }
        }
    }
}

return $response->build(200, [], [
    'id' => $id,
    'uriFragments' => $arguments,
    'payload' => $payload,
]);