How to convert a multidimensional to a nested array from several tables in PHP ?

Question

Hello,

I want to make a multidimensional array in php. Here is what i have done:

Firstly, i have 3 tables:

entreprise:

enterprise_id         name
1                      e1
2                      e2

site:

site_id              entreprise_id             name
1                       1                     e1_site1
2                       2                     e2_site1
...

salarie:

salarie_id           site_id                   name
1                       1                    e1_site1_salarie1
2                       2                    e2_site1_salarie1
...

I have the following PHP code:

$query = "select * from entreprise";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){

    $query2 = "select * from site where entreprise_id = $row[entreprise_id]";
    $result2 = mysql_query($query2);
    $a2 = array();
    while($row2 = mysql_fetch_assoc($result2)){

        $query3 = "select * from salarie where site_id = $row2[site_id]";
        $result3 = mysql_query($query3);

        while($row3 = mysql_fetch_assoc($result3)){
            $a3[] = array("text"=>$row3[nom]);
        }

        $a2[] = array("text"=>$row2[nom],'children'=>$a3);
    }
        $a1[] = array("text"=>$row[id]." ".$row[nom],'children'=>$a2);
}

But you can see the ouput is mixed. For example, the 'e1_site1_salarie1_nom' is in 'e1_site2', the 'e1_site1_salarie1_nom' is under 'e2_site2'. It's strange.

Array
(
    [0] => Array
        (
            [text] =>  e1
            [children] => Array
                (
                    [0] => Array
                        (
                            [text] => e1_site1
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [text] => e1_site1_salarie1_nom
                                        )

                                )

                        )

                    [1] => Array
                        (
                            [text] => e1_site2
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [text] => e1_site1_salarie1_nom
                                        )

                                    [1] => Array
                                        (
                                            [text] => e1_site2_sa1
                                        )

                                    [2] => Array
                                        (
                                            [text] => e1_site2_sa2
                                        )

                                )

                        )

                )

        )

    [1] => Array
        (
            [text] =>  e2
            [children] => Array
                (
                    [0] => Array
                        (
                            [text] => e2_site2
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [text] => e1_site1_salarie1_nom
                                        )

                                    [1] => Array
                                        (
                                            [text] => e1_site2_sa1
                                        )

                                    [2] => Array
                                        (
                                            [text] => e1_site2_sa2
                                        )

                                    [3] => Array
                                        (
                                            [text] => e2_site2_salarie2_nom
                                        )

                                )

                        )

                )

        )

)

I think it's the problem of my php code. I think i should use more conditional judgement such as if else etc with the following code.

        $a2[] = array("text"=>$row2[nom],'children'=>$a3);

        $a1[] = array("text"=>$row[id]." ".$row[nom],'children'=>$a2);

But I don't know how to change it.

Do you have any clue or suggestions?

Thanks in advance.

Edit：

Following Tatu's suggestion, it works. Thanks Tatu. Now i have one more question. I don't want the [1] => Array in the header of the arrays. How to achieve that?

Array
(
    [1] => Array
        (
            [text] => e1
            [children] => Array
                (
                    [1] => Array
                        (
                            [text] => e1_site1
                            [children] => Array
                                (
                                    [1] => e1_site1_salarie1_nom
                                )

                        )

                    [3] => Array
                        (
                            [text] => e1_site2
                            [children] => Array
                                (
                                    [3] => e1_site2_sa1
                                    [4] => e1_site2_sa2
                                )

                        )

                )

        )

    [2] => Array
        (
            [text] => e2
            [children] => Array
                (
                    [2] => Array
                        (
                            [text] => e2_site2
                            [children] => Array
                                (
                                    [2] => e2_site2_salarie2_nom
                                )

                        )

                )

        )

)

You can see the json code:

