Admin Panel - Populate dropdown based on previous drop down selection
6 posts by 2 authors in: Forums > CMS Builder
Last Post: November 26, 2013 (RSS)
By gadefgaertgqe - November 21, 2013 - edited: November 22, 2013
This might be a bit tricky to explain but here goes!
I have a large drop down of a series of bike MAKES that looks like this:
A-1|Aprilia
A-2|Benelli
A-3|BMW
...and so on
And I have a very large list of MODELS related to the MAKES listed above (The example below shows some of the Aprilia bike models:
A-1-1|125Strada
A-1-2|AF1
A-1-3|Amico
...and so on
I am trying to filter the list of bike models based on the selection made in the bike MAKES dropdown.
So I created another list called FILTERED MODELS, set it to "Get Options From MySQL Query" and configured it to use Advanced Filter: Refresh list when this field changes: MAKES
I am now trying to write the SQL query to only show the related MODELS to the chosen MAKE, but with little success.
Am I on the right path? Any advice welcome!
Thanks!
EDIT: I've tried to highlight the key names of what I am talking about. It looks like I am shouting lol! Also here is an image to clarify where I am doing this:
By Daryl - November 22, 2013
Hi 8bitPixel,
Is your MAKES and MODELS list fields came from a separate sections and added to "BIKES(?)" section using "Get options from database (advanced)"?
If yes, you should make sure that the "FILTERED MODELS" Get Options From MySQL Query query is correct. See example below:
SELECT num, model_name
FROM `<?php echo $TABLE_PREFIX ?>models_section` WHERE makes_num = '<?php echo $ESCAPED_FILTER_VALUE ?>'
If not, for the Advanced Filter to work, MAKES list items and MODELS list items should have their own nums by adding them to their own section so we can associate them by record num.
And MODELS section should have a related MAKES dropdown so a record will be associated to a certain MAKES record.
You can also check out this thread which is pretty similar to what you want to achieve: http://www.interactivetools.com/forum/forum-posts.php?postNum=2218572
Cheers!
PHP Programmer - interactivetools.com
By gadefgaertgqe - November 25, 2013 - edited: November 25, 2013
Hi Daryl,
Thanks for the reply. Much appreciated.
To explain our setup in more detail, we have a Used Bike Section. In that section we currently have 2 lists. The first list is a list of all Makes (MAKE). The second list is the full list of all models for all makes(MODEL).
The data structure is such that the list of models could be associated with the relevant make. So for example:
MAKE (List):
MB-230|Peugeot
MODEL (List):
MB-230-0|Other
MB-230-1|Blaster
MB-230-2|Buxy
MB-230-3|Elyseo
MB-230-4|Elystar
MB-230-5|Fox
MB-230-6|Geo
MB-230-7|Geopolis
MB-230-8|JetC-Tech
MB-230-9|JetForce
MB-230-10|Looxor
MB-230-11|Ludix
MB-230-12|Metal-X
MB-230-13|Satelis
MB-230-14|Speedfight
MB-230-15|SV
MB-230-16|TKR
MB-230-17|Trekker
MB-230-18|V-Clic
MB-230-19|Vivacity
MB-230-20|X-Fight
MB-230-21|XPS
MB-230-22|XR6
So I was thinking that we would need an additional list called FILTERED MODELS that is populated based on the chosen MAKE, to make choosing the correct model easier instead of going through the massive list of models for all makes.
I have attached some images as well of the current setup:
I have tried to make sense of your suggestion but struggling with it as MySQL is not my strong point and I only have to deal with CMSB every now and again.
If you previous suggestion still stands I will keep trying to make sense of it. However any further advise would be very welcome!
By the way I have tried looking at the other post you linked too, but Iam unable to view images in other posts. Anyone else got this problem?
Thanks again!
Paul
By Daryl - November 25, 2013 - edited: November 25, 2013
Hi Paul,
Thanks for giving us more details. It looks like your MAKE and MODEL list fields only uses the List Option "Use options listed below". As I mentioned before, we need the Make and Model to have their own sections.
I've created an example with screenshots and hopefully it will help you on setting it up.
First, I've created a "Make" section with just a title field.
Next, I've created the "Model" section with title and "Make" drop down list field. The "Make' field came from the "Make" section. See "model_makeField.png".
Then I've added sample Model records, see "model_addRecord.png". In the screenshot, we're associating model "Blaster" to "Peugeot".
After that, I've created the "Bikes" section with title, make, and model fields. The List options for the Make field of Bikes section is the same as the Model section's Make field: Get options from database (advanced).
Finally, for the model field, I used the List options "Get options from MySQL query (advanced)" that uses the advanced filter functionality. See "bike_modelField.png".
MySql query:
SELECT num, title FROM `<?php echo $TABLE_PREFIX ?>model` WHERE make = '<?php echo $ESCAPED_FILTER_VALUE ?>'
The $ESCAPED_FILTER_VALUE is the record num of the selected item in the Make field of Bikes section. So the query means select/load the models where their make is equals to the selected make value. See "bike_addRecord1.png" and "bike_addRecord2.png.
If you're using the list options value (ex. MB-230-1 for Blaster) in the front end, you might need to change it to Blaster's model section record num.
Hope this helps!
Cheers!
PHP Programmer - interactivetools.com
By gadefgaertgqe - November 26, 2013 - edited: November 26, 2013
Hi Daryl,
Now I get it! Great overview!
So that works for small lists however I am dealing with 119 makes and 842 models and the list changes frequently. Now I understand the method you showed me in the previous post, I realise that this is not practical due to the sheer size of the data.
I will explore today to see if there is a way to match each Model with the relevant make because of the ID structure. For example:
Peugeot is MB-230
Peugeot Blaster is MB-230-1
Any pointers on this will be welcome. If I work it out before then I'll post if here for others to see.
Solved!
Sometimes when you learn something it's good to go back to the beginning and review the process. The data is actually being passed to me as a CSV, and I remembered I bought the CSV import plug-in ages ago, so after upgrading CMSB to the latest version (you guys deserve the ££ after all your help!), I created a brand new table that contains all the bike info in various columns, then using what Daryl taught me, I managed to modify the queries etc so that I have the correct drop down lists showing the right info dependant on selection.
Thanks guys, I've been working on this project day and night since last week, and your assistance has been fantastic.
Paul