[RESOLVED] Sticky Cascading Dropdown

[zooropa]

Can anyone help with my latest endeavour - to create a sticky cascading combo.
I think i have the majority of the code in place but just can't figure out the bit to retain the selection in the parent dropdown when the page resubmits back to itself. The other problem is that I can't get the child dropdown to update depending on the selection made in the parent! Any help greatly appreciated.

I have a table tbl_parts with 3 fields:

part_id (Primary Key, Autonumber)
part_name (varchar)
part_unit (varchar)

e.g.

part_id part_name part_unit
1 part1 01
2 part1 02
3 part1 03
4 part2 01
5 part2 05

and I'd like Distinct part_name to be displayed in parent dropdown (so part 1 is displayed by default and part 2 underneath it with no duplicates)

If the user selects part 1 then 01, 02, 03 would be displayed in the child dropdown or if the user selects part 2 then 01, 05 would be displayed in the child dropdown.

 
<?php
ini_set('display_errors', false); 
require_once('mysqli_connect.php');
$q1="SELECT  DISTINCT tbl_parts.part_name FROM tbl_parts";
$category = $_GET["part_name"];
$q2 = "SELECT tbl_parts.part_name, tbl_parts.part_unit FROM tbl_parts";
?>

<script language="JavaScript">
function autoSubmit()
{
var formObject = document.forms['theForm'];
formObject.submit();
}
</script>

 
<form name="theForm" method="get">


<select name="category"    onChange="autoSubmit();" >   

    [code=php] 
    <?php

    //POPULATE CATEGORY DROP DOWN


    $category = mysqli_query($dbc,$q1);
    echo ("<option>Select Part</option>");
    while($row = mysqli_fetch_array($category))
    {        
        echo ("<option value=\"$row[part_name]\" " . ($category == $row["part_name"] ? " selected" : "") . ">$row[part_name]</option>");        
    }

    ?>

</select>

<select name="subcategory" >

 
        <?php

    //POPULATE SUBCATEGORY DROP DOWN


    $subcategory = mysqli_query($dbc,$q2);

    while($row = mysqli_fetch_array($subcategory))
    {        
        echo ("<option value=\"$row[part_unit]\" " . ($subcategory == $row["part_unit"] ? " selected" : "") . ">$row[part_unit]</option>");        
    }

    ?>
    

</select>

</form>

[/code]

[zooropa]

Oh, and please ignore the

tags - i didn't think these would be inserted in my post - guess i don't understand how to do these tags (as well as sticky cascading dropdowns) brilliant!

P.S. Any hints about how i should have inserted the php and html tags welcome - there was also some Javascript in my script but i couldn't see specific tags for that.

[johanafm]

The board has 3 code-related tags: code, php and html. If you "go advanced" you will see all of them in the editor, along with some other formatting options.

As for the selected option comparison, you are not comparing the proper things.

# this is done early in your script
$category = $_GET["part_name"];

# $compare now holds the selected value
# further down...
$category = mysqli_query($dbc,$q1);

# $category is now either boolean false, or a mysqli result resource. as such, you can
# no longer expect this to work
($category == $row["part_name"] ? " selected" : "")

You also do the same thing with $subcategory.

Personally I'd also split up your table data into two tables

menu
----------------
id
label

submenu
---------------
id
label
menu_id

For which you retrieve your data as such

SELECT menu.id AS menu_id, menu.label AS menu_label, submenu.label AS sub_label, submenu.id AS sub_id
FROM menu
INNER JOIN submenu ON menu_id = menu.id

