Main
Index 		Search
Posts 		Who's
Online 		Log
In

Home: Products: CMS Builder:
Related multi level category selection in user input

 
 


ht1080z
User

Dec 2, 2011, 1:53 PM

Post #1 of 5 (646 views)
Shortcut
Related multi level category selection in user input Can't Post
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,
ht1080z


Collin
Novice


Dec 3, 2011, 10:56 AM

Post #2 of 5 (635 views)
Shortcut
Re: [ht1080z] Related multi level category selection in user input [In reply to] Can't Post
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:

Code
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?


ht1080z
User

Dec 3, 2011, 11:59 AM

Post #3 of 5 (631 views)
Shortcut
Re: [Collin] Related multi level category selection in user input [In reply to] Can't Post
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


Jason
Staff / Moderator


Dec 4, 2011, 8:56 AM

Post #4 of 5 (611 views)
Shortcut
Re: [ht1080z] Related multi level category selection in user input [In reply to] Can't Post
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:

Code
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:

Code
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 - Programmer 
interactivetools.com

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


ht1080z
User

Dec 4, 2011, 9:51 AM

Post #5 of 5 (608 views)
Shortcut
Re: [Jason] Related multi level category selection in user input [In reply to] Can't Post
Hi Jason,

Thank you, this is exactly what I'm looking for! Smile

Perfect support as always!
Karls