Push key value in multidimensionnal array in loop php

VitseA · August 18, 2022

Hi,

I 'm trying to build jsonarray with my mysql database.

i'm using dabeng js pluging (orgchart)

My JS script work fine (fake $.mockjax by ajax call and return my php result)

$(function() {

var datasourceJSON;

$.mockjax({

url: '/datasource/',

contentType: 'application/json',

responseTime: 1000,

responseText: datasourceJSON

});

$.ajax({

url: "datasource.php",

dataType: 'JSON',

async: false,

success: function(response) {

datasourceJSON = response;

},

error: function(response, error) { // Info Debuggage si erreur

alert("Erreur : responseText: " + response.responseText);

},

});

$('#chart-container').orgchart({

'data': datasourceJSON,

'nodeContent': 'title',

'nodeId': 'id',

'parentNodeSymbol': '',

});

</script>

My PHP script is not finish ( I'have 4 levels)

<?php

require_once 'pdo.php';

$data_cst = array();

// FIRST LOOP

try{

$sql = "SELECT * FROM main WHERE level = 0";

$result = $pdo->query($sql);

if($result->rowCount() > 0){

while($row = $result->fetch()){

$id = $row['id'];

$data = array('id' => $row['id'], 'name' => $row['name'], 'title' => $row['title'] , 'relationship' => '001');

$data_cst = $data; //start of array

output => {"id":"1","name":"Su Miao,"title":"department manager","relationship":"001"}

// SECOND LOOP

try{

$sql1 = "SELECT * FROM main WHERE parent_id = '$id'";

$result1 = $pdo->query($sql1);

if($result1->rowCount() > 0){

while($row1 = $result1->fetch()){

$id1 = $row1['id'];

$data1 = array('id' => $row1['id'], 'name' => $row1['name'], 'title' => $row1['title'] , 'relationship' => '111');

$data_cst["children"][] = $data1;

it's added children on the first array

output => {"id":"1","name":"Su Miao,"title":"department manager","relationship":"001","children":[

{"id":"2","name":"Tie Hua","title":"senior engineer","relationship":"111"},

{"id":"3","name":"Hei Hei","title":"senior engineer","relationship":"111"}

]

}

// THIRD LOOP

try{

$sql2 = "SELECT * FROM main WHERE parent_id = '$id1'";

$result2 = $pdo->query($sql2);

if($result2->rowCount() > 0){

while($row2 = $result2->fetch()){

$data2 = array('id' => $row2['id'], 'name' => $row2['name'], 'title' => $row2['title'] , 'relationship' => '111');

?????????

That i want to do (addind children on the second array)

output => {"id":"1","name":"Su Miao,"title":"department manager","relationship":"001","children":[

{"id":"2","name":"Tie Hua","title":"senior engineer","relationship":"111" ,"children":[

{"id":"4","name":"Fei Fei","title":"engineer","relationship":"111"},

{"id":"5","name":"Xin Xin","title":"engineer","relationship":"111"}

},

{"id":"3","name":"Hei Hei","title":"senior engineer","relationship":"111", "children":[

{"id":"6","name":"Jei Jei","title":"engineer","relationship":"111"},

{"id":"7","name":"Xun Xun","title":"engineer","relationship":"111"}

},

]

}

// Free result set

unset($result2);

} else {

echo "No records matching your query were found.";

}

} catch(PDOException $e){

die("ERROR: Could not able to execute $sql2. " . $e->getMessage());

}

// Free result set

unset($result1);

} else {

echo "No records matching your query were found.";

}

} catch(PDOException $e){

die("ERROR: Could not able to execute $sql1. " . $e->getMessage());

}

// Free result set

unset($result);

} else {

echo "No records matching your query were found.";

}

} catch(PDOException $e){

die("ERROR: Could not able to execute $sql. " . $e->getMessage());

}

// Close connection

unset($pdo);

echo json_encode($data_cst);

?>

Thanks in advance

DavidAM · August 18, 2022

Please use the code tags - the "<>" in the formatting bar. It makes the code easy to read and work with.

You need to capture the index of the child you add in LOOP 2 and use that index to add the children in LOOP 3

# LOOP TWO
# ...
$data1 = array('id' => $row1['id'], 'name' => $row1['name'], 'title' => $row1['title'] , 'relationship' => '111');
$data_cst["children"][] = $data1; 
$index1 = count($data_cst["children"]) - 1;	// <== ADD THIS LINE

# LOOP THREE
# ...
$data2 = array('id' => $row2['id'], 'name' => $row2['name'], 'title' => $row2['title'] , 'relationship' => '111');

## ????????? // CHANGE THIS TO
$data_cst["children"][$index1][] = $data2;

Running queries inside a loop is generally not a good idea. If you know you are going to go 3 levels (or some other fixed number), you can do this all with a single query and then build your array.

Also, do not select everything from the table. Select only the columns you want/need.

You might try something like this. I have not tested it, but I think it will come close.

 <?php
	require_once 'pdo.php';
	$data_cst = array();
	// FIRST LOOP
	try{
		$sql = 'SELECT L1.id AS L1id, L1.name AS L1name, L1.title AS L1title,
					L2.id AS L2id, L2.name AS L2name, L2.title AS L2title,
					L3.id AS L3id, L3.name AS L3name, L3.title AS L3title
				FROM main AS L1 
					LEFT JOIN main AS L2 ON L2.parent_id = L1.id
					LEFT JOIN main AS L3 ON L3.parent_id = L2.id
				WHERE L1.level = 0';
		$result = $pdo->query($sql);

		if($result->rowCount() > 0){
			while($row = $result->fetch()){
				$L1id = $row['L1id'];
				if ( ! isset($data_cst[$L1id])) {
					// If this is the first row for the L1id, create the data for it
					$data_cst = array('id' => $row['L1id'], 'name' => $row['L1name'], 'title' => $row['L1title'] , 'relationship' => '001', 'children' => []);
				}

				if ( ! empty($row['L2id'])) {
					// We have data for a child of L1id
					$L2id = $row['L2id'];
					if ( ! isset($data_cst[$L1id]['children'][$L2id])) {
						$data_cst[$L1id]['children'][$L2id] = array('id' => $row['L2id'], 'name' => $row['L2name'], 'title' => $row['L2title'] , 'relationship' => '111', 'children' => []);
					}

					if ( ! empty($row['L3id'])) {
					// We have data for a child of L2id
					$L3id = $row['L3id'];
					$data_cst[$L1id]['children'][$L2id]['children'][$L3id] = array('id' => $row['L3id'], 'name' => $row['L3name'], 'title' => $row['L3title'] , 'relationship' => '111');
					}
				}
			}
		} else {
			echo "No records matching your query were found.";
		}
	} catch(PDOException $e){
		die("ERROR: Could not able to execute $sql2. " . $e->getMessage());
	}

	echo json_encode($data_cst);

Notes:

Don't bother closing query results or the database connection. PHP will handle it when the script ends
I indexed the "children" array with the associated ID instead of letting PHP assign something. This made it easier to add the next child or sibling

VitseA · August 19, 2022

Hi David,

Thank you for your time.

In both cases we are not far from the result, except one thing

{
    "id": "1",
    "name": "Su Miao",
    "title": "department manager",
    "relationship": "001",
    "children": [{
            "id": "2",
            "name": "Tie Hua",
            "title": "senior engineer",
            "relationship": "111",
            "children": [],
            "0": {
                "id": "5",
                "name": "Yu Jie",
                "title": "engineer",
                "relationship": "111"
            },
            "1": {
                "id": "6",
                "name": "Yu Lie",
                "title": "engineer",
                "relationship": "111"
            },
        },
        {
            "id": "3",
            "name": "Hei Hei",
            "title": "senior engineer",
            "relationship": "111",
            "children": [],
            "0": {
                "id": "7",
                "name": "Xin Xin",
                "title": "engineer",
                "relationship": "111"
            },
            "1": {
                "id": "8",
                "name": "Wang Wang",
                "title": "engineer",
                "relationship": "111"
            }
        }
    ]
}

My output is wrong.

"children": [],
            "0": {
                "id": "5",
                "name": "Yu Jie",
                "title": "engineer",
                "relationship": "111"
            },

I need this output

"children": [{
                "id": "5",
                "name": "Yu Jie",
                "title": "engineer",
                "relationship": "111"
            },
            {
                "id": "6",
                "name": "Yu Lie",
                "title": "engineer",
                "relationship": "111"
            }],

Thanks in advance David

Barand · August 19, 2022

This calls out for a recursive solution

$res = $pdo->query("SELECT parent
                         , id
                         , name
                         , title
                    FROM main    
                   ");
$emps = $res->fetchAll(PDO::FETCH_GROUP);            // results array grouped by parent

getChildren($emps[0], $emps);
$chart = $emps[0][0];

// view result
echo '<pre>' . print_r($chart, 1) . '</pre>'; 


/**
* recursive function to build hierarchical array
* 
* @param array $kids   array of current children
* @param array $emps   orig array of all employees
*/
function getChildren(&$kids, &$emps) {
    foreach ($kids as $k => &$emp) {
        $children = $emps[$emp['id']] ?? [];
        getChildren($children, $emps);
        $emp['children'] = $children;
    }
}

?>
<!-- DATA ----------------------------------------------------------------------

CREATE TABLE `main` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `title` varchar(45) DEFAULT NULL,
  `parent` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

'1', 'happy', 'Sales Manager', '5'
'2', 'comet', 'Sales Rep', '1'
'3', 'grumpy', 'Accountant', '5'
'4', 'prancer', 'Secretary', '6'
'5', 'bashful', 'General Manager', '0'
'6', 'dancer', 'Accounts clerk', '3'
'7', 'doc', 'Operations Manager', '5'
'8', 'blitzen', 'HR Clerk', '10'
'9', 'dasher', 'Technician', '7'
'10', 'rudolph', 'Personal Assistant', '5'
'11', 'vixen', 'Sales Rep', '1'
'12', 'cupid', 'Sales Assistant', '2'
'13', 'donner', 'Secretarial Assistant', '4'

------------------------------------------------------------------------------->

Results

VitseA · August 19, 2022

Hi Barand,

$children = $emps[$emp['id']] ?? [];

This lines dont work for me .. i can't try it

Regards

Barand · August 19, 2022

If you have old version of PHP you can replace that line with

$children = isset($emps[$emp['id']]) ? $emps[$emp['id']] : [];

VitseA · August 19, 2022

Thanks,

My last error is "Invalid argument supplied for foreach() "

in this line :

 foreach ($kids as $k => &$emp) {

Sorry

Barand · August 19, 2022

I don't get that error so either your code is different or your data is different from mine (or both).

Can you post the code you are using and a dump of your test data if different.

VitseA · August 19, 2022

<?php

require_once 'pdo.php';

$res = $pdo->query("SELECT id, name, title, parent FROM main1");

$emps = $res->fetchAll(PDO::FETCH_GROUP);            

getChildren($emps[0], $emps);

$chart = $emps[0][0];

function getChildren(&$kids, &$emps){
    foreach ($kids as $k => &$emp) {
        $children = isset($emps[$emp['id']]) ? $emps[$emp['id']] : [];
        getChildren($children, $emps);
        $emp['children'] = $children;
    }
}

json_encode($chart);

?>

My js ajax response said :

( ! ) Warning: Invalid argument supplied for foreach() in C:\wamp64\www\orgchart\datasource.php on line 14 ( => foreach ($kids as $k => &$emp) {))

I used exactly that..

CREATE TABLE `main` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `title` varchar(45) DEFAULT NULL,
  `parent` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

'1', 'happy', 'Sales Manager', '5'
'2', 'comet', 'Sales Rep', '1'
'3', 'grumpy', 'Accountant', '5'
'4', 'prancer', 'Secretary', '6'
'5', 'bashful', 'General Manager', '0'
'6', 'dancer', 'Accounts clerk', '3'
'7', 'doc', 'Operations Manager', '5'
'8', 'blitzen', 'HR Clerk', '10'
'9', 'dasher', 'Technician', '7'
'10', 'rudolph', 'Personal Assistant', '5'
'11', 'vixen', 'Sales Rep', '1'
'12', 'cupid', 'Sales Assistant', '2'
'13', 'donner', 'Secretarial Assistant', '4'

Barand · August 19, 2022

Change your

$res = $pdo->query("SELECT id, name, title, parent FROM main1");

to

$res = $pdo->query("SELECT parent, id, name, title  FROM main1");

When using FETCH_GROUP the column you want to group by has to be selected first. You are grouping by id so there is no $emps[0].

VitseA · August 20, 2022

Hi,

I don't get the right format.

I need its exactly

{
      'name': 'Lao Lao',
      'title': 'general manager',
      'children': [
        { 'name': 'Bo Miao', 'title': 'department manager' },
        { 'name': 'Su Miao', 'title': 'department manager',
          'children': [
            { 'name': 'Tie Hua', 'title': 'senior engineer' },
            { 'name': 'Hei Hei', 'title': 'senior engineer',
              'children': [
                { 'name': 'Dan Dan', 'title': 'engineer' }
              ]
            },
            { 'name': 'Pang Pang', 'title': 'senior engineer' }
          ]
        },
        { 'name': 'Hong Miao', 'title': 'department manager' }
      ]
    }

My follwing code work just for 2 levels.. and the format work

<?php

    require_once 'pdo.php';

    $data_cst = array();

    // Attempt select query execution
    try{

        $sql = "SELECT * FROM main WHERE level = 0";  

        $result = $pdo->query($sql);

        if($result->rowCount() > 0){

            while($row = $result->fetch()){

                $id = $row['id'];

                $data = array('id' => $row['id'], 'name' => $row['name'], 'title' => $row['title'] , 'relationship' => '001');
            
                $data_cst = $data;

                // Attempt select query execution
                try{

                    $sql1 = "SELECT * FROM main WHERE parent_id = '$id'";  

                    $result1 = $pdo->query($sql1);

                    if($result->rowCount() > 0){

                        while($row1 = $result1->fetch()){

                            $data1 = array('id' => $row1['id'], 'name' => $row1['name'], 'title' => $row1['title'] , 'relationship' => '111');
                        
                            $data_cst["children"][] = $data1;
                        
                        }

                        // Free result set
                        unset($result1);

                    } else {

                        echo "No records matching your query were found.";

                    }

                } catch(PDOException $e){

                    die("ERROR: Could not able to execute $sql1. " . $e->getMessage());

                }
               
            }

            // Free result set
            unset($result);

        } else {

            echo "No records matching your query were found.";

        }

    } catch(PDOException $e){

        die("ERROR: Could not able to execute $sql. " . $e->getMessage());

    }
    
    // Close connection
    unset($pdo);

    echo json_encode($data_cst);      

?>

How can i build it with PHP recursive fonction for much level

Any idea ?

Barand · August 20, 2022

The only difference I can see is that your employee elements don't have ids. Leave out the id and find another way of matching employee to parent.

When I do that my json output, with very little change to the code, is

{
"name":"bashful","title":"General Manager",
    "children":[
        {"name":"happy","title":"Sales Manager",
            "children":[
                {"name":"comet","title":"Sales Rep",
                    "children":[
                        {"name":"cupid","title":"Sales Assistant"}
                    ]
                },
                {"name":"vixen","title":"Sales Rep"}
            ]
        },
        {"name":"grumpy","title":"Accountant",
            "children":[
                {"name":"dancer","title":"Accounts clerk",
                    "children":[
                        {"name":"prancer","title":"Secretary",
                            "children":[
                                {"name":"donner","title":"Secretarial Assistant"}
                            ]
                        }
                    ]
                }
            ]
        },
        {"name":"doc","title":"Operations Manager",
            "children":[
                {"name":"dasher","title":"Technician"}
            ]
        },
        {"name":"rudolph","title":"Personal Assistant",
            "children":[
                {"name":"blitzen","title":"HR Clerk"}
            ]
        }
    ]
}

[PS]

If that output is as required, let me know and I'll post the code used to produce it.

VitseA · August 21, 2022

Hi,

This is my php code

<?php

require_once 'pdo.php';

$res = $pdo->query("SELECT parent, id, name, title  FROM main1");

$emps = $res->fetchAll(PDO::FETCH_GROUP);            

getChildren($emps[0], $emps);

$chart = $emps[0][0];

function getChildren(&$kids, &$emps){
    foreach ($kids as $k => &$emp) {
        $children = isset($emps[$emp['id']]) ? $emps[$emp['id']] : [];
        getChildren($children, $emps);
        $emp['children'] = $children;
    }
}

echo json_encode($chart);

?>

This is my json response.

{
    "id": "5",
    "0": "5",
    "name": "bashful",
    "1": "bashful",
    "title": "General Manager",
    "2": "General Manager",
    "children": [{
        "id": "1",
        "0": "1",
        "name": "happy",
        "1": "happy",
        "title": "Sales Manager",
        "2": "Sales Manager",
        "children": [{
            "id": "2",
            "0": "2",
            "name": "comet",
            "1": "comet",
            "title": "Sales Rep",
            "2": "Sales Rep",
            "children": [{
                "id": "12",
                "0": "12",
                "name": "cupid",
                "1": "cupid",
                "title": "Sales Assistant",
                "2": "Sales Assistant",
                "children": []
            }]
        }, {
            "id": "11",
            "0": "11",
            "name": "vixen",
            "1": "vixen",
            "title": "Sales Rep",
            "2": "Sales Rep",
            "children": []
        }]
    }, {
        "id": "3",
        "0": "3",
        "name": "grumpy",
        "1": "grumpy",
        "title": "Accountant",
        "2": "Accountant",
        "children": [{
            "id": "6",
            "0": "6",
            "name": "dancer",
            "1": "dancer",
            "title": "Accounts clerk",
            "2": "Accounts clerk",
            "children": [{
                "id": "4",
                "0": "4",
                "name": "prancer",
                "1": "prancer",
                "title": "Secretary",
                "2": "Secretary",
                "children": [{
                    "id": "13",
                    "0": "13",
                    "name": "donner",
                    "1": "donner",
                    "title": "Secretarial Assistant",
                    "2": "Secretarial Assistant",
                    "children": []
                }]
            }]
        }]
    }, {
        "id": "7",
        "0": "7",
        "name": "doc",
        "1": "doc",
        "title": "Operations Manager",
        "2": "Operations Manager",
        "children": [{
            "id": "9",
            "0": "9",
            "name": "dasher",
            "1": "dasher",
            "title": "Technician",
            "2": "Technician",
            "children": []
        }]
    }, {
        "id": "10",
        "0": "10",
        "name": "rudolph",
        "1": "rudolph",
        "title": "Personal Assistant",
        "2": "Personal Assistant",
        "children": [{
            "id": "8",
            "0": "8",
            "name": "blitzen",
            "1": "blitzen",
            "title": "HR Clerk",
            "2": "HR Clerk",
            "children": []
        }]
    }]
}

Regarding the following response I don't know why i have more collumn in my array like "0" : "5" etc

{
    "id": "5",
    "0": "5", <= Dont want
    "name": "bashful",
    "1": "bashful", <= Dont want
    "title": "General Manager",
    "2": "General Manager", <= Dont want

And i dont want empty array


                "id": "12",
                "0": "12",
                "name": "cupid",
                "1": "cupid",
                "title": "Sales Assistant",
                "2": "Sales Assistant",
                "children": [] <= dont want
            }

Thanks in advance for your time.

We are gona make it

Barand · August 21, 2022

Is the sample output I posted (link below) in the correct format?

https://forums.phpfreaks.com/topic/315202-push-key-value-in-multidimensionnal-array-in-loop-php/?do=findComment&comment=1599626

Barand · August 21, 2022

I ran the code you posted and my json output is ...

{"id":5,"name":"bashful","title":"General Manager","children":[{"id":1,"name":"happy","title":"Sales Manager","children":[{"id":2,"name":"comet","title":"Sales Rep","children":[{"id":12,"name":"cupid","title":"Sales Assistant","children":[]}]},{"id":11,"name":"vixen","title":"Sales Rep","children":[]}]},{"id":3,"name":"grumpy","title":"Accountant","children":[{"id":6,"name":"dancer","title":"Accounts clerk","children":[{"id":4,"name":"prancer","title":"Secretary","children":[{"id":13,"name":"donner","title":"Secretarial Assistant","children":[]}]}]}]},{"id":7,"name":"doc","title":"Operations Manager","children":[{"id":9,"name":"dasher","title":"Technician","children":[]}]},{"id":10,"name":"rudolph","title":"Personal Assistant","children":[{"id":8,"name":"blitzen","title":"HR Clerk","children":[]}]}]}

As you see, none of those extra items, but it looks like your problem is your default PD fetch mode. When you connect, set to PDO::FETCH_ASSOC.

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

It looks like yours is set to PDO::FETCH_BOTH which returns an array containing numeric keys and associative (column name) keys, therefore everything is duplicated.

If you don't want to change your default (although I can see no reason why anyone would want FETCH_BOTH) you can change

$emps = $res->fetchAll(PDO::FETCH_GROUP);
 // to
$emps = $res->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);

VitseA · August 23, 2022

Hi,

This is my new JSON array ouout.

{
    "id": "5",
    "name": "bashful",
    "title": "General Manager",
    "children": [{
        "id": "1",
        "name": "happy",
        "title": "Sales Manager",
        "children": [{
            "id": "2",
            "name": "comet",
            "title": "Sales Rep",
            "children": [{
                "id": "12",
                "name": "cupid",
                "title": "Sales Assistant",
                "children": []
            }]
        }, {
            "id": "11",
            "name": "vixen",
            "title": "Sales Rep",
            "children": []
        }]
    }, {
        "id": "3",
        "name": "grumpy",
        "title": "Accountant",
        "children": [{
            "id": "6",
            "name": "dancer",
            "title": "Accounts clerk",
            "children": [{
                "id": "4",
                "name": "prancer",
                "title": "Secretary",
                "children": [{
                    "id": "13",
                    "name": "donner",
                    "title": "Secretarial Assistant",
                    "children": []
                }]
            }]
        }]
    }, {
        "id": "7",
        "name": "doc",
        "title": "Operations Manager",
        "children": [{
            "id": "9",
            "name": "dasher",
            "title": "Technician",
            "children": []
        }]
    }, {
        "id": "10",
        "name": "rudolph",
        "title": "Personal Assistant",
        "children": [{
            "id": "8",
            "name": "blitzen",
            "title": "HR Clerk",
            "children": []
        }]
    }]
}

There are an last issue because sometimes returning empty array in my array

function getChildren(&$kids, &$emps){
    foreach ($kids as $k => &$emp) {
        $children = isset($emps[$emp['id']]) ? $emps[$emp['id']] : []; <= false arg returning empty array
        getChildren($children, $emps);
        $emp['children'] = $children;
    }
}

that's what I want

{
    "id": "12",
    "name": "cupid",
    "title": "Sales Assistant",
    "children": []
}

//TO

{
    "id": "12",
    "name": "cupid",
    "title": "Sales Assistant"
}

Regards

Barand · August 23, 2022

Last time you told me exactly what you want there were no id elements, as in screenshot below

image.png.d7dbfbfd2c412b77fff4a163a8892d19.png

Accordingly, I amended the code to remove them and also removed the empty children arrays (that you still keep moaning about). I posted this and asked you to verify if that was what you wanted. No reply to that request has been received.

Now it appears that id elements are back in the requirements. I also notice that there is no more mention of a relationship element that was in your first post.

As your requirements are changing from day to day, and I cannot get satisfactoty feedback, I am not even going to try to keep up. I guess you need to take it from here on your own.

Sign In

Push key value in multidimensionnal array in loop php

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information