Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

[Resolved] Populating a 2-level navmenu from 2 mysql tables

Codes here !

Moderators: macek, egami, gesf

[Resolved] Populating a 2-level navmenu from 2 mysql tables

Postby Red » Wed Jul 16, 2003 12:01 am

I'm making a pretty standard navigation menu that contains the different sections on my site. When the user clicks on a section in the menu, the section name gets highligghted in bold and a list of the categories gets listed under that section inside the navigation menu.
Both the sections and the categories are obtained from 2 tables in a MySQL database (section & category).

The problem is that the section names get repeated for each category, so instead of getting this:

SECTION1 (1 category)
SECTION2 (2 categories)
-category1
-category2
SECTION3 (5 categories)

I get this:
SECTION1
SECTION2
-category1
SECTION2
-category2
SECTION3
SECTION3
SECTION3
SECTION3
SECTION3

Code: Select all
 
//obtain current section:
@$section_id = $_GET["section"];

//define sql query:
$query = mysql_query("SELECT * FROM section
JOIN category ON section.id=category.sec_id
WHERE section.id=category.sec_id");

//populate menu from sql query:
while ($row = mysql_fetch_array($query))
  {
    $menu_section_id=$row["sec_id"];
    $menu_section_title=$row["sec_title"];
    $menu_category_id=$row["cat_id"];
    $menu_category_title=$row["cat_title"];

    if ($menu_section_id == $section_id) {
       print ("<tr><td><b><a href=\"intranet.php?view=section_overview& section=$menu_section_id\">
$menu_section_title</a></b><br>");
      print ("- <a href=\"intranet.php?view=category_overview& section=$menu_section_id&category=$menu_category_id\">
$menu_category_title</a><br>");
      print ("</td></tr>");
    }
    else {
      print ("<tr><td><a href=\"intranet.php?view=section_overview& section=$menu_section_id\">
$menu_section_title</a></td></tr>");
    }
  }


I think the solution might be making a sub-array for the categories inside the array for the sections, if the if-statement = true:

Code: Select all
    if ($menu_section_id == $section_id) {
       print ("<b>section name...</b>");
       -some sort of code for making a new array for the categories goes here...-
    }
    else
      print ("section name...");
    }


But I'm not sure exactly how I should do this.


I hope someone can help me, since this seems to be pretty simple stuff...
Last edited by Red on Wed Jul 16, 2003 3:50 am, edited 1 time in total.
Red
New php-forum User
New php-forum User
 
Posts: 2
Joined: Tue Jul 15, 2003 11:37 pm

Postby Joel » Wed Jul 16, 2003 3:01 am

Your problem is in your loop. Notice how many times its repeating, it's repeating the SECTION stuff as many times as their is categories in that section. I had 2 tables like this...

Table: Sections

section_id | section
1 | Section 1
2 | Section 2
3 | Section 3

Table: Categories

cat_id | section_id | category
1 | 1 | Category 1
2 | 2 | Category 2 (in section 2)
3 | 1 | Cat 3
4 | 3 | Cat 4 (in section 3)

Then I had a loop within a loop, with 2 queries. Like so

Code: Select all
$sect_sql = mysql_query("SELECT * FROM sections ORDER BY section");

while ($sect_result = mysql_fetch_assoc($sect_sql)) {
     $sect_id = $sect_result['sect_id'];
     echo "<b>".$sect_result['section']."</b><br>";
     $cat_sql = mysql_query("SELECT * FROM categories WHERE cat_id = $cat_id ORDER BY category");
    while ($cat_result = mysql_fetch_assoc($cat_sql) {
           echo $cat_result['category']."<br>";
     }
}




I'm sure joins and Group by could make this more efficient, but I'm much too tired for more thinking right now, hope I helped, which I think may be unlikely for what I just did.
Joel
New php-forum User
New php-forum User
 
Posts: 193
Joined: Sat Mar 29, 2003 11:57 pm
Location: Auckland, New Zealand

Postby Red » Wed Jul 16, 2003 3:50 am

Thanks, I did something similar to your example and it worked :)
Red
New php-forum User
New php-forum User
 
Posts: 2
Joined: Tue Jul 15, 2003 11:37 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: Google [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.