binary tree, long && difficult

[sathia]

Hi, I have this table:
 
CREATE TABLE `osservatorio` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `testo` varchar(255) NOT NULL default '',
  `parent` int(11) default NULL,
  `allegato` varchar(255) default NULL,
  `descrizione` varchar(255) default NULL,
  `online` enum('true','fa lse') NOT NULL default 'true',
  PRIMARY KEY  (`id`),
  KEY `parent` (`parent`),
  KEY `online` (`online`)
) TYPE=MyISAM AUTO_INCREMENT= 13 ;

#
# Dumping data for table `osservatorio`
#

INSERT INTO `osservatorio` VALUES (1, 'Monitoraggio Ambientale', 0, NULL,
'', 'true');
INSERT INTO `osservatorio` VALUES (2, 'Lotto 1.1', 1, NULL, '', 'true');
INSERT INTO `osservatorio` VALUES (3, 'Lotto 1.2', 1, NULL, '', 'true');
INSERT INTO `osservatorio` VALUES (4, 'Lotto 1.2.1', 3, NULL, '', 'true');
INSERT INTO `osservatorio` VALUES (5, 'Lotto 1.2.2', 3, NULL, '', 'true');
INSERT INTO `osservatorio` VALUES (6, 'Lotto 1.2.2.3', 3, NULL, '', 'true');
INSERT INTO `osservatorio` VALUES (7, 'Programma Attività', 5, NULL, '',
'true');
INSERT INTO `osservatorio` VALUES (8, 'Programma Attività', 6, NULL, '',
'true');
INSERT INTO `osservatorio` VALUES (9, 'Relazione Rumore Corso D\'Opera', 6,
NULL, '', 'true');
INSERT INTO `osservatorio` VALUES (10, 'Lotto 1.3', 1, NULL, '', 'true');
INSERT INTO `osservatorio` VALUES (11, 'Lotto 1.4', 1, NULL, '', 'true');
INSERT INTO `osservatorio` VALUES (12, 'Relazione Metodologica', 1, NULL,
'', 'true');

with a simple "while" i can get this array

(please look at the keys)

Array
(
    [0] => Array
        (
            [1] => Monitoraggio Ambientale
        )

    [1] => Array
        (
            [2] => Lotto 1.1
            [3] => Lotto 1.2
            [10] => Lotto 1.3
            [11] => Lotto 1.4
            [12] => Relazione Metodologica
        )

    [3] => Array
        (
            [4] => Lotto 1.2.1
            [5] => Lotto 1.2.2
            [6] => Lotto 1.2.2.3
        )

    [5] => Array
        (
            [7] => Programma Attività
        )

    [6] => Array
        (
            [8] => Programma Attività
            [9] => Relazione Rumore Corso D'Opera
        )

)



I'd, like to have every element of the array in a hierarchical way.
i still can't get any solution for this.


any help will be appreciated

--
Sat_

[Pedro Graca]

Re: binary tree, long && difficult

sathia wrote:[color=blue]
> I'd, like to have every element of the array in a hierarchical way.
> i still can't get any solution for this.
>
>
> any help will be appreciated[/color]

I think you need a recursive solution:

<?php
function recurse_db(&$ar r, $parent) {
$index = 0;
$sql = "select id, testo, parent, allegato, descrizione, online "
. "from test.osservator io "
. "where parent = $parent";
$recs = mysql_query($sq l) or die($sql . ' ==> ' . mysql_error());

while ($rec = mysql_fetch_row ($recs)) {
$arr[$index] = $rec;
recurse_db($arr[$index]['children'], $rec[0]);
++$index;
}

mysql_free_resu lt($recs);
}

mysql_connect() or die('==>' . mysql_error());
recurse_db($arr , 0); # <== start with the records for which the parent is 0
mysql_close();
print_r($arr);
?>


Happy Coding :-)
--
USENET would be a better place if everybody read: | to mail me: simply |
http://www.catb.org/~esr/faqs/smart-questions.html | "reply" to this post, |
http://www.netmeister.org/news/learn2quote2.html | *NO* MIME, plain text |
http://www.expita.com/nomime.html | and *NO* attachments. |

[sathia]

Re: binary tree, long &amp;&amp; difficult

Thank you very much, i never tried to use several queries.
on my own i thought it would be possible just using one query.

you made my day :)

lovely solution.

Sat_

[Pedro Graca]

Re: binary tree, long &amp;&amp; difficult

sathia wrote:[color=blue]
> Thank you very much, i never tried to use several queries.
> on my own i thought it would be possible just using one query.[/color]

It might be better :-)

Do one query and select all the rows you're interested in; put them in a
simple array, and recurse the array instead of doing a lot of
mysql_query()

I guess that as your database grows larger my last solution will grow
unacceptably slow.

[color=blue]
> lovely solution.[/color]

Thank you!
--
USENET would be a better place if everybody read: | to mail me: simply |
http://www.catb.org/~esr/faqs/smart-questions.html | "reply" to this post, |
http://www.netmeister.org/news/learn2quote2.html | *NO* MIME, plain text |
http://www.expita.com/nomime.html | and *NO* attachments. |

