Dropdown categories with sub-categories

[showman13]

Hi,

I'm struggling with creating a dynamic drop down menu and hope someone can help with some direction, or possibly even a code snippet to build on.

I have a table called 'cat_table'

fields are cat_id, cat_name, cat_parent, create_date, last_update

What I am creating is an ad page, displayed by category / sub-category

Members can submit their ads and select whatever category / sub they want it listed under, and if there isn't an appropriate cat or sub-cat, they will be able to add one.

But, what I want is to create the drop down from a mysql query, and list all the cats alphabetically, and then under each cat if there are any sub cats, list them under each primary category.

What I would really like to do is have unlimited levels of sub categories, but that isn't that important right now... Just need to get the basics up now.

Here is what I have to display the main categories:
and what I was thinking was maybe I needed to call a function to get the subs, if there are any that exist, which would require another query for every primary category that is listed...

$sql="SELECT cat_id, cat_name FROM cat_table where cat_parent='0' ORDER BY cat_name asc";
//print "<br />sql is <br />".$sql."<br />";
$base_cat = mysql_query($sql) or die($sql);
$cats=0;
print"<select name=\"categories\">";
while($cat_list=mysql_fetch_array($base_cat)){
  ++$cats;
print "<option value='".$cat_list[0]."'>".$cat_list[1]."</option>";
    //sub_cat($cat_list[0]);   //  this will be a call to a function for sub cats

}
print'</select>';

Any suggestions would be greatly appreciated.

Thanks in advance for any assistance you may be able to provide.

[network18]

you have done creating the first drop down box so far properly and I think on selecting from this drop down you expect to change the contents of second (sub-category) drop down box.
To achieve this you will need to call another script which will load the sub category combo-box with its respective sub-categories, everytime category is selected with ajax.

You can start something like from your below modified code -

<form name=frm id=frm method=post >
<?php 
$sql="SELECT cat_id, cat_name FROM cat_table where cat_parent='0' ORDER BY cat_name asc";
//print "<br />sql is <br />".$sql."<br />";
$base_cat = mysql_query($sql) or die($sql);
$cats=0;
print"<select name=\"categories\" id=\"categories\" onChange=\"javascript:autoLoad();\" >";
while($cat_list=mysql_fetch_array($base_cat)){
  ++$cats;
print "<option value='".$cat_list[0]."'>".$cat_list[1]."</option>";
    //sub_cat($cat_list[0]);   //  this will be a call to a function for sub cats

}
print'</select>';
?>
<select name=subcategories >
</select>
</form>

I assume you have a form at appropriate place, if not I have added one for you.Now call another script from autoLoad() to fill up this subcategories combobox

[showman13]

Thanks for your reply Network18,

I'm not really sure how to work with what you sent.

If I understand what you are saying, the javascript will generate a new sub-category drop down box??

I am not sure I really want to go that route. What I thought I would try to do is to have a listing like this

Autos
Chevy
Ford
Dodge
Advertising
newspaper
radio
tv
etc.....

and if I couldn't do that, possibly
Autos
Autos:Chevy
Autos:Ford
Autos:Dodge
Advertising
Advertising:newspaper
Advertising:radio
Advertising:tv
etc...
I think it can all be done in a single dropdown menu, just need to figure out the most efficient way to accomplish this.

Any suggestions for that?

thanks again

Douglas

[network18]

No doubt you can do this way to but It will be considered as a poor way of data representation.
But you can give it a try before you invest your energy into something complicated I said before, like fetching subcategory for each category -

$sql="SELECT cat_id, cat_name FROM cat_table where cat_parent='0' ORDER BY cat_name asc";
//print "<br />sql is <br />".$sql."<br />";
$base_cat = mysql_query($sql) or die($sql);
$cats=0;
print"<select name=\"categories\">";
while($cat_list=mysql_fetch_array($base_cat))
{
  ++$cats;
//---fetch subcat for this cat
$query = "select * from cat_table where cat_parent='".$cat_list['cat_id']."' ";
$ret = mysql_query($query);

while($row=mysql_fetch_assoc($ret))
{

print "<option value='".$cat_list[0]."'>".$cat_list[1].":".$row['cat_name']."</option>";
    //sub_cat($cat_list[0]);   //  this will be a call to a function for sub cats

}

}
print'</select>';

