Remember that this is my first script, but here's how I extracted and
associated category hierarchies:
include 'c:\apache2\adodb\adodb\adodb.inc.php';
$ADODB_CACHE_DIR = 'c:\apache2\adodb\adodb-cache';
$db = &ADONewConnection( 'mysql' );
$db -> Connect( 'localhost', 'root', 'mysql', 'reach');
$sql_cats = "SELECT cat_id, cat_title FROM phpbb_categories ORDER BY
cat_id";
$sql_fora = "SELECT forum_id, cat_id, forum_name FROM phpbb_forums ORDER BY
cat_id";
$sql_topics = "SELECT forum_id, topic_id, topic_title FROM phpbb_topics
ORDER BY forum_id";
$db -> SetFetchMode( ADODB_FETCH_ASSOC );
$rs_cats = &$db -> Execute( $sql_cats );
$rs_fora = &$db -> Execute( $sql_fora );
$rs_topics = &$db -> Execute( $sql_topics );
$cat_id = "";
$forum_id = "";
$cats_inc = 1;
$fora_inc = 1;
$topics_inc = 1;
while( $cats_row = $rs_cats -> FetchNextObject() ) {
$cat_id = $cats_row -> CAT_ID;
print "<tr><td align='left'>" .
"<input type='text' value='+ " . $cats_row -> CAT_TITLE . "' name='"
.
$cats_row -> CAT_TITLE .
"' readonly onclick='change_value( this, this.name, this.value )' "
.
"style='border:none;cursor:hand;color:#000099;font-weight:600'>";
$rs_fora -> MoveFirst();
while( $fora_row = $rs_fora -> FetchNextObject() ) {
if( $cat_id == $fora_row -> CAT_ID ) {
$forum_id = $fora_row -> FORUM_ID;
print "<br/> <input
type='text' value='+ " .
$fora_row -> FORUM_NAME . "' name='" . $fora_row ->
FORUM_NAME . "' readonly " .
"onclick='change_value( this, this.name, this.value
)' " .
"style='border:none;cursor:hand;color:#000099;font-weigth:600'>";
}
$rs_topics -> MoveFirst();
while( $topics_row = $rs_topics -> FetchNextObject() ) {
if(( $topics_row -> TOPIC_TITLE != "" ) && (
$forum_id == $topics_row -> FORUM_ID )){
print
"<br/> " .
"<input type='text' value='=> " .
$topics_row -> TOPIC_TITLE . "' name='" .
$topics_row -> TOPIC_TITLE . "' readonly " .
"onclick='change_value( this, this.name,
this.value )' " .
"style='border:none;cursor:hand;color:#000099;font-weigth:600'>";
}
}
}
print "</td></tr><tr><td> </td></tr>";
}
?>
I'm sure this can be refactored, but I don't know PHP well enough to
optimize it right now.
Cheers!
Mark
-----Original Message-----
From: John W. Holmes [mailto:holmes072000@xxxxxxxxxxx]
Sent: Friday, March 19, 2004 11:42 AM
To: php-db@xxxxxxxxxxxxx; Age Bosma
Subject: Re: MySQL category tree db sorting
From: "Age Bosma" <agebosma@xxxxxxx>
> I'm trying to work out what the most efficient way will be to get the
> complete tree structure from top to bottom of a category tree db.
Search the archives or Google for "nested sets". That's going to be the most
efficient database scheme to use. Any parent-child-relationship solution is
going to require a lot of queries when your "trees" get large.
I second the recommendation for "SQL for Smarties" by Joe Celko, too.
Excellent book.
---John Holmes...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