[Sathia Musso]

Re: binary tree, long &amp;&amp; difficult

Pedro Graca wrote:
[color=blue]
> sathia wrote:[color=green]
>> Thank you very much, i never tried to use several queries.
>> on my own i thought it would be possible just using one query.[/color]
>
> It might be better :-)
>
> Do one query and select all the rows you're interested in; put them in a
> simple array, and recurse the array instead of doing a lot of
> mysql_query()[/color]

I'm gonna try this this evening. now i'm on something else (even more
boring).

Tomorrow I'll let you know.

regards

--
Sat_

[Pedro Graca]

Re: binary tree, long &amp;&amp; difficult

Sathia Musso wrote:[color=blue]
> I'm gonna try this this evening. now i'm on something else (even more
> boring).
>
> Tomorrow I'll let you know.[/color]

Well ... I couldn't stop thinking about how to do it :)



<?php
error_reporting (E_ALL);

function fetch_db_data($ sql) {
$conn = mysql_connect() or die('==>' . mysql_error());
$res = mysql_query($sq l) or die($sql . ' ==> ' . mysql_error());
while ($row = mysql_fetch_row ($res)) {
$data[] = $row;
}
mysql_free_resu lt($res);
mysql_close($co nn);
return $data;
}

/* maybe this global data and the two functions could be done in OOP */
/* and change the calls inside recurse_db_arra y() */

/* GLOBAL data */
$GBL_filter_val ue = 0;

/* uses global data!!! */
function set_filter_valu e($x) {
global $GBL_filter_val ue;
$GBL_filter_val ue = $x;
}

/* uses global data!!! */
function filter($row) {
global $GBL_filter_val ue;
return $row[2] == $GBL_filter_val ue;
}

/* pass data by reference, but it never changes */
/* if max_level is < 0, do a full recursion on all the data */
function recurse_db_arra y(&$data, &$arr, $parent, $max_level) {
if ($max_level-- == 0) return;
$index = 0;

/* maybe use a class??? */
/* I'm not used to OOP -- I am only used to "oops" */
set_filter_valu e($parent);
$children = array_filter($d ata, 'filter');

foreach ($children as $child) {
$arr[$index] = $child;
recurse_db_arra y($data, $arr[$index]['children'], $child[0], $max_level);
++$index;
}
}



$sql = 'select id, testo, parent, allegato, descrizione, online '
. 'from test.osservator io '
. 'where 1=1';
$sql_data = fetch_db_data($ sql);
recurse_db_arra y($sql_data, $arr, 0, -1);
print_r($arr);

/* another example */
unset($arr);
recurse_db_arra y($sql_data, $arr, 3, 1);
print_r($arr);
?>


--
USENET would be a better place if everybody read: | to mail me: simply |
http://www.catb.org/~esr/faqs/smart-questions.html | "reply" to this post, |
http://www.netmeister.org/news/learn2quote2.html | *NO* MIME, plain text |
http://www.expita.com/nomime.html | and *NO* attachments. |

[Sathia Musso]

Re: binary tree, long &amp;&amp; difficult

grazie :)

it's very good now!

later i'll study it.


i've already tryed it and it works like a charm.
thank you very much indeed

Ciao dall'italia!


where are you from?


--
Sat_

[Pedro Graca]

Re: binary tree, long &amp;&amp; difficult

Ciao Sathia

Sathia Musso wrote:[color=blue]
> it's very good now![/color]

Glad you like it :-)


[snip][color=blue]
> where are you from?[/color]

It's all in my headers ... but I'll spare you :)

X-Location: Third rock from the Sun, Europe, Portugal, Coimbra
--
USENET would be a better place if everybody read: | to mail me: simply |
http://www.catb.org/~esr/faqs/smart-questions.html | "reply" to this post, |
http://www.netmeister.org/news/learn2quote2.html | *NO* MIME, plain text |
http://www.expita.com/nomime.html | and *NO* attachments. |

[sathia musso]

Re: binary tree, long &amp;&amp; difficult

Thank you very much, i studied it and it works perfectly.

unfortunately i can't let you see what i done, 'cause it's under password.

Thank you very much, and see you soon :)

[Sathia Musso]

Re: binary tree, long &amp;&amp; difficult

oooops

it seems broken, here's the entire discussion

http://groups.google.it/groups?hl=it&lr=&threadm=slrncnstqa.8p2.hexkid%40ID-203069.user.uni-berlin.de&rnum=6&prev=/groups%3Fq%3Dlong%2B%2Bdifficult%2Bbinary%26hl%3Dit%26lr%3D%26selm%3Dslrncnstqa.8p2.hexkid%2540ID-203069.user.uni-berlin.de%26rnum%3D6

--[color=blue][color=green]
>> man man ; man cd; man ls; man bash[/color]
> fatto non succede nulla[/color]

[Pedro Graca]

Re: binary tree, long &amp;&amp; difficult

