Webmaster Forum

Go Back   Webmaster Forum > Web Development > Coding Forum

Coding Forum Problems with your code? Discuss coding issues, including JavaScript, PHP & MySQL, HTML & CSS, Flash & ActionScript, and more.


Reply
 
Thread Tools Display Modes
Share |
  #1  
Old 08-19-2004, 09:12 AM
robpicken robpicken is offline
Member
 
Join Date: 02-03-04
Location: Wales (for a while)
Posts: 81
iTrader: 0 / 0%
creating category structure php mysql

Hi all,

I've spent the past couple of days trying to sort out the category structure for a new web site i'm building. First of all i was informed it was going to be just 2 levels deep, now its looking as though it could be 3 or 4 levels deep for some cats.

The part i'm really struglling with is getting the 3 level deep cats to show up properly in a drop down menu, as i need it to form part of an add product form.

I can do the 2 levels, and that works, and i've tried to create a work around to get the three levels working. So far if i get the three levels showing up in the dd menu, i only get those showing up and no other cats, with a mysql error message for each three level cats that are showing up, calling up an mysql error in the query, (hope you follow me).

I've also tried running the code from a category tree table, and the normal cats table, but just can't quite fix it.

I've included the code below, its kind of a mess as i've spent most of the past 3 hours trying to fix it and ended up confusing myself, so if anybody can point me in the right direction or even pass on advice as to the best way of doing this bit i'd be most grateful.

[code:1:e55fd47981]$sql = mysql_query("SELECT * FROM brochure_cats");
while($row = mysql_fetch_array($sql)) {
$id = $row['cat_id'];
$parent = $row['parent_id'];
$name = $row['cat_name'];

// Lets Get its Parent
$parent = mysql_query("SELECT * FROM brochure_cats where cat_id='$parent'");
while($row = mysql_fetch_array($parent)) {
$parent_id = $row['cat_name'];
$parent_name = $row['parent_id'];

$middle = mysql_query("SELECT * FROM brochure_cats WHERE cat_id='$parent_name'");
while($row = mysql_fetch_array($middle)) {
$middle_id = $row['cat_name'];

$selector = ' :';
$parent_output = $parent_id . $selector;
$output .= "<OPTION VALUE="$id">$middle $parent_output $name</OPTION> \n";

}
}
return $output;

}[/code:1:e55fd47981]

oh, p.s, the parent and middle bits pull up the info the wrong way round as you'll notice in the output element.

Many Thanks,
 
Reply With Quote

Advertisement

Advertisement

  #2  
Old 08-19-2004, 10:13 AM
littleFella's Avatar
littleFella littleFella is offline
Senior Member
 
Join Date: 06-20-04
Location: Ontario
Posts: 1,756
iTrader: 0 / 0%
show us the data structure as it presents itself withing the database tables, along with relations between tables.
 
Reply With Quote
  #3  
Old 08-19-2004, 01:10 PM
robpicken robpicken is offline
Member
 
Join Date: 02-03-04
Location: Wales (for a while)
Posts: 81
iTrader: 0 / 0%
i'm guessing you want the table set up, for the tables involved ie the product table and the cats table?

The cats table:

cat_id (autoincrement) primary
parent_id varchar index
cat_name varchar
description text

The Products Table

product_id (autoincrement) primary
name varchar
description text
cats varchar index

I've also got the cat tree table which i tried to use:

cat_id primary
parents index
children

Hopefully thats what you were after, if there are any errors or strange things with the tables, please don't ask me why i did them cause i prob won't know

Thanks
 
Reply With Quote
  #4  
Old 08-19-2004, 02:28 PM
littleFella's Avatar
littleFella littleFella is offline
Senior Member
 
Join Date: 06-20-04
Location: Ontario
Posts: 1,756
iTrader: 0 / 0%
That setup seems a little suspicious. I may not fully understand what kind of realtions you work with but the cursory look at your tables would suggest that you need to study relational databases and normalization a little more.

Anyway, you need 4 levels, so Iwould suggest the following tables (using an imaginary product):

TABLE_PPARENT
PARENT_ID (PK) // level 1
PARENT_DESCR

TABLE_CAT
CAT_ID (PK) // level 2
PARENT_ID (INDEX)
CAT_DESCR

TABLE_PRODUCT // level 3
PRODUCT_ID (PK)
CAT_ID (INDEX)
PRODUCT_DESCR

TABLE_COLOR // level 4
COLOR_ID (PK)
PRODUCT_ID (INDEX)
COLOR_NAME_DESCR

Takea careful look at these tables and notice how there is little redundant data and yet you can associate any item from any table with any other item frm any other table based on the relations among the 4 tables

Of course this would be done with a true RDBMS, which most of MySQL versions are not. I'm not sure how well MySQL implement SQL-92, which is critical to creating complex queries. If it's good enough then you're into some heavy duty SQL exercises, or you may want to opt for a less elegant, but much simpler solution. This would equire you to exectute a series of queries (instead of one) to get all the data you need. Sometimes, it wouldnt be such a bad idea anyway, especially if the number of records is substantial.
 
Reply With Quote
  #5  
Old 08-23-2004, 12:24 PM
robpicken robpicken is offline
Member
 
Join Date: 02-03-04
Location: Wales (for a while)
Posts: 81
iTrader: 0 / 0%
thanks for the info,

i did loads of searches and most of the decent info i could find reflected what you said about MYSQL and not been able to certain things. I think most of the info i found said that the best way was based around nodes.

My initial feeling when i read that was that it wasn't really worth dealing with nodes as i only needed 3 cat levels.

I actually spent a couple of hours rebuilding the queries like you suggested and have now got it to show the two and three level deep cats in the same drop down menu. Its still not 100% perfect, but am working on it.

Thanks for the help though, you highlighted some good points
 
Reply With Quote
Go Back   Webmaster Forum > Web Development > Coding Forum

Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
URL structure Forsh SEO Forum 6 09-22-2007 12:16 AM
Best Page Structure noob_0001 SEO Forum 5 09-14-2007 07:33 AM
URL Structure Red devil SEO Forum 17 07-28-2007 12:06 AM
Link structure Atom SEO Forum 13 10-16-2003 02:15 AM


V7N Network
Get exposure! V7N I Love Photography V7N SEO Blog V7N Directory


All times are GMT -7. The time now is 12:30 AM.
Powered by vBulletin
Copyright 2000-2014 Jelsoft Enterprises Limited.
Copyright © 2003 - 2018 VIX-WomensForum LLC