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','false')*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]

sathia wrote:

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

I think you need a recursive solution:

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

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

mysql_free_result($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]

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]

sathia wrote:

Thank you very much, i never tried to use several queries.
on my own i thought it would be possible just using one query.
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.

lovely solution.

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]

Pedro Graca wrote:

sathia wrote:

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

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'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]

Sathia Musso wrote:

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

Tomorrow I'll let you know.

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($sql) or die($sql . ' ==> ' . mysql_error());
while ($row = mysql_fetch_row($res)) {
$data[] = $row;
}
mysql_free_result($res);
mysql_close($conn);
return $data;
}

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

/* GLOBAL data */
$GBL_filter_value = 0;

/* uses global data!!! */
function set_filter_value($x) {
global $GBL_filter_value;
$GBL_filter_value = $x;
}

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

/* pass data by reference, but it never changes */
/* if max_level is < 0, do a full recursion on all the data */
function recurse_db_array(&$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_value($parent);
$children = array_filter($data, 'filter');

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

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

/* another example */
unset($arr);
recurse_db_array($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]

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]

Ciao Sathia

Sathia Musso wrote:

it's very good now!
Glad you like it :-)
[snip] where are you from?

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]

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]

oooops

it seems broken, here's the entire discussion

http://groups.google.it/groups?hl=it....de%26rnum%3D6

--

man man ; man cd; man ls; man bash

fatto non succede nulla

[Pedro Graca]

Sathia Musso wrote:

oooops

it seems broken ...

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.osservatorio;
+----+--------------------------------+--------+----------+-------------+--------+
| 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]

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

the script is great;)

Bye

[Pedro Graca]

Sathia Musso wrote:

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

Ah! Sorry for the misunderstanding :-)
--
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.