Sathia Musso wrote:[color=blue]
> oooops
>
> it seems broken ...[/color]

It works for me.

The only change I did to the posted code was for the mysql_connect()
call: I included the parameters for my specific MySQL installation.

Ah! I also included an echo "\n\n--------\n\n"; between the two
examples.





mysql> select * from test.osservator io;
+----+--------------------------------+--------+----------+-------------+--------+
| id | testo | parent | allegato | descrizione | online |
+----+--------------------------------+--------+----------+-------------+--------+
| 1 | Monitoraggio Ambientale | 0 | NULL | | true |
| 2 | Lotto 1.1 | 1 | NULL | | true |
| 3 | Lotto 1.2 | 1 | NULL | | true |
| 4 | Lotto 1.2.1 | 3 | NULL | | true |
| 5 | Lotto 1.2.2 | 3 | NULL | | true |
| 6 | Lotto 1.2.2.3 | 3 | NULL | | true |
| 7 | Programma Attività | 5 | NULL | | true |
| 8 | Programma Attività | 6 | NULL | | true |
| 9 | Relazione Rumore Corso D'Opera | 6 | NULL | | true |
| 10 | Lotto 1.3 | 1 | NULL | | true |
| 11 | Lotto 1.4 | 1 | NULL | | true |
| 12 | Relazione Metodologica | 1 | NULL | | true |
+----+--------------------------------+--------+----------+-------------+--------+
12 rows in set (0.05 sec)


php$ php sathia.php

Array
(
[0] => Array
(
[0] => 1
[1] => Monitoraggio Ambientale
[2] => 0
[3] =>
[4] =>
[5] => true
[children] => Array
(
[0] => Array
(
[0] => 2
[1] => Lotto 1.1
[2] => 1
[3] =>
[4] =>
[5] => true
[children] =>
)

[1] => Array
(
[0] => 3
[1] => Lotto 1.2
[2] => 1
[3] =>
[4] =>
[5] => true
[children] => Array
(
[0] => Array
(
[0] => 4
[1] => Lotto 1.2.1
[2] => 3
[3] =>
[4] =>
[5] => true
[children] =>
)

[1] => Array
(
[0] => 5
[1] => Lotto 1.2.2
[2] => 3
[3] =>
[4] =>
[5] => true
[children] => Array
(
[0] => Array
(
[0] => 7
[1] => Programma Attività
[2] => 5
[3] =>
[4] =>
[5] => true
[children] =>
)

)

)

[2] => Array
(
[0] => 6
[1] => Lotto 1.2.2.3
[2] => 3
[3] =>
[4] =>
[5] => true
[children] => Array
(
[0] => Array
(
[0] => 8
[1] => Programma Attività
[2] => 6
[3] =>
[4] =>
[5] => true
[children] =>
)

[1] => Array
(
[0] => 9
[1] => Relazione Rumore Corso D'Opera
[2] => 6
[3] =>
[4] =>
[5] => true
[children] =>
)

)

)

)

)

[2] => Array
(
[0] => 10
[1] => Lotto 1.3
[2] => 1
[3] =>
[4] =>
[5] => true
[children] =>
)

[3] => Array
(
[0] => 11
[1] => Lotto 1.4
[2] => 1
[3] =>
[4] =>
[5] => true
[children] =>
)

[4] => Array
(
[0] => 12
[1] => Relazione Metodologica
[2] => 1
[3] =>
[4] =>
[5] => true
[children] =>
)

)

)

)


--------

Array
(
[0] => Array
(
[0] => 4
[1] => Lotto 1.2.1
[2] => 3
[3] =>
[4] =>
[5] => true
[children] =>
)

[1] => Array
(
[0] => 5
[1] => Lotto 1.2.2
[2] => 3
[3] =>
[4] =>
[5] => true
[children] =>
)

[2] => Array
(
[0] => 6
[1] => Lotto 1.2.2.3
[2] => 3
[3] =>
[4] =>
[5] => true
[children] =>
)

)


--
Mail sent to my "From:" address is publicly readable at http://www.dodgeit.com/
== ** ## !! !! ## ** ==
TEXT-ONLY mail to the complete "Reply-To:" address ("My Name" <my@address>) may
bypass the spam filter. I will answer all pertinent mails from a valid address.

[Sathia Musso]

Re: binary tree, long &amp;&amp; difficult

oooops i meant "broken" talking about my NG server who keeps thread for 2
weeks only...

the script is great;)

Bye

[Pedro Graca]

Re: binary tree, long &amp;&amp; difficult

Sathia Musso wrote:[color=blue]
> oooops i meant "broken" talking about my NG server who keeps thread for 2
> weeks only...[/color]

Ah! Sorry for the misunderstandin g :-)
--
Mail sent to my "From:" address is publicly readable at http://www.dodgeit.com/
== ** ## !! !! ## ** ==
TEXT-ONLY mail to the complete "Reply-To:" address ("My Name" <my@address>) may
bypass the spam filter. I will answer all pertinent mails from a valid address.