need help with Virtual items in categories
Moderator: General Moderators
need help with Virtual items in categories
Hello,
I write some code in PHP & MYSQL that involved with unlimited sub-categories (recursive).
Now I can make a complex tree of categories and assign items for each category.
But now I want to improve the code to work with virtual items, I dont now if this is the right name for what I want, so I explain :
Virtual items - allow you to assign a single item to unlimited number of categories/sub-categories
Also important question is after the Virtual items code works - how to pull data based on categories for queries.
I wolud thank alot if someone could help or direct me to some inforamtion or concept articles about this subject.
thanks
I write some code in PHP & MYSQL that involved with unlimited sub-categories (recursive).
Now I can make a complex tree of categories and assign items for each category.
But now I want to improve the code to work with virtual items, I dont now if this is the right name for what I want, so I explain :
Virtual items - allow you to assign a single item to unlimited number of categories/sub-categories
Also important question is after the Virtual items code works - how to pull data based on categories for queries.
I wolud thank alot if someone could help or direct me to some inforamtion or concept articles about this subject.
thanks
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
I would have a single table named categories with the structure along the lines of
id (int(5) (primary key, auto increment)
category (text, whatever)
parent_id (int(5)) (default 0)
Now when your page is first accessed you would run a query checking for categories where the parent_id is 0, because it is not a subcategory.
Now when you want to access a subcategory --
On your page you should access individual categories by the query string, so now we have to modify our query a little bit...
Notice the (int) which forces the input to become an int, just incase the user tampers with the query string for some unknown reason..
Now you see how it works? The subcategories can go on forever..
id (int(5) (primary key, auto increment)
category (text, whatever)
parent_id (int(5)) (default 0)
Now when your page is first accessed you would run a query checking for categories where the parent_id is 0, because it is not a subcategory.
Code: Select all
SELECT * FROM categories WHERE parent_id = 0Now when you want to access a subcategory --
On your page you should access individual categories by the query string, so now we have to modify our query a little bit...
Code: Select all
SELECT * FROM categories WHERE parent_id = {(int)$_GET['id']}Now you see how it works? The subcategories can go on forever..
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
I don't see the problem?
Assuming you keep your category id identical to the table storing your items
Code: Select all
SELECT * FROM items WHERE cat_id = {int($_GET['id']}let say this is my categories tree:
----> (1).Computers & Internet
------------> (2) Network (parent id = 1)
------------> (3) Software (parent id = 1)
------------> (4) Hardware (parent id = 1)
now, I create new Item (not category)
the new item called "Modem", And I need to assign him to the Categories "Network" + "Hardware"
my question is how to create a single item with multipele parents ?
I'm looking for a method/concept and not just a couple of code lines
thanks
----> (1).Computers & Internet
------------> (2) Network (parent id = 1)
------------> (3) Software (parent id = 1)
------------> (4) Hardware (parent id = 1)
now, I create new Item (not category)
the new item called "Modem", And I need to assign him to the Categories "Network" + "Hardware"
my question is how to create a single item with multipele parents ?
I'm looking for a method/concept and not just a couple of code lines
thanks
Just create a "many to many" tablle:
(Assuming you have integer primary keys for both items and categories.
Code: Select all
CREATE TABLE item_categories (
itemid INT NOT NULL REFERENCES items ON UPDATE CASCADE ON DELETE CASCADE,
catid INT NOT NULL REFERENCES categories ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (itemid, catid)
);- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
it's called a many-to-many relationship. You'll need a table to create the cross linking between n-items and m-categories. Typical layout is simply two fields: category_id and item_id. Selection becomes a little bit more complicated, but allows you to link any item to any number of categories.
[edit]wow, I took far too long to write this post
[edit]wow, I took far too long to write this post
thanks,
that's help but I think I have another problem,
in my code I have only one table of categoried (that's the way I need it) and 3 tables of items.
like this:
table: Categories
table: Gallery (items)
table: Articles (items)
table: Events (items)
I have integer primary keys for all of this tables.
but primary keys are not helping because duplicate keys (like in articles and events) are still exist..
so how to go on from here?
that's help but I think I have another problem,
in my code I have only one table of categoried (that's the way I need it) and 3 tables of items.
like this:
table: Categories
table: Gallery (items)
table: Articles (items)
table: Events (items)
I have integer primary keys for all of this tables.
but primary keys are not helping because duplicate keys (like in articles and events) are still exist..
so how to go on from here?
Two options:
1. Have one m:m table for each class of things:
or add a "type" field to the single table
The second version uses the ability to use foreign keys, however.
I'd greatly prefer the first one, but its a personal opinion thing.
1. Have one m:m table for each class of things:
Code: Select all
CREATE TABLE gallery_categories (...);
CREATE TABLE articles_categories (...);
CREATE TABLE events_categories (...);Code: Select all
CREATE TABLE item_categories (
catid INT NOT NULL REFERENCES categories ON UPDATE CASCADE ON DELETE CASCADE,
type TEXT NOT NULL,
itemid INT NOT NULL
PRIMARY KEY(catid,type,itemid)
);I'd greatly prefer the first one, but its a personal opinion thing.