Multidimensional nested array from mysql table
Atomic
Hello All
I'm little bit stuck with this problem
I try to build multidimensional array with data from mysql table
my mySQL table contain fields ( this is sample data )
id name parent_id link
1 item 1 0 linkpage1.html
2 item 2 0 linkpage2.html
3 item 3 0 linkpage3.html
4 item 4 0 linkpage4.html
5 item 5 0 linkpage5.html
6 item 6 1 linkpage6.html
7 item 7 1 linkpage7.html
8 item 8 4 linkpage8.html
9 item 9 4 linkpage9.html
10 item 10 1 linkpage10.html
11 item 11 4 linkpage11.html
12 item 12 9 linkpage12.html
13 item 13 9 linkpage13.html
I try to build multidimensional array with parent/children relations with
query
SELECT * FROM category ORDER BY id
i try to figure out myself but only what i get is first 2 levels
is it possible to create array with parent/children with all "leafs" since i
plan to use such array to build UL/LI list
Thanks in advanced
Atomski
__________ ESET NOD32 Antivirus informacija, verzija baze podataka virusnih potpisa 5413 (20100831) __________
poruka je provjerena programom ESET NOD32 Antivirus.
Marious Barrier
> i try to figure out myself but only what i get is first 2 levels
>
> is it possible to create array with parent/children with all "leafs" since i
> plan to use such array to build UL/LI list
Do this.-
// you can copy and paste this code as-is and test it.
// A single query to get all the entries in a single array. $allitems
// Would end up being something like this
$allitems = array(
1 => array( 'id' => 1, 'name' => 'item1', 'par' => 0),
2 => array( 'id' => 2, 'name' => 'item2', 'par' => 1),
3 => array( 'id' => 3, 'name' => 'item3', 'par' => 0),
4 => array( 'id' => 4, 'name' => 'item4', 'par' => 1),
5 => array( 'id' => 5, 'name' => 'item5', 'par' => 2),
6 => array( 'id' => 6, 'name' => 'item6', 'par' => 1),
);
// A “while”, to link them using references.
while (list($z, $o) = each($allitems))
if ($o['par'] && isset($allitems[$o['par']]))
$allitems[$o['par']]['child'][] =& $allitems[$z];
reset($allitems);
// And then unset all the childs. leaving the items with par = 0
while (list($z, $o) = each($allitems))
if ($o['par']) unset($allitems[$z]);
// see what it results and if it is what you want.
print_r($allitems);
Erwin Moller
Nice approach Marious, but there is one small hick-up.
The success of your routine depends on the order of feeded source (the
$allitems array in your example).
If it encounters parentid's that weren't used earlier, it will fail.
Feed it this to test:
$allitems = array(
1 => array( 'id' => 1, 'name' => 'item1', 'par' => 0),
2 => array( 'id' => 2, 'name' => 'item2', 'par' => 1),
3 => array( 'id' => 3, 'name' => 'item3', 'par' => 0),
4 => array( 'id' => 4, 'name' => 'item4', 'par' => 1),
5 => array( 'id' => 5, 'name' => 'item5', 'par' => 2),
6 => array( 'id' => 6, 'name' => 'item6', 'par' => 7),
7 => array( 'id' => 10, 'name' => 'item10', 'par' => 5),
8 => array( 'id' => 15, 'name' => 'item15', 'par' => 7),
9 => array( 'id' => 44, 'name' => 'item44', 'par' => 2)
);
And that cannot always be solved with a simple ORDER BY clause in the
underlying query.
I had that problem once (complex nested navigation structure), and
approached it in a different way.
(I'll try to dig my code up, but I am not 100% sure where I left it.)
Regards,
Erwin Moller
--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare
Marious Barrier
Nop nop, you did a mistake there... you just changed the 'id' key which
is actually used by nothing.
7 for instance.
7 => array('id' => 10 ...
should be
10 => array('id' => 10 ...
Erwin Moller
Hi Marious,
I also changed the 'par' key to 7 in that line.
(Since 7 doesn't exist at that time.)
But on second inspection I see that your script handles that just right.
Not sure what I was thinking/doing yesterday when I tested your code,
but it surely wasn't something intelligent. I stand corrected.
I expect I was confused by the fact you used the array index instead of
the 'id' field as coming from the database, which is a more realistic
field to use, so I blindly assumed that you did.
Regards,
Erwin Moller
>
>>
>> And that cannot always be solved with a simple ORDER BY clause in the
>> underlying query.
>>
>> I had that problem once (complex nested navigation structure), and
>> approached it in a different way.
>> (I'll try to dig my code up, but I am not 100% sure where I left it.)
Marious Barrier
Don’t worry, the array will be made dynamically so that won’t happen for
sure.