Getting from MySQL table -> hierarchical navigation menu

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Getting from MySQL table -> hierarchical navigation menu

Post by Sinemacula »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I've taken a stab at doing this myself, and it seems to be partially/almost working, but I can't get it to where I need it. I'm also not sure if the approach I've taken makes any sense...

I have set up a single db table as follows:

[syntax="sql"]CREATE TABLE `itpbooks` (
  `id` int(11) NOT NULL auto_increment,
  `coursegroup` varchar(15) default NULL,
  `coursenum` varchar(100) NOT NULL default '',
  `title` varchar(250) default NULL,
  `isbn` varchar(15) default NULL,
  KEY `id` (`id`)
)
Each 'coursegroup' will have several 'coursenum' and each 'coursenum' will have several 'title' and 'isbn' entries. I want to end up with a navigation menu that is like this:
  • Course Group 1
    • - Course A
      • - < a href to ISBN 1>Title 1< / a>
      • - < a href to ISBN 2>Title 2< / a>
    • - Course B
      • - < a href to ISBN 3>Title 3< / a>
  • Course Group 2
    • - Course C
      • - < a href to ISBN 4>Title 4< / a>
...and so on, where coursegroups are parents of coursnums, which are parents of ISBN and titles.

Here's what I tried and got partially working:[/syntax]

Code: Select all

<?php			
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$result = mysql_query("SELECT coursegroup FROM itpbooks",$db);

echo "<ul id=\"treemenu1\" class=\"treeview\">";

while ($myrow = mysql_fetch_array($result))
{

echo "<li>".$myrow['coursegroup']."";

$grouping=$myrow['coursegroup'];
$result2 = mysql_query("SELECT coursenum FROM itpbooks WHERE coursegroup = '$grouping'",$db);

echo "<ul>";

while ($myrow2 = mysql_fetch_array($result2))
{

echo "<li>".$myrow2['coursenum']."";

$course=$myrow2['coursenum'];
$result3 = mysql_query("SELECT * FROM itpbooks WHERE coursenum = '$course'",$db);

echo "<ul>";

while ($myrow3 = mysql_fetch_array($result3))
{

echo "<li><a href=\"http://astore.amazon.com/itp-ma4cs-20/detail/".$myrow3['isbn']."\" target=\"iframe\">".$myrow3['title']."</a></li>";
}

echo "</li></ul>";
}

echo "</li></ul>";
}

echo "</li></ul>";

?>
This does get the nested hierarchy displayed, but it displays each of the coursegroups several times, so I've got something like this:
  • Course Group 1
    • - Course A
      • - < a href to ISBN 1>Title 1< / a>
      • - < a href to ISBN 2>Title 2< / a>
    • - Course B
      • - < a href to ISBN 3>Title 3< / a>
  • Course Group 1
    • - Course A
      • - < a href to ISBN 1>Title 1< / a>
      • - < a href to ISBN 2>Title 2< / a>
    • - Course B
      • - < a href to ISBN 3>Title 3< / a>
  • Course Group 2
    • - Course C
      • - < a href to ISBN 4>Title 4< / a>
  • Course Group 2
    • - Course C
      • - < a href to ISBN 4>Title 4< / a>
After doing a search of the forums here, I'm not sure whether I should be redesigning the database part by dividing it into three separate tables, or if I should be trying to do "joins" within the existing table, or if maybe there's a simple fix to the code I've already got.

Unfortunately, I don't know enough to know which is the best approach, and would probably need help getting going if I'm to change strategies.

Suggestions?

Thanks,
Scott


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

After posting that, I decided to give the multiple tables approach a try, so I divided the one table into three:

-table itpgroups has id and groups
- table itpcourses has id, groups, and courses
- table itpbooks has courses, titles, and isbn

So, the first query was to the itpgroups table, then a query to the itpcourses table nested within that, and a query to the itpbooks table nested within that, as follows:

Code: Select all

<?php			
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$result = mysql_query("SELECT coursegroup FROM itpgroups",$db);

echo "<ul id=\"treemenu1\" class=\"treeview\">";

