Filtering two fields to produce a table of contents

A

Anonymous

Guest
Hi,

I have a database setup which contains a table called "categories". Initially there was only one field in it (name) and I had my PHP get each category name, produce a link with a reference to the category and that was it. There was no need to worry about duplicate entries, as there would be none. BUT now things have got a little more complicated and as I am relatively new to using PHP and MySQL, I'm struggling.

I now have a second field in the 'categories' table called 'name2'. This field shows a sub-category (if any) of the category in 'name'. So, for example, my table could look like this:

[name]----|----[name2]
CATEGORY
CATEGORY------SUB1
CATEGORY------SUB2

And so I therefore want to produce a TOC like:

CATEGORY
->SUB1
->SUB2

(Dashes are showen here to make the display more comprehensible!)

I'm finding this a bit of a mind bender and would appreciate any help in terms of psuedocode, approach to take, or even the code if you've got nothing better to do.

Thanks!
 
although not entirely sure what your asking, do you use 1 table or 2? :-/
 
I am using just the one table called "categories", which has 2 fields in it 'name' and 'name2' ('name' being the MAIN category and 'name2' being the subcategory of 'name' should it be required (it is otherwise NULL)).

There is a 2nd table which has the same categories in it, but they are repeated for each item and so for simplicity, I don't want to worry about that at the moment. I just want to get this single table sorted.

So, what I want, is my php to automatically create a table of contents. All the entries from field1 ('name') create the main category heading and then should there be any subcategories of this main category, found in field2 ('name2'), they are placed under the main category heading, slightly indented.

Hope that's a little clearer.
 
ok, you could try something like:
Code: 
$query = "SELECT * FROM tablename WHERE name2=''";
#this get's us the main category name..
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$category = $row["name"];
echo $category; #there's our main heading
$query2 = "SELECT * FROM tablename WHERE name='$category'";
$result2 = mysql_query($query) or die(mysql_error());
while ($row2 =  mysql_fetch_array($result)) {
$sub = "  "; #that gives us a 2 space indentation at the beginning
$sub .= $row["name2"];
echo $sub; } }

try that on for size, I think it should work exactly as you want :)

Andrew
 
Thanks for that. I understand exactly how it works... or how it SHOULD work! It's not returning any errors, but the first SQL statement isn't doing what it should as it's never getting into the while loop. I am playing around with it though and should have it sorted soon.

Thanks very much for your help.
 
I'm correcting a couple of errors I made:

Code: 
$query = "SELECT * FROM tablename WHERE name2=''"; 
#this get's us the main category name.. 
$result = mysql_query($query) or die(mysql_error()); 
while ($row = mysql_fetch_array($result)) { 
$category = $row["name"]; 
echo $category; #there's our main heading 
$query2 = "SELECT * FROM tablename WHERE name='$category'"; 
$result2 = mysql_query($query2) or die(mysql_error()); 
while ($row2 =  mysql_fetch_array($result2)) { 
$sub = "  "; #that gives us a 2 space indentation at the beginning 
$sub .= $row2["name2"]; 
echo $sub; } }
 
That's cool, but after a bit of head scratching late last night, I saw the subtle errors and got it working fine! I was considering posting the modified code back up for others, but then I thought you might be offended - but you've done it now anyway!

The solutions are always so obvious when someone else gives them to you! Think this has got me into programming mode now though and things are looking rosey. Touch wood.

Cheers!
 
hehe - it's a mistake I make frequently - if I'm running 2 queries at once, I always forget to put the number on the other one, or just get them mixed up completely :(

Andrew
 
You must log in or register to reply here.
Back
Top