Menu
Community /Pin to ProfileBookmarkCategories
subCategory
@NogDogJun 01.2010 — #I might try something like:
The above is untested and the HTML it outputs could be made much more semantically meaningful (perhaps nested UL lists?), but hopefully it gives you the idea.@NogDogJun 01.2010 — #Oops. Try removing the commas at the end of these lines (in the FROM clause):
@NogDogJun 01.2010 — #I think you want to replace...
However, I've got a suspicion this won't be quite what you want, as I think it will list all sub-categories as categories without sub-categories (while also listing them correctly as sub-categories). I might have to think about that a bit....@NogDogJun 02.2010 — #OK, I broke down and created the DB tables, did some tweaking and testing, and came up with...
It output the following:
And here's the test data I used:
@NogDogJun 02.2010 — #I was working under the impression that it was only a two-level structure. If it is going to have [i]n[/i] levels, then I'd probably scrap this data design and go for a "nested set" design. (Scroll down to the "Nested Set Model" heading on [url=http://dev.mysql.com/tech-resources/articles/hierarchical-data.html]this page[/url] .)@NogDogJun 02.2010 — #
For me, part of the "paying forward" is teaching myself stuff while trying to answer other people's questions. ?
Categories & related categories
Hi all,
Cheers for reading my post. I’ve got a list of categories in my main database table, and any of these can be related categories of another (1 to many).
So, i’ve got 2 tables:
Categories
———-
———-
categoryID
categoryName
subCategory
————
————
subCategoryID
categoryID
relatedCategoryID
and i would need to output as:
category 1
– category 2
– category 3
category 2
– category 6
– category 8
I’ve tried a few SQL queries, but i can’t seem to get the right output.
Can anyone please provide help or guidance?
Thanks. Martin.
Sign in
to post a comment14 Comments(s) ↴
0
@NogDogJun 01.2010 — #I might try something like:[code=php]
$sql = "
SELECT
c1.categoryName,
IFNULL(c2.categoryName, '')
FROM
Categories AS c1,
LEFT JOIN subCategory AS sc ON sc.categoryID = c1.categoryID,
LEFT JOIN Categories AS c2 ON c2.categoryID = sc.subCategoryID
GROUP BY c1.categoryID
ORDER BY c1.categoryName, c2.categoryName
";
$result = mysql_query($sql) or die(mysql_error());
$category = "";
while($row = mysql_fetch_row($result))
{
if($row[0] != $category)
{
$category = $row[0];
echo $row[0] . "<br />n";
}
if(!empty($row[1]))
{
echo "- " . $row[1] . "<br />n";
}
}
[/code]The above is untested and the HTML it outputs could be made much more semantically meaningful (perhaps nested UL lists?), but hopefully it gives you the idea.
0
@NogDogJun 01.2010 — #Oops. Try removing the commas at the end of these lines (in the FROM clause):<i>
</i> Categories AS c1,
LEFT JOIN subCategory AS sc ON sc.categoryID = c1.categoryID,
0
@NogDogJun 01.2010 — #I think you want to replace...<i>
</i>LEFT JOIN Categories AS c2 ON c2.categoryID = sc.subCategoryID
<i>
</i>LEFT JOIN Categories AS c2 ON c2.categoryID = sc.relatedCategoryID
However, I've got a suspicion this won't be quite what you want, as I think it will list all sub-categories as categories without sub-categories (while also listing them correctly as sub-categories). I might have to think about that a bit....
0
@NogDogJun 02.2010 — #OK, I broke down and created the DB tables, did some tweaking and testing, and came up with...[code=php]
$sql = "
SELECT
c1.categoryName,
IFNULL(c2.categoryName, '') AS subName
FROM
Categories AS c1
LEFT JOIN SubCategory AS sc ON sc.categoryID = c1.categoryID
LEFT JOIN Categories as c2 ON sc.relatedCategoryID = c2.categoryID
WHERE c1.categoryID NOT IN (SELECT relatedCategoryID FROM SubCategory)
ORDER BY c1.categoryName, c2.categoryName
";
$result = mysql_query($sql) or die(mysql_error());
$category = '';
while($row = mysql_fetch_row($result))
{
if($row[0] != $category)
{
$category = $row[0];
echo $row[0]."<br />n";
}
if($row[1] != '')
{
echo "- ".$row[1]."<br />n";
}
}
[/code]It output the following:
<i>
</i>Top A
- Sub A 1
- Sub A 2
Top B
- Sub B 1
- Sub B 2
Top C
And here's the test data I used:
<i>
</i>-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 01, 2010 at 08:20 PM
-- Server version: 5.1.36
-- PHP Version: 5.2.9-2
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: <span><code>tester</code></span>
--
-- --------------------------------------------------------
--
-- Table structure for table <span><code>categories</code></span>
--
CREATE TABLE IF NOT EXISTS <span><code>categories</code></span> (
<span><code>categoryID</code></span> int(11) NOT NULL AUTO_INCREMENT,
<span><code>categoryName</code></span> varchar(255) NOT NULL,
PRIMARY KEY (<span><code>categoryID</code></span>)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- Dumping data for table <span><code>categories</code></span>
--
INSERT INTO <span><code>categories</code></span> (<span><code>categoryID</code></span>, <span><code>categoryName</code></span>) VALUES
(1, 'Top A'),
(2, 'Top B'),
(3, 'Sub A 1'),
(4, 'Sub A 2'),
(5, 'Sub B 1'),
(6, 'Sub B 2'),
(7, 'Top C');
-- --------------------------------------------------------
--
-- Table structure for table <span><code>subcategory</code></span>
--
CREATE TABLE IF NOT EXISTS <span><code>subcategory</code></span> (
<span><code>subCategoryID</code></span> int(11) NOT NULL AUTO_INCREMENT,
<span><code>categoryID</code></span> int(11) NOT NULL,
<span><code>relatedCategoryID</code></span> int(11) NOT NULL,
PRIMARY KEY (<span><code>subCategoryID</code></span>),
KEY <span><code>categoryID</code></span> (<span><code>categoryID</code></span>,<span><code>relatedCategoryID</code></span>)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table <span><code>subcategory</code></span>
--
INSERT INTO <span><code>subcategory</code></span> (<span><code>subCategoryID</code></span>, <span><code>categoryID</code></span>, <span><code>relatedCategoryID</code></span>) VALUES
(1, 1, 3),
(2, 1, 4),
(3, 2, 5),
(4, 2, 6);
0
@NogDogJun 02.2010 — #I was working under the impression that it was only a two-level structure. If it is going to have [i]n[/i] levels, then I'd probably scrap this data design and go for a "nested set" design. (Scroll down to the "Nested Set Model" heading on 0
@NogDogJun 02.2010 — #That's absolutely fine, you've most certainly put me in the right direction with this. Thanks for all your help, it's nice to see people who go out of their way to help others. I'll pay it forward when i can on these forums.
Cheers. Martin.[/QUOTE]
For me, part of the "paying forward" is teaching myself stuff while trying to answer other people's questions. ?