Modify this further, as I am not sure what value you would like to set for each option and its not the final code for what your trying to achieve.

[showman13]

OK, well I have the drop down box working fine, with a 2 space offset for sub-categories, and both main categories and sub categories listed alphabetically.

But I do still have a couple of issues to resolve.

How do I capture the selected value? I thought I remembered enough about html to know that the value should be captured in the variable matching the name of the <select name= which in this case is category, but the variable $category is always blank...

this little portion of the project is taking waaaayy too long to wrap up.

It is amazing to me how simple issues can take such time and effort to resolve

Here is the code that I have that works great for display, and selection, but capturing the selection is a different story.

Any suggestions would be greatly appreciated.

<?php
//  Get listing of base categories
$sql="SELECT cat_id, cat_name FROM cat_table where cat_parent='0' ORDER BY cat_name asc";
$base_cat = mysql_query($sql) or die($sql);
print"<select name=\"category\">";
while($cat_list=mysql_fetch_array($base_cat)){
	$option="";
		$option .= "<option";
		if	($cat_list[0]==$cur_cat) {$option.= " selected='selected'";}
		$option .= ">" . $cat_list[1] . "</option>";
        print $option;
    sub_cat($cat_list[0]);// this calls the function for sub cats
}
print'</select>';

// ***********************************************************************
// Sub-Category Function
function sub_cat($cat_num){
    $sub_cat = mysql_query("SELECT cat_id, cat_name FROM cat_table where cat_parent='$cat_num' ORDER BY cat_name asc");
    while($sub_cat_list = mysql_fetch_array($sub_cat)){
	$option="";
		$option .= "<option";
		if	($sub_cat_list[0]==$cur_cat) {$option.= " selected='selected'";}
		$option .= ">&nbsp;&nbsp;" . $sub_cat_list[1] . "</option>";
        print $option;
    }
}
// ***********************************************************************
?>

[showman13]

Here is the most recent version of this portion of the script. It works good for displaying the options, and when you select whichever option you want, it is in fact updated in the DB table, but when you go back to update that ad, the category isn't showing as being the selected category upon display...

Hope this makes sense.

When this part of the script is included into the main body, the following variables are in place and have the correct values assigned:

$cur_cat holds the cat_id of the members ad they are modifying (from DB query)

The only issue I am having is that it won't display the selected category in the drop down box when it is shown. It always shows the first category on the list.

<?php
//  Get listing of base categories
$sql="SELECT cat_id, cat_name FROM cat_table where cat_parent='0' ORDER BY cat_name asc";
$base_cat = mysql_query($sql) or die($sql);
print"<select name=\"category\">";
while($cat_list=mysql_fetch_array($base_cat)){
?>
<option value="<?php print $cat_list[0];?>" <?php if($cat_list[0]==$cur_cat){ print" selected=\"selected\""; } ?>><?php print $cat_list[1];?></option>
<?php
    sub_cat($cat_list[0]);// this calls the function for sub cats
}
print'</select>';

// ***********************************************************************
// Sub-Category Function
function sub_cat($cat_num){
    $sub_cat = mysql_query("SELECT cat_id, cat_name FROM cat_table where cat_parent='$cat_num' ORDER BY cat_name asc");
    while($sub_cat_list = mysql_fetch_array($sub_cat)){
?>
<option value="<?php print $sub_cat_list[0];?>" <?php if($sub_cat_list[0]==$cur_cat){ print" selected=\"selected\""; } ?>>&nbsp;&nbsp;<?php print $sub_cat_list[1];?></option>
<?php
    }
}
// ***********************************************************************
?>

[network18]

your problem got to do something with -

<?php if($cat_list[0]==$cur_cat){ print" selected=\"selected\""; }

check out these conditions in both the cases which lets you show the correct option as selected while it loads.
And remember you always have google as your best friend.
you can see this article for your reference http://php.bigresource.com/Track/php-zAKxq4On/
and learn basics about php using http://w3schools.com/php/default.asp

[showman13]

your problem got to do something with -

<?php if($cat_list[0]==$cur_cat){ print" selected=\"selected\""; }

