need help with Virtual items in categories

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
itzikpel
Forum Newbie
Posts: 5
Joined: Sat Aug 27, 2005 6:18 pm

need help with Virtual items in categories

Post by itzikpel »

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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

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.

Code: Select all

SELECT * FROM categories WHERE parent_id = 0


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...

Code: Select all

SELECT * FROM categories WHERE parent_id = {(int)$_GET['id']}
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..
itzikpel
Forum Newbie
Posts: 5
Joined: Sat Aug 27, 2005 6:18 pm

Post by itzikpel »

thanks but the recursive queries is not the problem
the problem is with the Virtual items....
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I don't see the problem?

Code: Select all

SELECT * FROM items WHERE cat_id = {int($_GET['id']}
Assuming you keep your category id identical to the table storing your items
itzikpel
Forum Newbie
Posts: 5
Joined: Sat Aug 27, 2005 6:18 pm

Post by itzikpel »

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
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Just create a "many to many" tablle:

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)
);
(Assuming you have integer primary keys for both items and categories.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

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 :P
itzikpel
Forum Newbie
Posts: 5
Joined: Sat Aug 27, 2005 6:18 pm

Post by itzikpel »

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?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Two options:

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 (...);
or add a "type" field to the single table

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)
);
The second version uses the ability to use foreign keys, however.

I'd greatly prefer the first one, but its a personal opinion thing.
itzikpel
Forum Newbie
Posts: 5
Joined: Sat Aug 27, 2005 6:18 pm

Post by itzikpel »

can you direct me to read more about this method?
thanks
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

I guess do a search for "normalization", "database design", or "many to many relationships".

Its not really a "method" just one of the fundamentals of database design.
Post Reply