I've been working on something like this since about September/October.
It's not too hard, but it did take more coding than I thought it would.
Now, there's a couple of ways you could do this. I chose the cheap (in
terms of the number of DB tables) way out.
The way I have it set up is this. You have certain categories (Main
categories) that have a parent category of ',0,' (I found I had to
enclose the categoryIDs in commas to prevent accidentally selecting a
category that matched any of the digits of any other categoryID in the
same order) then I have the subcategories which have multiple parents.
The form to add a new product gathers all the information I need --
category name/title, description, whether it should be displayed and the
parent category(s), with the parent categorys being displayed in the
form of checkboxes. This lets the admin select multiple categories for
the parent (we have a similar set up for the products since a product
can belong in multiple categories).
The checkboxes themselves are named p_id (I think) and are an array,
thus I print the name value as NAME='p_id[ . $cid . ]', where $cid is
the variable containing the category ID (I actually have it in a
recordset array, of course). Then, it just prints a p_id checkbox for
each possible parent (obviously when editing the category ID of the one
being edited isn't printed). When editing, those categories to which
the category has as parents are checked.
When the form is submitted, it puts the posted variable values (the
array of checkboxes) into a comma-delimited string. Whenever I query
the database for subcategories, unfortunately, I have to make sure I
remember to check for parent LIKE '%,ID,%' so I don't pick up one I
don't want (i.e. if I were to say LIKE '%ID%' and I was looking for ID =
20 and had that and ID =200, subcategories with parents of category 20
and 200 would end up returned). That's how I handled it. I'm probably
going to upgrade to this next option later on, though.
Create a table for the categories and then a table that determines a
category's children. Basically, it's just two fields: categoryID and
parent -- with both fields making up the primary key. Then, for each
parent category they checked on the form, insert the new category's ID
in the categoryID field and the parent's ID into the parent field.
That's probably the best way since it would probably cut down on space
(I use a TEXT field currently, whereas the second option could be done
with two MEDIUMINT fields).
If you want some code samples, e-mail me privately. If you want to see
the script (version 1.5.23 -- we've got 2.0 almost finished up now) in
action, we've got one of our clients playing guinea pig at
www.4dacres.com.
__
Peter Adams[EMAIL PROTECTED]
Web Developer http://www.interkan.net
InterKan.Net, Inc. (785) 565-0991
-Original Message-
From: Corey Eiseman [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 28, 2002 2:56 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] storing and retrieving arrays in mysql
Hi folks, I've got a question hopefully someone can shed some
light on for me.
I'm building an online store for a client, and one of the
things he wants is to organize his products into categories
and subcategories.. not so unusual, but the kicker is he
wants to be able to associate a subcategory with more than
one category.
I was thinking that I should be able to easily serialize an
array of cat_IDs and store it, but my concern is that this
will sacrifice a great deal of flexibility when retrieving
the data. For instance when I want to get the subcategories
in a single category, I would pretty much have to select ALL
the rows in the subcategory table, unserialize the category
array for each row, and then check each to see if the cat_ID
is in the array..?
That just feels inefficient to me, and I'm almost certain I
must be overlooking something simpler..
Also, I don't think I can use a SET data type because I want
to be able to add values to the set (categories) dynamically
in the future. But maybe I'm wrong and there is a way to do that...?
Anyway I thought I'd throw it to those more experienced than
me before I started coding. Anyone have any ideas?
Thanks in advance,
Corey Eiseman
Infinite Orange Incorporated
http://infiniteorange.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail:
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
