Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I need some help, im writing a shopping cart script which is going to have tons of ctaegiries, like ctagories inside categories, so in the database would I have to store them like this 
category1
category1|subcat1
category1|subcat1|subcat1
category1|subcat1|subcat2
category1|subcat1|subcat3
category1|subcat2
category1|subcat2|subcat1
category2

[download]
If I seperated them like how would I get all the categories in category1|subcat1, so it would only get 
category1|subcat1|subcat1
category1|subcat1|subcat2
category1|subcat1|subcat3

[download]

Replies are listed 'Best First'.
Re: MySQL Answer
by Nightblade (Beadle) on Jul 19, 2002 at 13:14 UTC
    SELECT * FROM table WHERE title LIKE "category1|subcat1|%" ORDER BY title;
[reply]
      Thank you, I was thinking of that
[reply]
        what if I just wanted to get the 2 categories
[reply]
Re: MySQL Answer
by screamingeagle (Curate) on Jul 19, 2002 at 17:02 UTC
    ummm...there are better ways to store the data .The database structure you currently have violates the First Normal Form of database design by saving category and subcategory in a single field. Here's a nice article about database normalization (Normalization)
    I would recommend having a Category Table and a Subcategory table , the link between the both of them being the category ID , which would be the primary key of the Category table and the foreign key of the SubCategory table. Using such a strucure would mean that you would not need to use any parsing to get the data you want; all you would need is a simple SQL : 
    select c.*, sc.* 
from category c, subcategory sc
where c.categoryID = sc.categoryID
and categoryID = <the category you want>

    [download]
    This would get you all the data related to the category you're looking for as well as all the subcategories realated to that specific category
[reply]
[d/l]
Re: MySQL Answer
by Anonymous Monk on Jul 19, 2002 at 14:30 UTC
    No I mean what If I just wanted to get ctaegory1, and category2.
[reply]

Back to Seekers of Perl Wisdom