How to make cascading drop-down lists using mysql and php

Question

I have to make a form with multiple drop-down menus that modify one another. The menus/database tables are:

Categories, Styles, Types, and Mechanisms

I have attempted to do this with my limited knowledge of Ajax, but can only seem to access MySQL once (on the initial page) to populate the Categories table without being able to then update the Styles table by querying for the next set of results. I receive an error that claims the database is empty.

I also tried having the drop-down populated through an option group to handle both the Categories and Styles with a looped query, but only the Category headings show up with all of the Style sub-values showing up blank. My code is as follows:

                $query1="SELECT categories.category_id, categories.Category_Name ";
                $query1.="FROM categories ";
                $query1.="ORDER BY categories.Category_Name ASC";
                $category_result=mysql_query($query1, $connection);

                if(!$category_result){
                    die("Database query failed: " . mysql_error());
                }

                $options="";

                $con=0;

                while ($category_row=mysql_fetch_array($category_result)) {
                    $category_name=$category_row["Category_Name"];
                    $CategoryID=$category_row["category_id"];

                    $options.="<OPTGROUP LABEL=\"$category_name\"> <br />";

                    $query2="SELECT categories.category_id, categories.Category_Name, ";
                    $query2.="styles.style_id, styles.Style_Name ";
                    $query2.="FROM categories, styles ";
                    $query2.="WHERE styles.Category_ID = $CategoryID ";
                    $style_result=mysql_query($query2, $connection);

                    if(!$style_result){
                        die("Database query failed: " . mysql_error());
                    }

                    while ($style_row=mysql_fetch_array($style_result)) {
                        $style_name=$row["Style_Name"];
                        $id=$row["style_id"];

                        $options.="<OPTION VALUE=\"$id\" <a href=\"#\" onClick=\"javascript:swapContent('$style_name');\" >".$style_name.'</OPTION>';
                    }
                    $options.='</OPTGROUP> <br />';
                }
            ?>

            <SELECT NAME="category_id">
                <OPTION VALUE=0></OPTION>
                <?php echo $options ?>choose
            </SELECT>

Any insight into what I am doing wrong would be greatly appreciated!

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained and the [deprecation process](http://j.mp/Rj2iVR) has begun on it. See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — hakre, Oct 28 '12 at 20:46
Especially in your case, the iterator capabilities of PDO results are really good for what you ask about. — hakre, Oct 28 '12 at 20:46
@hakre My hope is to get this to work in mysql, which I understand, before switching everything to PDO in the future...time constraints being what they are at present...but thank you for the heads up! — Jelrak The Black, Oct 28 '12 at 21:32

score 0 · Answer 1 · answered Oct 28 '12 at 19:57

0

option must be array.

$options[]="<OPTGROUP LABEL=\"$category_name\"> <br />";
<SELECT NAME="category_id">
   <?php foreach ($options as $value) 
           echo $value?>
</SELECT>

answered Oct 28 '12 at 19:57

AlachiQ

11
3

thank you for this suggestion, but I get a message: "Fatal error: [] operator not supported for strings". What else might I be doing wrong here? – Jelrak The Black Oct 28 '12 at 21:26

score 0 · Accepted Answer · answered Dec 24 '12 at 22:26

Finally just had to figure it out on my own, but thanks for those who tried to help.

I will post my middle table from my Class/Category/Style/Type cascading tables. First make a function such as the below:

 <?php //Category Selection
    function Category_Selection($link) {
        global $connection;
        $options="";
        if(isset($_GET["class_id"])) {
            $query="SELECT categories.category_id, categories.Category_Name ";
            $query.="FROM categories ";
            $query.="ORDER BY categories.Category_Name ASC";
            $result=mysql_query($query, $connection);
            $class_id=$_GET['class_id'];

            if(!$result){
                die("Database query failed: " . mysql_error());
            }
            while ($row=mysql_fetch_array($result)) {
                $name=$row["Category_Name"];
                $id=$row["category_id"];
                $link2=$link."&category_id={$id}";
                $options.="<OPTION VALUE=\"$link2\" ";
                if(isset($_GET["category_id"])) {
                    $category_id = $_GET['category_id'];
                    if($id==$category_id) {
                        $options.=" selected=\"selected\" ";
                    }
                }
                $options.=" >".$name.'</OPTION>';
            }
        } else {
            $options.="<OPTION selected=\"selected\" VALUE=0>First Select Class</OPTION>";
        }
        return($options);
    }
?>

Then place on your main page:

<?php session_start() ?>
    //Category
    if(isset($_GET['category_id'])) {
        $category_id=$_GET['category_id'];
        setcookie('category_id',$category_id);
        $link.="&category_id={$category_id}";
    }elseif(isset($_COOKIE['category_id'])) {
        $_GET['category_id']=$_COOKIE['category_id'];
        $category_id=$_COOKIE['category_id'];
        $link.="&category_id={$category_id}";
    }

Now make your selection drop-down:

<?php //Category Selection
                $options=Category_Selection($link);
            ?>
            <center>
            <SELECT NAME="category_id" ONCHANGE="location = this.options[this.selectedIndex].value;">
                <OPTION VALUE=0></OPTION>
                <?php echo $options ?>
            </SELECT>
            </center>

Repeat above for each required drop-down.

Good luck...and, of course use MYSQLi to protect your site rather than the MYSQL shown above...

How to make cascading drop-down lists using mysql and php

2 Answers2

Linked