Sub-categories

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Sub-categories

Post by Caroline »

I have a table like this

Code: Select all

CREATE TABLE cats (
id INT auto_increment NOT NULL,
catname VARCHAR (150) NOT NULL,
parentcat VARCHAR (150) NOT NULL default '0',
primary key (id)
);
Suppose that we have 5 categories in which 2 and 3 are sub-categories of 4.

Suppose that we have inserted these records

Code: Select all

catname = 1; parentcat = 0
catname = 2; parentcat = 4
catname = 3; parentcat = 4
catname = 4; parentcat = 0
catname = 5; parentcat = 0
Then i want to echo them to the screen like this

Code: Select all

1
4
-- 2
-- 3
5
How to do that?

Thanks
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

I dont think you can do that directly with a MySQL query - you'll need to code it after fetching the result and display accordingly - not sure abt MySQL 5 though.
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Post by Caroline »

Yes, with mysql_query, i can select right records. But how to echo them to the screen is a true problem.

I've had a query to select main categories and echo them to the screen like this

Code: Select all

$cats = mysql_query ("select * from table where parentcat = 0 order by id asc");
while ($cat = mysql_fetch_array($cats)) {
echo "$cat[catname]<br>";
}
from here... it's confused...

How to have

Code: Select all

1
4
-- 2
-- 3
5
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

To be honest, i think you have your table set up wrong.

I would have had 2 tables...1 for the main sections, and 1 for the sub-sections.

This would have been easier.

Then a query along the lines of

Code: Select all

SELECT s.id, s.section_name, ss.subsectionid, ss.subsection_name
FROM database.sections AS s
LEFT OUTER JOIN database.subsections AS ss
ON s.id = ss.subsectionid
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Post by Caroline »

Assume that we have 2 tables as in your suggestion
how to echo them to the screen?

For example:

Code: Select all

if ($parentcat !== '0') && ($parentcat == '4') {
echo "-- $cat[catname]<br>";
}
right?

but how to insert that code into the while() ???
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

something like

Code: Select all

while ($cat = mysql_fetch_array($cats)) {

    if ($parentcat !== '0') {

        echo "-- ".$cat[catname]."<br>";

    } else {

        echo $cat[catname]."<br>";

    }

}
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

If you have more subcategories under a sub-category then it becomes a tree structure and you'll need a recursive function for that.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Code: Select all

<?php
Display_Cat_Column(0);
?>
<?php
function Display_Cat_Column($CatID)
 {
        $Res = mysql_query("SELECT * FROM cats WHERE parentcat='$CatID'");
        if (mysql_num_rows($Res) == 0) return;
        echo '<ul>';
        while($Row = mysql_fetch_array($Res))
         {
                echo '<li>'.$Row['catname'].'</li>';;
                Display_Cat_Column($Row['catname']);
         }
        echo '</ul>';
 }
?>
If the tree structure is too expansive then a recursive function is not preferred.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

"tree in sql" is a good subject for a websearch. you will see there are some very nice solutions which don't require you to make recursive queries etc...


but you can solve your problem as following: (untested)

Code: Select all

SELECT * 
FROM cats
ORDER BY parentcat ASC, catname ASC

Code: Select all

$parent = '';
while ($row = myqsl_fetch_assoc($result))
{
  if ($parent != $row['parentcat'])
  {
    echo $row['parentcat'];
    echo "<br>";
  }
  if ($row['catname'] != 0)
  {
    echo "-- ";
    echo $row['catname'];
    echo "<br>";
  }
  $parent = $row['parentcat'];
}
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Post by Caroline »

The request on me is a little more complicated (or more simple as you thought)

Please see real matter here:

http://www.hoconrua.com/subcat/

Thanks, guru!
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Half-solved

Post by Caroline »

I've found a way to solved the problem and it is here

http://hoconrua.com/subcat/subcat.php

A little more try still needed
Post Reply