What you want to represent is a tree data structure. In fact you use the same database scheme to represent a two-level tree as a n-level tree. The top level nodes (the main categories) have no parent, whereas all other sub-nodes will have a parent. Also, the same schema can be used to represent a one-to-one relationship between a sub-category and its parent or a one-to-many mapping. That is, a sub-category may be related to only one main category or may be cross-referenced to many main categories. Either way, the tables below should do the job.
Same thing with the articles. Does an article belong to only one category (sub or main) or many categories? Again the below will handle either case.
Code:
CREATE TABLE Category (
cat_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(250),
parent_id INT UNSIGNED,
PRIMARY KEY(cat_id, parent_id)
);
CREATE TABLE Article (
art_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(250),
... whatever else ...
);
CREATE TABLE Article_Category (
art_id INT NOT NULL,
cat_id INT NOT NULL,
PRIMARY KEY(art_id, cat_id)
);
// insert a main category 'foo' (parent_id will default to NULL)
INSERT INTO Category SET name='foo'
// insert a sub-category 'bar' of main category parent = 'foo'
INSERT INTO Category SET name='bar', parent='foo'
// insert an article
INSERT INTO Article SET ...
// Relate article 123 to Category 345
INSERT INTO Article_Category SET art_id=123, cat_id=345
// find all Sub-categories of Category 'foo'
SELECT * FROM Category WHERE parent_id='foo'
// find all Articles associated with sub-category 'bar'
SELECT * FROM Article_Category WHERE cat_id='bar'
A bit of application logic
PHP Code:
// lookup category 'Zoot' and find its sub-categories
$cat = 'Zoot';
$sql = "SELECT cat_id FROM Category WHERE parent_id='$cat'";
$result = mysql_query($sql);
// if there are one or more sub-categories print them out
if ( mysql_num_rows($result) ) {
//extract each row from the result set and print out "name"
while ( $row = mysql_fetch_array($result) {
echo $row["name"], '<br />';
}
// else if the no sub-cats then look up related articles and print them out
} else {
$sql = "SELECT Article.name AS artName
FROM Article_Category, Article
WHERE Article.art_id = Article_Category.art_id
AND cat_id = '$cat'";
$result = mysql_query($sql);
// if there are no articles print a message
if ( ! mysql_num_rows($result) {
echo 'sorry no articles';
} else {
while ($row = mysql_fetch_array) {
echo $row["artName"], '<br>';
}
}
}
that should be a bit of a start for you.
). Each article can be held in either a main category (where no sub categories exist), or a sub category.
{/edit}
...
Bookmarks