while ($myrow = mysql_fetch_array($result))
{

echo "<li>".$myrow['coursegroup']."";

$grouping=$myrow['coursegroup'];
$result2 = mysql_query("SELECT coursenum FROM itpcourses WHERE coursegroup = '$grouping'",$db);

echo "<ul>";

while ($myrow2 = mysql_fetch_array($result2))
{

echo "<li>".$myrow2['coursenum']."";

$course=$myrow2['coursenum'];
$result3 = mysql_query("SELECT * FROM itpbooks WHERE coursenum = '$course'",$db);

echo "<ul>";

while ($myrow3 = mysql_fetch_array($result3))
{

echo "<li><a href=\"http://astore.amazon.com/itp-ma4cs-20/detail/".$myrow3['isbn']."\" target=\"iframe\">".$myrow3['title']."</a></li>";
}

echo "</li></ul>";
}

echo "</li></ul>";
}

echo "</li></ul>";

?>
This produces the desired output!! :D

That said, if there's a better, or more effecient way to do this, I'm still open to suggestions.

Thanks,
Scott
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

Most important thing here, don't do a query, loop on that query and do a query for each record. Doing that is extremely inefficient and can almost always be solved by a join.

Second most important item, indent your nested loops... I have killed for less.

Do a single query ordered by group, course, then title. It doesn't matter whether its multiple tables or what, as long as your data ends up looking right (in your case the proper normalized structure is 3 tables like you have). If you have 3 tables you will use an sql join.

You should end up with results something like this.

group 1 course 2 title1
group 1 course 2 title3
group 1 course 5 title6
group 2 course 1 title 2
group 2 course 1 title 4
group 3 course 1 title 1

Looping on something like this is pretty straight forward. You will store the group in a variable and the current course in a variable. Then you will loop through your records, if the group changes you will print out the group title and reset your group variable to the new one, and if the course changes you will print out the course title and switch the course variable to the new one.

Code: Select all

$currentGroup = '' ;
$currentCourse = '' ;
while ($myrow = mysql_fetch_array($result))
{
  if ($currentGroup != $myRow['group'] ) 
  {
    $currentGroup = $myRow['group'] ;
    echo $currentGroup.'<br />' ;
  }

  if ($currentCourse != $myRow['course'])
  {
    $currentCourse = $myRow['course'] ;
    echo $currentCourse.'<br />' ;
  }
  echo $myRow['title'] ;
}
The above should give you

group 1
course 2
title1
title3
course 5
title6
group 2
course 1
title 2
title 4
group 3
course 1
title 1


You will need to of course adapt your loops for LI
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Thanks, Begby,

I see what you're doing there, and I'm pretty sure I follow...

I am having a bit of a problem with the query though, as I'm not very clear on how JOINs work (looking over the MySQL documentation hasn't helped much, yet).

Here's one of the ways I've tried it:

Code: Select all

$result = mysql_query("SELECT g.coursegroup AS group, c.coursenum AS course, b.title AS title, b.isbn AS isbn FROM itpgroups AS g JOIN itpcourses AS c ON(g.coursegroup=c.coursegroup) JOIN itpbooks AS b ON(c.coursenum=b.coursenum)",$db);
I've also tried:

Code: Select all

$result = mysql_query("SELECT g.coursegroup AS group, c.coursenum AS course, b.title AS title, b.isbn AS isbn FROM itpgroups AS g JOIN itpcourses AS c USING(coursegroup) JOIN itpbooks AS b USING(coursenum)",$db);
but those are both coming up with:
mysql_fetch_array(): supplied argument is not a valid MySQL result resource
I've tried several other variations, but trial and error isn't getting me too far. :(

I did have one version that didn't give me the "invalid" error, but the array came up empty... so not much better.

Is there an easy way to gain an understanding of the logic for JOINing tables? I've obviously missed it.

Thanks,
Scott
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

I suggest reading up on joins from a book or manual to get started on it. I think you are going to want to use left join as well in this instance.

Next try experimenting with very simple joins until you are getting the resultsets you expect and are understanding it, then tackle this.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Unfortunately (or not) since this isn't my job/career, and I don't get paid for the time I spend trying to do this sort of thing on my own, I can't afford the time to wade through the manuals (I do always browse through when pointed in the right direction, to see if I can figure stuff out myself without needing to learn it all from the ground up, but on these JOINs I haven't been able to get it - yet).

What I've got may not be efficient, but since it gives the result I want, I'll probably just leave well-enough alone.
Post Reply