Page 1 of 1
Sub-categories
Posted: Wed Jun 01, 2005 4:36 am
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
How to do that?
Thanks
Posted: Wed Jun 01, 2005 5:22 am
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.
Posted: Wed Jun 01, 2005 5:38 am
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
Posted: Wed Jun 01, 2005 5:45 am
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
Posted: Wed Jun 01, 2005 5:52 am
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() ???
Posted: Wed Jun 01, 2005 5:58 am
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>";
}
}
Posted: Wed Jun 01, 2005 6:01 am
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.
Posted: Wed Jun 01, 2005 6:51 am
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.
Posted: Wed Jun 01, 2005 8:21 am
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'];
}
Posted: Wed Jun 01, 2005 11:42 pm
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!
Half-solved
Posted: Sat Jun 04, 2005 11:25 pm
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