Related multi level category selection in user input

5 posts by 3 authors in: Forums > CMS Builder
Last Post: December 4, 2011   (RSS)

By ht1080z - December 2, 2011

Hi,

I created 3 level category table [location]. How can i create mysql selection in step by step during user input from the same category?

1. user select data creation
2. enter title for the record
3. [region] select category where depth =0 (top level category)
4. [prefecture] select category from the above selected region
5. [city] select category from above selected perfecture
6. user enter more data for the record and save...

for the step 3. i can created mysql query:
SELECT name FROM `<?php echo $TABLE_PREFIX ?>location` WHERE depth = 0
but i cannot go deeper from here.

Please advise,

Re: [ht1080z] Related multi level category selection in user input

By Collin - December 3, 2011

Regions contain prefectures which contain cities, correct?

It looks like you have these all stored in 1 table? So the table would look like:

Region, Prefecture, City
=================

Region1, Prefecture1, City1
Region1, Prefecture1, City2
Region1, Prefecture1, City3
Region1, Prefecture2, City1
...etc.

I would recommend separating it into 3 different tables so it's more normalized and you don't have so much duplicate data.

But if we keep the current format, you can query like this:

SELECT city FROM location WHERE Region = 'Region1' AND Prefecture = 'Prefecture1'

I'm not sure how the depth comes into play. Perhaps you could clarify what your table looks like?

Re: [Collin] Related multi level category selection in user input

By ht1080z - December 3, 2011

Hi Collin,

Thank you for your reply!
Basically my category table contains the 3 sections (region, perfecture and cities) in different level (depth) and are related to each other like you describe:
Region1, Prefecture1, City1
Region1, Prefecture1, City2
Region1, Prefecture1, City3
Region1, Prefecture2, City1

If i create 3 separated table i think the same amount of data will be created because ill need to relate the regions-perfectures-cities again.
On php webpages i can manage the categories and filter to each level but i want to create mysql script to use in admin side when i input data in another table where the location selected level by level from the category table.
Also i installed the 'Add Apply Button' plugin, maybe after each level selection and apply i can somehow select from the current record the related subcategory...

or how 3 separated tables can help me? its to many data in every section, cannot manage without relation.

Any ideas? Please advise,
Karls

Re: [Jason] Related multi level category selection in user input

By ht1080z - December 4, 2011

Hi Jason,

Thank you, this is exactly what I'm looking for! [:)]

Perfect support as always!
Karls