This way you no longer have redundant data (other than the foreign key "menu_id", while you previously stored each menu label once per submenu item.

Vaidate your html code here. The <script> element has no "language" attribute, and if it did it could not be "javascript", but rather "en", "us-en", "fr" etc. What it does and should have though, is a type attribute: type="text/javascript".
If you serve xhtml rather than html, make your attribute names lowercase (onChange => onchange), and change 'selected' into 'selected="selected"'

[zooropa]

Hi Johanafm,
Thanks for your reply! I agree with your comment about splitting into 2 tables to achieve normalization.

Assuming i leave the tables as they are, could anyone take a stab at editing my code and reposting with what they think the code should be.

For a beginner this isn't obvious or easy and there's a lack of tutorials online (the ones i've found either sort of work or don't work at all)

Thanks

[johanafm]

If you just make sure not to overwrite the same variable with different things, and also make use ofthe second GET parameter (for the submenu), the right options should be selected. Also, the query string parameters (your $_GET element names) are called category and subcategory, since those are the names of the select elements.

# Always sanitize and verify data input
$selected_category = $_GET['category'];
if (substr($selected_category, 0, 4) != 'part' || ((int) substr($selected_category, 4) == 0) {
	# $_GET['part_name'] wasn't valid. If you havn't screwed up, someone is posting "fake" data...
	header('HTTP/1.1 404 Not Found');
	exit;
}
$selected_subcategory = $_GET['subcategory'];
if (!is_numeric($selected_subcategory)) {
	header('HTTP/1.1 404 Not Found');
	exit;
}
# Now you have your two variables to check against the values on each row so you can
# echo 'selected="selected"

[zooropa]

Hi Johanafm,
I decided to back up, follow your advice and split the data into two tables:

tbl_menu

menu_id
submenu_id

tbl_submenu

submenu_id
submenu_label

where submenu_id is a foreign key in the menu table

I've managed to INSERT a value for submenu label (and submenu_id is updated automatically being an Autonumber Primary Key).

So say the submenu table now holds the following values:

submenu_id submenu_label
1 cat
2 dog
3 rabbit

where the latest record i have inserted is 3, rabbit.

What I then want to do is to put the last submenu_id value = 3 and insert it into the submenu_id foreign key field in the menu table. I've tried to do this by associating it with a variable $animal but without success.

Any ideas about how i should go about this. Sorry this is turning into a marathon post due to my fuzzy thinking (and i have spent at least 4 hours on this). Yourhelp is very much appreciated.

[johanafm]

zooropa;10965413 wrote:

Hi Johanafm,

tbl_menu
----------------
menu_id
submenu_id

tbl_submenu
---------------
submenu_id
submenu_label

What I then want to do is to put the last submenu_id value = 3 and insert it into the submenu_id foreign key field in the menu table. I've tried to do this by associating it with a variable $animal but without success.

If I understand you correctly, you are trying to put 3 (the id of the submenu row for "rabbit") into the menu tbl_menu, which is not the right way to do it. Each menu item can have several submenu items. But when you look from the perspective of a submenu item, it can only belong to one menu item. As such, you should rather put the id from tbl_menu into a foreign key field in tbl_submenu, and your tables should look like

tbl_menu
----------------
menu_id
menu_label

tbl_submenu
---------------
submenu_id
submenu_label
menu_id

Now, you'd insert things into your tables like this

INSERT INTO tbl_menu(menu_label) VALUES
('Domestic Animals'),
('Wild, cute and cuddly Animals');

INERT INTO tbl_submenu(submenu_label, menu_id) VALUES
('cat', 1),
('dog', 1),
('rabbit', 2);

How you deal with things on the PHP side matters less, as long as you achieve SQL along the lines of the above.

[zooropa]

Hi Johanafm,
Again thanks for your reply. I didn't explain what i did very well - i actually did what you suggested i.e. fire the record into tbl_menu (even though i said i did the opposite).

What I can't seem to do is take menu_id (the PK) and insert that into tbl_submenu - i've tried:

$q1="INSERT INTO tbl_submenu (menu_id) VALUES(echo'SELECT menu_id FROM tbl_menu ORDER BY menu_id DESC LIMIT 1')";

but to no avail. I just don't know how to turn that pesky last menu_id value into a variable!

[johanafm]

If you need to insert a new menu item and give it a submenu item directly

# assuming properly sanitized data in these variables
$menu_label = ...;
$submenu_label = ...;

# assuming $db to be an instance of mysqli
if (!$db->query("INSERT INTO tbl_menu(menu_label) VALUES ('$menu_label'
)")) {
	# something went wrong. nothing inserted. handle/log error then exit or go on?
	exit;	
}
$id_from_last_query = $db->insert_id;
$db->query("INSERT INTO tbl_submenu(submenu_label, menu_id) VALUES('$submenu_label', $id_from_last_query)");

But if you're happy with first creating a new empty menu, the user would later select one of the menus to edit, and then you'd allready have your menu_id.

[zooropa]

Hi Johanafm,
Thanks for your help. I managed to solve this with the following approach.

$q1="SELECT menu_id FROM tbl_menu ORDER BY menu_id DESC LIMIT 1";
$r1=mysqli_query($dbc,$q1);

while ($row=mysqli_fetch_array($r1))
{
$x=$row['menu_id'];
}

The SELECT query obtains the menu_id field (the primary key) for the last record in table tbl_menu.

Then I loop through the array (of just one record) and set $x = $row['menu_id'].

I then used $x in another loop and poked it into tbl_submenu (so if i set the value in my textbox to 5 say, the value of $x (say it's 17) will be written to the menu_id foreign key in tbl_menu 17 times)