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: [ht1080z] Related multi level category selection in user input

By Jason - December 4, 2011

Hi Karls,

What you can do is set up 3 fields in your new section called region, prefecture, and city.

In the first one, you can use make it a list field. Select "Get Options From MySQL query(advanced)". Use this query:

SELECT num, name
FROM `<?php echo $TABLE_PREFIX ?>locations`
WHERE depth = 0

This will pull in records with a depth of 0.

Next, create a prefecture field. Set this up as a list and using a MySQL query. Under "Advanced Filter " select the region field. Then use this query:

SELECT num, name
FROM `<?php echo $TABLE_PREFIX ?>locations`
WHERE parentNum = '<?php echo $ESCAPED_FILTER_VALUE;?>>'

This will refresh the list each time a new region is selected. Then it will repopulate prefectures that are under the selected region.

You can use this same process to create a city field.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/