How to convert a multidimensional to a nested array from several tables in PHP?
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
(
开发者_JAVA技巧 [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"}}}}}
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.
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.
加载中,请稍侯......
精彩评论