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
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
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
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
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 = 0This 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/
Re: [Jason] Related multi level category selection in user input
By ht1080z - December 4, 2011
Thank you, this is exactly what I'm looking for! [:)]
Perfect support as always!
Karls