{"1":{"text":"e1","children":{"1":{"text":"e1_site1","children":{"1":"e1_site1_salarie1_nom"}},"3":{"text":"e1_site2","children":{"3":"e1_site2_sa1","4":"e1_site2_sa2"}}}},"2":{"text":"e2","children":{"2":{"text":"e2_site2","children":{"2":"e2_site2_salarie2_nom"}}}}} 

I want the result to be like this:

{{"text":"e1","children":{{"text":"e1_site1","children":{"text":"e1_site1_salarie1_nom"}},{"text":"e1_site2","children":{"text":"e1_site2_sa1","text":"e1_site2_sa2"}}}},{"text":"e2","children":{{"text":"e2_site2","children":{"text":"e2_site2_salarie2_nom"}}}}} 

Answer 1

I would roll the entire database interaction into one query and then just use that to populate the array as you require it.  That would be ..

    $query = 'SELECT e.enterprise_id,'.
                    'e.name as enterprise,'.
                    's.site_id,'.
                    's.name as site,'.
                    'sa.salarie_id,'.
                    'sa.name as salarie '.
             'FROM enterprise e,'.
                  'site s,'.
                  'salarie sa '
             'WHERE e.enterprise.id=s.enterprise.id'.
             'AND sa.site_id=s.site_id';
  $result =
mysql_query($query); while($row =
mysql_fetch_assoc($result)){  
$resultArray[$row['enterprise'][$row['site']][$row['salarie_id']]
     = $row['salarie'];

That solution, of course, requires that both enterprise and site name are unique, too -- otherwise, you would have to use the ids as index and only map to the names later.

Answer 2

Don't do nested queries, they are completely unnecessary and only slow things down. Get the values first and combine them later. This might work:

$enterprise_q = mysql_query("select * from entreprise");
$site_q = mysql_query("select * from site");
$salarie_q = mysql_query("select * from salarie");

$result = array();

$enterprise = array();
$site = array();
$salarie = array();

# Now loop through the results 'top down',
# starting from salaries and indexing by site_id.
while($row = mysql_fetch_assoc($salarie_q)) {
    # Initialize variables properly
    if(!is_array($salarie[$row['site_id']])) {
        $salarie[$row['site_id']] = array();
    }

    $salarie[$row['site_id']][$row['salarie_id']] = $row['name'];
}

# Loop through sites, store sites and associated salaries by enterprise_id
while($row = mysql_fetch_assoc($site_q)) {
    if(!is_array($site[$row['enterprise_id']])) {
        $site[$row['enterprise_id']] = array();
    }

    $site[$row['enterprise_id']][$row['site_id']] = array(
        'name' => $row['name'],
        'salarie' => $salarie[$row['site_id']]
    );
}

# Loop through enterprises and gather all site data
while($row = mysql_fetch_assoc($enterprice_q)) {
    if(!is_array($enterprise[$row['enterprise_id']])) {
        $enterprise[$row['enterprise_id']] = array();
    }

    $enterprise[$row['enterprise_id']] = array(
        'name' => $row['name'],
        'sites' => $site[$row['enterprise_id']];
    );
}

This is not an optimal way to handle it, but it is better than your current. This should result in an array something like this:

$enterprise = array(
    1 => array(
        'name' => 'e1',
        'sites' => array(
            1 => array(
                'name' => 'e1_site1',
                'salarie' => array(
                    1 => 'e1_site1_salarie1',
                    1 => 'e1_site1_salarie2',
                    1 => 'e1_site1_salarie3'
                )
            ),
            2 => array(
                'name' => 'e1_site2',
                'salarie' => array(
                    1 => 'e1_site2_salarie1',
                    1 => 'e1_site2_salarie2'
                )
            )
        )
    ),
    2 => array(
        'name' => 'e2',
        'sites' => array(
            1 => array(
                'name' => 'e2_site1',
                'salarie' => array(
                    1 => 'e2_site1_salarie1'
                )
            )
        )
    )
)

Which should be what you're looking for. I haven't tested this so don't expect it to work on first try, but this should at least give you some ideas.