multi level subcategories : php and databases

May 9, 2008 09:00am pdt

1. hielo 391,380
2. angelIII 132,290
3. logudotcom 95,183
4. routinet 94,772
5. hernst42 90,474
6. RQuadling 90,196
7. rowejd 77,343
8. dr_dedo 74,810
9. nplib 72,610
10. Xyptilon2 68,772
11. bportlock 60,756
12. yodercm 60,754
13. Ray_Paseur 60,314
14. Roonaan 54,335
15. steelseth12 43,890

1. angelIII 717,457
2. ldbkutty 688,003
3. hernst42 646,347
4. Roonaan 582,275
5. wasifg 503,829
6. RQuadling 472,809
7. hielo 408,305
8. snoyes_jw 401,445
9. steelseth12 301,165
10. i_m_aamir 292,259
11. glcummins 281,313
12. gamebits 277,284
13. TeRReF 262,878
14. yodercm 252,990
15. Diablo84 239,472

03.24.2008 at 05:08PM PDT, ID: 23265753

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

4.5

multi level subcategories

Zones: PHP and Databases, PHP Scripting Language

Tags: php and databases

Hi, I have this piece of code I received from the forum and need some help in getting it to layout the way I need it. I have a table in mysql that is set up like this:

cat_id int(11)
cat_name varchar(30)
cat_parent int(11)

Here is some data in there

cat_id cat_name cat_parent
1 Tack 0
2 Western 1
3 Horses 0
4 Saddle 2

What I need to do is have the information layout like this:

Horses Tack
Western
Saddle

Right now this script does one level subcategory but I need to know if I have my database set up ok to allow 2nd level subcategories and how I would show the second subcategory with what I have.

Thank you much for helping me out. I am ok at taking code and implementing but not good at adding or rearranging it.

Rob

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

           
           
             <?PHP
function findSubs($parentid){
    $res = mysql_query("select * from tblCategories where cat_parent='$parentid'") or die ('the error is' .mysql_error() );
    while($inf = mysql_fetch_array($res)){
        echo "<br>&nbsp;&nbsp;&#149;&nbsp;<a href=\"viewcategory.php?cat_id=".$inf['cat_id']."\" class=\"brownlinks\">".$inf["cat_name"]."</a>";    
        findSubs($inf["cat_id"]);
       
    }    
}
 
 
$res = mysql_query("select * from tblCategories where cat_parent='0' order by cat_name asc");
$columncount = 1;
while($inf = mysql_fetch_array($res)){ ?>
  <td valign="top"><a href="viewcategory.php?cat_id=<?php echo $inf['cat_id']; ?>" class="title"><?PHP echo $inf["cat_name"]; ?></a>  
  <?PHP findSubs($inf["cat_id"]); ?>
  </td>
 
  <?PHP if($columncount == 3) {
    echo '</tr><tr>';
    $columncount = 1;
  }
  else {
    $columncount++;
  }
}
?>

           
         

Start your free trial to view this solution

Question Stats

Zone: Web Development

Question Asked By: pertrai1

Solution Provided By: amacleod1983

Participating Experts: 1

Solution Grade: C

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

03.25.2008 at 01:56AM PDT, ID: 21200307

amacleod1983:

