Skip navigation
Tyrii
Currently Being Moderated

Does anyone know how to show a recordset by using a drop down pick list to filter it by?

Apr 30, 2012 12:01 AM

Tags: #drop #jquery #dreamweaver #database #down #list #bindings #coding #recordset #picklist

I have a database built and I just want a dropdown list to filter the products for me for the user on the user side? It is for a Beer and Wine Destributing Company. See an example below of what they are wanting.

 

http://www.superiorbeveragegroup.com/superior-brands.html

 

I cannot figure out how to get my drop down list items to link to the Recordset Bindings that I have created....I am lost!

This is the page so far on the testing site.. http://www.atomic.cyndeeadkins.com/beer.php

At the moment I just have all of the data desplaying at once...and What I want to have happen is have that hidden until a choice is selected in the drop down list.

 

This page is the one I am playing with http://www.atomic.cyndeeadkins.com/beer2.php.

 
Replies
  • Sudarshan Thiagarajan
    4,000 posts
    Oct 15, 2010
    Currently Being Moderated
    Apr 30, 2012 2:33 AM   in reply to Tyrii

    Make a connection.php file with your DB credentials like this:

     

    <?php

    ob_start();

    session_start();

    $con = mysql_connect("yourdomain.com or localhost","username","password");

    mysql_select_db("yourdbname",$con);

    ?>

    Include that in your frontend PHP file, all the way at the top like this:

    <?php

    include_once("connection.php");

    ?>

    Put this script in your <head> tag to define the linking path for your dropdown selection:

    <script language="javascript">

    function seltype(typeid){

        location.href="?type="+typeid;

    }

    </script>

     

    Your form will look like this:

     

    <form name="typeselect" method="post">

                                    <input type="hidden" name="action" value="searchtype" />

                                        <table align="center" width="300px">

                                            <tr>

                                            <?php

                                                $sel_type=mysql_query("SELECT DISTINCT type FROM `YOURTABLENAME` ORDER BY `type`");

                                            ?> 

                                           

                                                <td align="right" > <b>Select County</b> </td> <td> </td>

                                                <td>

                                                    <select name="type" style="width:150px;" onchange="seltype(this.value);">

                                                    <option value="">Select A Type</option>

                                                       

                                                        <?php

                                                            while($typ = mysql_fetch_array($sel_type)) {

                                                        ?>

                                                        <option value="<?=$typ[type] ?>" <? if($_REQUEST[type]==$typ[type]) echo "selected"; ?>> <?=$typ[type] ?> </option>

                                                        <?php } ?>

                                                       

                                                    </select>

                                                </td>

                                               

                                            </tr>

                                        </table>

                                  </form>

    Now that we've instructed the browser what to fetch and how to fetch it, we'll move on to displaying the results:

    Use this code before your result display code:

    <?php

                if(isset($_POST))

                {

                    $action=$_POST['action'];

                    if($action="searchtype")

                    {

                        $sear=mysql_query("SELECT * FROM `YOURTABLENAME` WHERE `type` ='".$_REQUEST[type]."' order by `adddate`");       

                        if($_REQUEST[type] != "")

                        {

                        ?>      

    YOURTABLENAME should be your table's name in your DB.

    type is a column in your table - this is what the browser will fetch when it searches the DB. This is also the filter type we'll be using on our search form.

    adddate is a date column in your table - the results will be sorted based on the addition date.

     

     

    This code will come in your content area to define your imgurl (image location):

    <?php

                            $i=0;

                            while($r = mysql_fetch_array($sear))

                            {

                            ?>  

                                    <?php

                                    $imgurl = 'imgurl';

                                    ?>

    imgurl is a column name in your table - this will store the URL of the image that you want to display. preferreably an absolute URL

     

    Finally, this code will come in your DIV/ table for content and image:

    <td width="199"><?=$r[countydetails]?></td>

    <td width="143"><img src="<?=$r[imgurl] ?>" width="200" height="200"  /></td>

    countydetails is a column in your table that holds the detailed text

     

     

    Trust this helps.

     

    Cheers,

    Sudarshan

     
    |
    Mark as:
  • Sudarshan Thiagarajan
    4,000 posts
    Oct 15, 2010
    Currently Being Moderated
    Apr 30, 2012 2:36 AM   in reply to Sudarshan Thiagarajan

    Apart from the custom code I posted for you above, you could also use an AJAX function to simply FILTER off your data from the MySQL DB in PHP. Follow this link to learn how: http://www.w3schools.com/php/php_ajax_database.asp

     
    |
    Mark as:
  • Currently Being Moderated
    May 19, 2012 9:25 AM   in reply to Tyrii

    See http://help.adobe.com/en_US/dreamweaver/cs/using/WSF6ADD6BC-F21C-432d- A694-CD343AC413C0a.html

     

    Looking at your database, you need to re-organise it into lots more tables, but with a minimum as follows:

    1. A list of counties - with county_id as the main index.

    2. A list of  beer types with a beer_type_ index and  a beer_type field.

    3. A list of beers with an index, a beer_type_index and a county_id and other fields describing the beer.

    In 3, The beer_type_index will have the same valueas the bear_type_index in table 2 and the county_id will have the same name as the county_id in table 1.

    This allows you to find them later.

    When you are creating table three, you use two drop down lists from tables one and two to populate table three. If you have additional sets of data that will repeat often, such as flavours and colours, create a table for these  and use them for the same purpose. A good databes usually stores a value once only - though there are times when string it more than once can be valuable. An index to a value usually takes up less room than repeating the value.

    Fiil 1 with the names of counties.

    Fill 2 with the beer types and let beer_type_index be an auto index in your sql file.

    Using the data from these fields, you then construct 3 .

    It should look like this

     

    index  beer_type  county_id - beer_name beer_flavour etc.

     

     

    David Powers has a good article at http://www.adobe.com/devnet/dreamweaver/articles/first_dynamic_site_pt 1.html

     

    Best way to create a drop down list in Dreamweaver (up to 5.5) is first of all to create a form.

    The form should have as its action, the file to which you will go when the form's submit button is clicked. so add a button from the insert form menu.

    You need a recordset that contains the data that you wish to display in the drop down list.

    Then from the insert menu click forms /select list/menu.

    This inserts an empty drop down list in your form. A list gives you a lot of lines to pick from, while a menu gives you just a single line  (could be the other way round)

    Give the list a name, and a label-  then on the properties panel, click on the yellow arrow.

     

    This brings up a panel which allows you to select what goes in the list from your previously built recordset. Under the "Options from Recordset "box select the recordset. that contains what you need to display in your list. Put the value that you need to submit to the page where you are going to display the result,  in the value box (usually the index column of your table) and the lable wil be a description field.

     

    For instance, if you wanted to display all the counties then the value would be the county_index, as would the label as we have only one value in the counties table and that is the county name. If you have a seperate index in your county list, set that to the value and the county as the label. When the user has selected a county, he then clicks the button, and you are directed yo the page that you set as the action of your form. If you have not changed the form method, you will see a line of text like   ..localhost/counties.php?pickit=2&button=GO  This is the url and the bit after the question mark is the name and value of the drop down list. Ignore the button. It only shows if the form's methos is set to get.

    In the page called counties.php to where you are directed. pickit is a url variable or form variable, depending on whether you have used get or put as the form's method, and 2 is the index value of the county in the counties table that you picked.

    You use that to build another recordset which will pick out all the beers in the selected county.

    Like Select all from table3 where county = pickit

    This will select all rows in which the county record value is 2.

     

    Bind the values to a table in your page and they will display - using a repeat region server behaviour as there should be more than one.

    Complicated?

    Dreamweaver will complete all the code for you.

     

    Here is an example of a drop down list that selectsa all the clubs in a recordset called clubset that has two fields - club_index and club_name, puts the selected value into the variable pickit and sends that value to a page called getclubs.php using the get method. The sql to fill the recordset is as follows:

     

    SELECT club_index, club_name

    FROM clubs

    ORDER BY club_name ASC

     

    And now the drop down list code:

     

    <form action="getclubs.php" method="get" name="pick" target="_blank">

          <select name="pickit" size="1">

            <?php

    do { 

    ?>

            <option value="<?php echo $row_clubset['club_index']?>"<?php if (!(strcmp($row_clubset['club_index'], $row_clubset['club_index']))) {echo "selected=\"selected\"";} ?>><?php echo $row_clubset['club_name']?></option>

            <?php

    } while ($row_clubset = mysql_fetch_assoc($clubset));

      $rows = mysql_num_rows($clubset);

      if($rows > 0) {

          mysql_data_seek($clubset, 0);

          $row_clubset = mysql_fetch_assoc($clubset);

      }

    ?>

          </select>

          <input name="register" type="submit" value="Go"></form>

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 21, 2012 10:05 AM   in reply to whatalotofrubbish

    I have a similar question to that of the original poster: I want to use a dynamic list/menu in dreamweaver that contains customer names pulled from a MySQL database.  Once the user selects a customer from the list, I want that record to be populated in text fields provided where the fields can be edited.

     

    I am building a site on a localhost so I don't have a public URL to provide as an example but I have provided a screenshot in hopes that it might give everyone a better idea of what I'm talking about.  There is already a ton of code on the page so I won't post it here right now but I can provided a few quick details:

    Dynamic list/menu name where user can select customer: customer_list_menu

    Database name: project_jm

    Table name where record is pulled from: customers

    Column name in "customers" table that is shown in dynamic list/menu: last_name_business_name

     

    Sudarshan provided a great example above but I just could not get it to work the way I wanted it to. Any help that anyone can provide would be greatly appreciated.

    screen_shot.jpg

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 22, 2012 9:42 AM   in reply to alexp0w

    alexp0w -

     

    See also : http://help.adobe.com/en_US/dreamweaver/cs/using/WS68FB36A1-4EDF-4777- B4AE-EDBFEFABD995.html

    This article gives lots of info in using forms and most components of forms.

    That is all you should need to achieve your goal.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points