check out these conditions in both the cases which lets you show the correct option as selected while it loads.
And remember you always have google as your best friend.
you can see this article for your reference http://php.bigresource.com/Track/php-zAKxq4On/
and learn basics about php using http://w3schools.com/php/default.asp

Trust me network18, I make full use of google constantly, as well as w3schools.com and numerous other sources, prior to asking on this forum.

I am just totally baffled as to why it isn't making the connection when comparing the category option values and the category number on file, and marking it as 'selected'.

If you or anyone else has any bright ideas, I would truly appreciate them.

This is the html result from the script:
And I know going in that the option that should be selected is #6 because that is the value of the $cur_cat going in.(verified), but it always displays #4, and if the ad is updated without re-selecting the category, it will be recorded as #4...

<select name="category">
<option value="4">Advertising</option>
<option value="8">&nbsp;&nbsp;&nbsp;Newspaper</option>
<option value="10">&nbsp;&nbsp;&nbsp;Radio</option>
<option value="9">&nbsp;&nbsp;&nbsp;Television</option>
<option value="2">Autos</option>
<option value="5">&nbsp;&nbsp;&nbsp;Chevy</option>
<option value="7">&nbsp;&nbsp;&nbsp;Dodge</option>
<option value="6">&nbsp;&nbsp;&nbsp;Ford</option>
<option value="3">Marketing</option>
</select>

I have modified this code every way I can think of, and tried various forms of using it, both as html with php snippets included and the other way around...

I've reached a point where I think I'm just redoing what I've already done, and not getting any different results (definition of insanity I think)

This is the most recent rendition of the code block in question:

<?php
//  Get listing of base categories
$sql="SELECT cat_id, cat_name FROM cat_table where cat_parent='0' ORDER BY cat_name asc";
$basecat = mysql_query($sql) or die($sql);
print"<select name=\"category\">";
while($cat_list=mysql_fetch_array($basecat)){
 if ($cat_list[0]==$cur_cat){$test= ' selected';}else{$test='';}
 print '<option value="'.$cat_list[0].'"'.$test.'>'.$cat_list[1].'</option>';
    sub_cat($cat_list[0]);// this calls the function for sub cats
}
print'</select>';
// ***********************************************************************
// Sub-Category Function
function sub_cat($cat_num){
    $subcat = mysql_query("SELECT cat_id, cat_name FROM cat_table where cat_parent='$cat_num' ORDER BY cat_name asc");
    while($sub_cat_list = mysql_fetch_array($subcat)){
 if ($sub_cat_list[0]==$cur_cat){$test=' selected';}else{$test='';}
print '<option value="'.$sub_cat_list[0].'"'.$test.'>&nbsp;&nbsp;&nbsp;'.$sub_cat_list[1].'</option>';
    }
}
// ***********************************************************************
?>

[ravi_28]

    <?php
    //  Get listing of base categories
$sql="SELECT cat_id, cat_name FROM cat_table where cat_parent='0' ORDER BY cat_name asc";
$basecat = mysql_query($sql) or die($sql);
        print"<select name=\"category\">";
while($cat_list=mysql_fetch_array($basecat)){
 if ($cat_list[0]==$cur_cat){$test= ' selected';}else{$test='';}
 print '<option value="'.$cat_list[0].'"'.$test.'>'.$cat_list[1].'</option>';
            sub_cat($cat_list[0]);// this calls the function for sub cats
        }
    print'</select>';
    // ***********************************************************************
// Sub-Category Function
    function sub_cat($cat_num){
        $subcat = mysql_query("SELECT cat_id, cat_name FROM cat_table where cat_parent='$cat_num' ORDER BY cat_name asc");
        while($sub_cat_list = mysql_fetch_array($subcat)){
 if ($sub_cat_list[0]==$cur_cat){$test=' selected';}else{$test='';}
    print '<option value="'.$sub_cat_list[0].'"'.$test.'>   '.$sub_cat_list[1].'</option>';
    }
}
    // ***********************************************************************
?>

    im using  this code but how to insert in database table

    database table
    product_id  int
    category_id int
    parent_id int
    product_name varchare
    product_price int
    product_images varchar

[diafol]

Start a thread. Don.t resurrect one that died 5 years ago.