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

Filtering two fields to produce a table of contents

Codes here !

Moderators: macek, egami, gesf

Filtering two fields to produce a table of contents

Postby Joker » Tue Jun 24, 2003 4:01 am

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!
Joker
New php-forum User
New php-forum User
 
Posts: 6
Joined: Tue Jun 24, 2003 3:47 am
Location: Portsmouth, UK

Postby bezmond » Tue Jun 24, 2003 10:19 am

although not entirely sure what your asking, do you use 1 table or 2? :-/
User avatar
bezmond
Moderator
Moderator
 
Posts: 312
Joined: Sat Apr 05, 2003 4:33 am
Location: Mansfield, UK

Postby Joker » Tue Jun 24, 2003 10:59 am

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.
Joker
New php-forum User
New php-forum User
 
Posts: 6
Joined: Tue Jun 24, 2003 3:47 am
Location: Portsmouth, UK

Postby bezmond » Tue Jun 24, 2003 11:26 am

ok, you could try something like:
Code: Select all
$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
User avatar
bezmond
Moderator
Moderator
 
Posts: 312
Joined: Sat Apr 05, 2003 4:33 am
Location: Mansfield, UK

Postby Joker » Tue Jun 24, 2003 12:36 pm

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.
Joker
New php-forum User
New php-forum User
 
Posts: 6
Joined: Tue Jun 24, 2003 3:47 am
Location: Portsmouth, UK

Postby bezmond » Wed Jun 25, 2003 1:04 am

I'm correcting a couple of errors I made:

Code: Select all
$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; } }
User avatar
bezmond
Moderator
Moderator
 
Posts: 312
Joined: Sat Apr 05, 2003 4:33 am
Location: Mansfield, UK

Postby Joker » Wed Jun 25, 2003 2:51 am

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!
Joker
New php-forum User
New php-forum User
 
Posts: 6
Joined: Tue Jun 24, 2003 3:47 am
Location: Portsmouth, UK

Postby bezmond » Wed Jun 25, 2003 3:19 am

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
User avatar
bezmond
Moderator
Moderator
 
Posts: 312
Joined: Sat Apr 05, 2003 4:33 am
Location: Mansfield, UK


Return to mySQL & php coding

Who is online

Users browsing this forum: Yahoo [Bot] and 2 guests

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