This code should do what you need
sorry in advance for any typos :-)

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

           
           
             function listMenu($menu) {
		$query = mysql_query("select * from tblCategories where cat_parent='0' order by cat_name asc");
		$results = mysql_fetch_array($query);
		if($results > 0){
		echo "<div>";
		echo "<ul>";
		foreach($results as $result) {
			echo "<li><a href=\"viewcategory.php?cat_id={result['cat_id']}\" class=\"brownlinks\">{result['cat_name']}</a></li>";
			findSubs($result['cat_id']);
		}
		echo "</ul></div>";
		}
		else {
			echo "<div>No Such Menu</div>";
		}
		
	function findSubs($parentID){
		$query = mysql_query("select * from tblCategories where cat_parent='$parentID' order by cat_name asc");
		$results = mysql_fetch_array($query);
		if($results > 0){
		echo "<li>";
		echo "<ul>";
		foreach($results as $result) {
			echo "<li><a href=\"viewcategory.php?cat_id={result['cat_id']}\" class=\"brownlinks\">{result['cat_name']}</a></li>";
			$query2 = mysql_query("select * from tblCategories where cat_parent='{result['cat_id']}' order by cat_name asc");
			$results2 = mysql_fetch_array($query);
			if($results2 > 0){
				echo "<li>";
				echo "<ul>";
				foreach($results2 as $result2) {
					echo "<li><a href=\"viewcategory.php?cat_id={result2['cat_id']}\" class=\"brownlinks\">{result2['cat_name']}</a></li>";
				}
			echo "</ul></li>";
			}
		}
		echo "</ul></li>";
		}
	}

           
         

Open in New Window

03.25.2008 at 07:02AM PDT, ID: 21201976

pertrai1:

Hey, this looks like it will do the trick for me but I would like to keep the table format so that I have 3 columns with as many rows as it takes depending on how many categories there are. Where would I put the proper formatting of the <td> and <tr> to accomplish this?

Rob

03.25.2008 at 08:09AM PDT, ID: 21202616

amacleod1983:

Are you wanting a colum per mune/submenu?

03.25.2008 at 08:29AM PDT, ID: 21202809

pertrai1:

I am not for sure what you mean by that but what I would like is this:

Menu Menu Menu
Sub Sub Sub
Sub Sub Sub

Menu Menu Menu

etc...

So 3 columns across my page and unlimited rows so that categories can be added. I don't mind if the subs are <br /> below the main category and same with the sub subs.

Thank you for taking the time to help me. You are a blessing.

One more thing, and if need be, I can make a new topic to add points since this is already at 500. How would I should how many items are in each category? For example

Menu (10)
Sub(5)
Sub(3)

Again, if points are needed I can start a new topic for this.
Rob

03.25.2008 at 10:44AM PDT, ID: 21204189

amacleod1983:

Here you go used a nested table for sub results so shouldn't have any format issues

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

           
           
             <?php
function listMenu($menu) {
                $query = mysql_query("select * from tblCategories where cat_parent='0' order by cat_name asc");
                $results = mysql_fetch_array($query);
                if($results > 0){
                echo "<table>";
                echo "<tr>";
                foreach($results as $result) {
                        echo "<td><table><td><tr><a href=\"viewcategory.php?cat_id={result['cat_id']}\" class=\"brownlinks\">{result['cat_name']}</a></tr>";
                        findSubs($result['cat_id']);
						echo "</td></table>";
                }
                echo "</tr></table>";
                }
			}
                
        function findSubs($parentID){
                $query = mysql_query("select * from tblCategories where cat_parent='$parentID' order by cat_name asc");
                $results = mysql_fetch_array($query);
                if($results > 0){
                	foreach($results as $result) {
                        echo "<tr><a href=\"viewcategory.php?cat_id={result['cat_id']}\" class=\"brownlinks\">{result['cat_name']}</a></tr>";
                        $query2 = mysql_query("select * from tblCategories where cat_parent='{result['cat_id']}' order by cat_name asc");
                        $results2 = mysql_fetch_array($query);
                        if($results2 > 0){
                        	foreach($results2 as $result2) {
                            	echo "<tr><a href=\"viewcategory.php?cat_id={result2['cat_id']}\" class=\"brownlinks\">{result2['cat_name']}</a></tr>";
                            }
                        }
                	}
                }
        }
?>

           
         

Open in New Window

03.25.2008 at 11:23AM PDT, ID: 21204566

pertrai1:

Hi, I can't get this to produce any output at all. I am looking it over trying to figure out why and will keep doing so to see what is stopping it from producing the output. The echo's are not even being produced in the source code.

Thank you for your time.

Rob

03.25.2008 at 12:23PM PDT, ID: 21205168

amacleod1983:

can i get an sql dump of your menu table and i can work on it here.

03.26.2008 at 07:49AM PDT, ID: 21212202

amacleod1983:

Please close this question and start a different one for counting menu items.

03.26.2008 at 10:39AM PDT, ID: 21214260

pertrai1:

How do I close this question?

03.26.2008 at 10:51AM PDT, ID: 21214371

amacleod1983:

accept an snswer

03.26.2008 at 11:10AM PDT, ID: 21214551

pertrai1:

ok, but I have not had a chance to get you my data so we can try to figure this out. Here is the data:

cat_id       cat_name        cat_parent
3       Horse Wear        0
34       Riding Apparel      0
35       Horse Tack        0
36       Horse Blankets       0
37       Training Supplies        0
38       Stable Supplies       0
39       LIQUIDATORS\' CLOSEOUT!       0       0
40       Breeches and Jodphurs       34
41       Saddles        35
43       Pessoa Saddles        41
44       Ovation        40

Any thoughts?

Rob

03.26.2008 at 11:16AM PDT, ID: 21214613

amacleod1983:

the code should work. you are calling listMenu

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

           
           
             <?php
 
function findSubs($parentID){
                $query = mysql_query("select * from tblCategories where cat_parent='$parentID' order by cat_name asc");
                $results = mysql_fetch_array($query);
                if($results > 0){
                        foreach($results as $result) {
                        echo "<tr><a href=\"viewcategory.php?cat_id={result['cat_id']}\" class=\"brownlinks\">{result['cat_name']}</a></tr>";
                        $query2 = mysql_query("select * from tblCategories where cat_parent='{result['cat_id']}' order by cat_name asc");
                        $results2 = mysql_fetch_array($query);
                        if($results2 > 0){
                                foreach($results2 as $result2) {
                                echo "<tr><a href=\"viewcategory.php?cat_id={result2['cat_id']}\" class=\"brownlinks\">{result2['cat_name']}</a></tr>";
                            }
                        }
                        }
                }
        }
 
function listMenu() {
                $query = mysql_query("select * from tblCategories where cat_parent='0' order by cat_name asc");
                $results = mysql_fetch_array($query);
                if($results > 0){
                echo "<table>";
                echo "<tr>";
                foreach($results as $result) {
                        echo "<td><table><td><tr><a href=\"viewcategory.php?cat_id={result['cat_id']}\" class=\"brownlinks\">{result['cat_name']}</a></tr>";
                        findSubs($result['cat_id']);
                                                echo "</td></table>";
                }
                echo "</tr></table>";
                }
                        }
                
        
?>

           
         

Open in New Window

Accepted Solution

03.26.2008 at 11:49AM PDT, ID: 21214918

pertrai1:

Hi, I appreciate your help on this. I am a rookie at the php stuff and I do not know what you mean when you say calling listMenu. Please help me to understand.

Thank you again.
Rob

03.26.2008 at 11:56AM PDT, ID: 21214992

amacleod1983:

at the end of all the code withing just before the closing ?>
type: listMenu();

03.26.2008 at 04:20PM PDT, ID: 21217420

pertrai1:

amacleod, I could not get the code to work but I am giving the point because you took the time to help me. I will try to figure this out. Thank you much.

20080236-EE-VQP-29 / EE_QW_2_20070628