Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Completing a 'Where' clause from a query populated drop-down list

    I'm a total newbie and I'm stuck...

    I'm trying to complete the WHERE clause in a MySQL SELECT statement from a drop down list that has been populated by a previous SELECT statement. I've got the drop-down list populated, but I haven't got the second query to run. Specifically here's what I'm trying to do:

    I have a single table that list product brands and model numbers (2 separate fields).
    The drop down list presents the brand names (via a SELECT DISTINCT statement).
    I want to output a table of the model numbers after the user selects a brand name.

    Suggestions?

  • #2
    Senior Coder rafiki's Avatar
    Join Date
    Aug 2006
    Location
    Floating around somewhere...
    Posts
    2,046
    Thanks
    19
    Thanked 42 Times in 42 Posts
    You will need to use Javascript or send an AJAX request back to a PHP page to re-run a query and repopulate the second drop down list.

  • Users who have thanked rafiki for this post:

    CWind (03-25-2009)

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    If you don't want to use AJAX for whatever reason, you can submit the form as soon as a brand name is chosen, and use the resulting $_GET or $_POST info to complete the second query and populate the model numbers listbox. AJAX is a better solution though.

    Another possiblity is to use PHP to build a Javascript array using every possible brand name choice and its children, and then it's a simple Javascript function to use the data in the array to re-build the second listbox whenever the first listbox changes.

  • Users who have thanked Fumigator for this post:

    CWind (03-25-2009)

  • #4
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you for the suggestions; I'll look into the use of AJAX (but like I said, I'm a TOTAL newbie to this). I apparently wasn't clear on what I'm trying to do here, which is to output a table (not a second drop down list) of the model numbers associated with a brand selected from the first (and only) drop down list.

    I have been able to accomplish this with a text type input as follows:

    PHP Code:
    <form action="<?php $PHP_SELF?>" method="POST">
             Name:
             <input type="text" name="name" />
             <br />
    </form>
    <?php
    mysql_connect
    ("localhost""username""password") or die(mysql_error());
    mysql_select_db("myDatabase") or die(mysql_error());
    $data mysql_query("SELECT * FROM tblSystems WHERE `brand` LIKE '%" $_POST['name'] . "%'")
    or die(
    mysql_error());
    Print 
    "<table border cellpadding=3>";
    Print 
    "<th>Systems</td>";
    while(
    $info mysql_fetch_array$data ))
    {
    Print 
    "<tr>";
    Print 
    "<td>".$info['Brand'] . " ".$info['Model_Num'] ."</td> ";
    }
    Print 
    "</table>";
    mysql_free_result($rs);
    mysql_close($link);
    exit();
    ?>
    So what I want to do now is replace the text type input with a drop down listing of all the brands in the table.
    Last edited by CWind; 03-26-2009 at 04:55 PM.

  • #5
    New Coder
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    0
    Thanked 2 Times in 2 Posts
    You want to populate a drop down list with a columns SET options?

    Query the database along the lines of

    PHP Code:
     <tr><td>Brand:</td>
               <td><select name='brand'></td>
               <?php
            $query 
    "SHOW COLUMNS FROM `table_name` LIKE 'row_name'";
            
    $resultset mysql_query($query);
            
    $setColumnData mysql_fetch_assoc($resultset);
            
    $setString substr($setColumnData["Type"], 6, -2); // the 6 means strip the first 6 chars, the -2 means strip the last two chars
            
    $setOptions explode("','"$enumString);
                            
            foreach (
    $setOptions as $option) {
            echo 
    "<option value='$option'>$option"
            }
    ?>
        </tr>
    That will diplay a drop down option list in the form containing the brands in the SET row (presumably called brand)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •