7 Replies Latest reply on Nov 26, 2008 3:06 PM by drewread

    ADDT Dependant Dropdowns 3 deep...

      Hello,
      I'm trying to get 3 drop down list's to work together, basically i have 1. Vehicle Type list 2. Make List 3. Model List. My database has a join table (type_make_model) that basically joins every modelID to a makeID to TypeID.

      So the flow would be Select Vehicle Type -> populates Make List -> Select a Make -> populates the Model list

      Do i need to create 2 separte DB queries? 1 that selects the Makes based on the vehicle type selected and 1 that selects the models based on the Make and Vehicle Type selected???

      I can get the Makes List to populate no problem when a Vehicle Type is selected, the problem comes in with the 3rd drop downlist.

      Juat wondering is there any example showing how to accomplish this type of scenerio?

      Thanks,
      Cory
        • 1. Re: ADDT Dependant Dropdowns 3 deep...
          Günter Schenk Level 4
          Hi Cory,

          that´s indeed extremely interesting, but I fear that so far noone seems to have done that with ADDT -- or if so, they just keep it to themselves :-)

          -----
          Do i need to create 2 separte DB queries?
          -----

          yes, I think so, all the more you already need 2 separate recordsets for the regular dependant dropdown menus.

          Well, rather than trying that myself, please tell me if you´re inclined to do some testing on my behalf :-)

          If yes, here´s what I´d try to do:

          1. add that 3rd recordset to the form that´s already containing the regular dependant dropdown

          2. somewhere in the doc´s head you should see a PHP fragment which contains something like this:

          //begin JSRecordset
          $jsObject_Recordset2 = new WDG_JsRecordset("Recordset2");
          echo $jsObject_Recordset2->getOutput();
          //end JSRecordset

          add another instance of that below and have it reference your new recordset, like this:

          //begin JSRecordset
          $jsObject_newrecordsetname = new WDG_JsRecordset("newrecordsetname");
          echo $jsObject_newrecordsetname->getOutput();
          //end JSRecordset

          3. assuming your form already provides e.g. a text input field (or a static menu) for that very column that´s supposed to be rendered as your 3rd dropdown menu:

          a) do away with that static form element and replace it with an instance of ADDT´s dependant dropdown code -- copy/paste the existing instance, that´s in the following named "2nd_menu"

          b) here´s what ADDT´s menu code looks like:

          select wdg:subtype="DependentDropdown" name="column_name" id="column_name" wdg:type="widget" wdg:recordset="newrecordsetname" wdg:displayfield="newrecordset_column_to_display" wdg:valuefield="newrecordset_column_to_submit" wdg:fkey="foreign_key_of_recordset_that_drives_the_2nd_menu" wdg:triggerobject="name_of_existing_2nd_menu"

          please note that the abovementioned code doesn´t contain the complete select...select code, because these forums make a mess out of various html code it considers inappropriate

          You can see that I tried to "generalize" the abovementioned values, as they all refer to existing stuff in your form (recordsets, menu names) which I don´t know and which apparently need to adapted by you.

          However, that should IMO about all there is to do -- the good question is: does it work at all ? does the 2nd menu really trigger the third one ?

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: ADDT Dependant Dropdowns 3 deep...
            Level 1
            Günter
            Thanks,
            This where i'm at, it is almost working only the 3rd drop down menu is getting populated with all the models of a certain Make. It isn't being filtered based on the first selection of Type. So basically when i select the first list "Type" say "Trucks" the second list get populated correctly with Makes of trucks but when i select say "Ford" on the econd list the 3rd list get's populated with all the "Ford" inventory not just "Ford Trucks"

            Here's the 3 record sets i'm using
            //Populates The "Types List"
            $query_rsGetType = "SELECT * FROM vehicle_type ORDER BY vehicle_typeID ASC";

            // Populates the makes list based on Type
            $query_rsGetMakeModel = "SELECT ms.makeID, ms.make, tmm.vehicle_typeID FROM type_make_model AS tmm INNER JOIN makes AS ms ON tmm.makeID = ms.makeID INNER JOIN model AS md ON tmm.modelID = md.modelID GROUP BY ms.makeID ORDER BY ms.make";

            // Populates the model List but not buy both Type & Make???
            $query_rsGetMakeModel2 = "SELECT md.model, md.modelID, ms.makeID FROM type_make_model AS tmm INNER JOIN makes AS ms ON tmm.makeID = ms.makeID INNER JOIN model AS md ON tmm.modelID = md.modelID";

            The 3rd recordset needs to been a more "intelligent" so that it knows that the 1st selection was Type "Truck" or "car" etc so possibly a WHERE Clause of some sort?

            Heres the 3 select lists
            <select name="vehicle_typeID" id="vehicle_typeID" o>
            <option value = "">Vehicle Type</option>
            <?php do { ?>
            <option value="<?php echo $row_rsGetType['vehicle_typeID']?>"><?php echo $row_rsGetType['vehicle_type']?></option>
            <?php
            } while ($row_rsGetType = mysql_fetch_assoc($rsGetType));
            $rows = mysql_num_rows($rsGetType);
            if($rows > 0) {
            mysql_data_seek($rsGetType, 0);
            $row_rsGetType = mysql_fetch_assoc($rsGetType);
            }
            ?>
            </select>

            <select name="makeID" id="makeID" wdg:subtype="DependentDropdown" wdg:type="widget" wdg:recordset="rsGetMakeModel" wdg:displayfield="make" wdg:valuefield="makeID" wdg:fkey="vehicle_typeID" wdg:triggerobject="vehicle_typeID" wdg:selected="Make">
            <option value = "">ALL</option>
            <option>-----</option>
            </select>

            <select name="modelID" id="modelID" wdg:subtype="DependentDropdown" wdg:type="widget" wdg:recordset="rsGetMakeModel2" wdg:displayfield="model" wdg:valuefield="modelID" wdg:fkey="makeID" wdg:triggerobject="makeID" wdg:selected="Model">
            <option value = "">ALL</option>
            <option>-----</option>
            </select>


            I appreciate your help!!

            Thanks,
            Cory
            • 3. Re: ADDT Dependant Dropdowns 3 deep...
              Günter Schenk Level 4
              Hi Cory,

              don´t know it´s because of that, but I see that your recordsets GetMakeModel and GetMakeModel2 actually query the same table "type_make_model" -- whereas I personally would have provided an extra table for the md.model and md.modelID data plus would have provided a third column "ms.makeID" which actually contains the foreign keys related to your type_make_model table.

              That said, so far I can´t detect any foreign key specification in your 3rd query which would let the newly created menu instance know what it´s supposed to refer to, and I guess that´s why the new menu happily displays them all.

              ----
              so possibly a WHERE Clause of some sort?
              ----

              don´tknow if this would help, because the recordsets I´m using for dependant dropdown menus are basically unrelated to each other *except* providing a foreign key column that´s related to the "upper" table´s Primary Key.

              However, technically the method I was suggesting does work, right, means the 2nd menu does trigger the third one ? If so, that´s at least a huge progress in itself :-)

              Cheers,
              Günter Schenk
              Adobe Community Expert, Dreamweaver
              • 4. Re: ADDT Dependant Dropdowns 3 deep...
                Level 1
                Günter
                Well, here was my solution... basically what i ended up doing was have the first DDL (Vehicle Type) post back to the page with a Vehicle_TypeID so that i could filter the other 2 recordsets accordingly.

                //1st RS populates Vehicle Type List//
                $query_rsGetType = "SELECT * FROM vehicle_type ORDER BY vehicle_typeID ASC";

                //2nd populates Vehicle Make list filtered by $_POST[vehicle_typeID]//
                $vTypeID = $_POST['vehicle_typeID'];
                $query_rsGetMakeModel = "SELECT ms.makeID, ms.make, tmm.vehicle_typeID FROM type_make_model AS tmm INNER JOIN makes AS ms ON tmm.makeID = ms.makeID WHERE vehicle_typeID = '$vTypeID' GROUP BY ms.makeID ORDER BY ms.make";]

                //3rd populates Model List similair//
                $query_rsGetMakeModel2 = "SELECT md.model, md.modelID, tmm.makeID FROM type_make_model AS tmm INNER JOIN model AS md ON tmm.modelID = md.modelID WHERE vehicle_typeID = '$vTypeID'";

                // the 3 select lists you'll notice the first one uses a simple javascript to post the form back to the page//

                <script type="text/javascript" language="javascript">
                function sender(form){
                form.submit();
                }
                </script>

                <select name="vehicle_typeID" id="vehicle_typeID" onChange="sender(searchDB)">
                <option value = "">Vehicle Type</option>
                <?php do { ?>
                <option value="<?php echo $row_rsGetType['vehicle_typeID']?>" <?php if (!(strcmp($row_rsGetType['vehicle_typeID'], $_POST['vehicle_typeID']))) {echo "selected=\"selected\"";} ?>><?php echo $row_rsGetType['vehicle_type']?></option><?php
                } while ($row_rsGetType = mysql_fetch_assoc($rsGetType));
                $rows = mysql_num_rows($rsGetType);
                if($rows > 0) {
                mysql_data_seek($rsGetType, 0);
                $row_rsGetType = mysql_fetch_assoc($rsGetType);
                }
                ?>
                </select>

                <select name="makeID" id="makeID">
                <option value = "">Make</option>
                <?php do { ?>
                <option value="<?php echo $row_rsGetMakeModel['makeID']?>"><?php echo $row_rsGetMakeModel['make']?></option><?php
                } while ($row_rsGetMakeModel = mysql_fetch_assoc($rsGetMakeModel));
                $rows = mysql_num_rows($rsGetMakeModel);
                if($rows > 0) {
                mysql_data_seek($rsGetMakeModel, 0);
                $row_rsGetMakeModel = mysql_fetch_assoc($rsGetMakeModel);
                }
                ?>
                </select>

                <select name="modelID" id="modelID" wdg:subtype="DependentDropdown" wdg:type="widget" wdg:recordset="rsGetMakeModel2" wdg:displayfield="model" wdg:valuefield="modelID" wdg:fkey="makeID" wdg:triggerobject="makeID" wdg:selected="Model">
                <option value = "">ALL</option>
                <option>-----</option>
                </select>

                I'm sure there is a more elegant way to do this without needing to post to the page the Vehicle_TypeID... but this does what need well enough

                Thanks for your time.

                Cory
                • 5. Re: ADDT Dependant Dropdowns 3 deep...
                  Günter Schenk Level 4
                  Hi Cory,

                  kudos, that´s at least a smart way to achieve the same :-)

                  Guess I´ll be trying to find an easier "3 dropdowns" solution over the weekend -- if I succeed, you´ll be hearing from me :-)

                  Cheers,
                  Günter Schenk
                  Adobe Community Expert, Dreamweaver
                  • 6. Re: ADDT Dependant Dropdowns 3 deep...
                    Level 1
                    Gunter

                    Well it would be great to have it work without the Post back to the page from the first DDL, but it works for now until something better comes along... if you figure out a solution please let me know.

                    Thanks for all your help.

                    Cory
                    • 7. Re: ADDT Dependant Dropdowns 3 deep...
                      drewread
                      Did this ever get sorted and working without posting back to the page?

                      I am trying to do the same sort of thing.

                      The only problem that I can think of is that because the records for the second and third drop-downs are from the same table... the 3rd drop-down would need to know what the first two drop-downs are returning and it only knows what the second drop down is doing.

                      Any way to do this without reposting the variable?