Related multi level category selection in user input

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?

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

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.

Hi Jason,

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

Perfect support as always!
Karls