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]