Would like to search areas with subcategories in a select box

Hi,
I have a table called 'areas'. I would like to a have a select box that would show this:

Bronx
Brooklynn
...

Manhattan
     Chelsea
     East Village
    .........
     Upper West Side
Saten Island......

This would be based on an mysql call in php.

I would like to do it with one table.
I have used zones to show subcategories.
I have attached code so far to grab all the areas but need to subcategorise.

CREATE TABLE `areas` (
  `area_ID` smallint(4) NOT NULL DEFAULT '0',
  `area` varchar(40) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `zone` varchar(40) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `area_link` varchar(40) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`area_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `areas`
--

INSERT INTO `areas` VALUES(1, 'Brooklynn', '', '');
INSERT INTO `areas` VALUES(2, 'Bronx', '', '');
INSERT INTO `areas` VALUES(3, 'Long Island', '', '');
INSERT INTO `areas` VALUES(4, 'Manhattan', '', '');
INSERT INTO `areas` VALUES(5, 'Staten Island', '', '');
INSERT INTO `areas` VALUES(6, 'Upstate', '', '');
INSERT INTO `areas` VALUES(7, 'Westchester', '', '');
INSERT INTO `areas` VALUES(8, 'Upper East Side', '4', '');
INSERT INTO `areas` VALUES(9, 'Upper West Side', '4', '');
INSERT INTO `areas` VALUES(10, 'Midtown', '4', '');
INSERT INTO `areas` VALUES(11, 'Greenwich Village', '4', '');
INSERT INTO `areas` VALUES(12, 'East Village', '4', '');
INSERT INTO `areas` VALUES(13, 'Lower East Side', '4', '');
INSERT INTO `areas` VALUES(14, 'Financial District', '4', '');
INSERT INTO `areas` VALUES(15, 'Harlem', '4', '');
INSERT INTO `areas` VALUES(16, 'Chelsea', '4', '');
INSERT INTO `areas` VALUES(17, 'Gamercy', '4', '');
INSERT INTO `areas` VALUES(18, 'Soho', '4', '');
INSERT INTO `areas` VALUES(19, 'Tribeca', '4', '');
INSERT INTO `areas` VALUES(20, 'Morningside Heights', '4', '');
INSERT INTO `areas` VALUES(21, 'Washington Heights', '4', '');
INSERT INTO `areas` VALUES(22, 'Inwood', '4', '');


//php to get the areas

$areaselect=array();
$getarea = mysql_query ("SELECT * FROM areas ORDER BY area ");
if ($arearow = mysql_fetch_array($getarea)) {
	do{ 
		$areaselect[$arearow['area_ID']]=$arearow['area'];
			
		}
	while ($arearow = mysql_fetch_array($getarea));
}

//display the box

echo("<select name=\"$field\"  id=\"$field\"><option value=\"".$value."\"selected=\"selected\">".$selected."</option>");
	while (list ($key, $val) = each ($areaselect)) { 
						echo "<option value=\"".$key."\">".$val."</option>"; 
					} 
						
echo("</select>");

Open in new window

mmguideAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gurvinder Pal SinghCommented:
check the following link to see, on how to create sub-menus in javascript

some thing link this?
http://www.w3schools.com/tags/tryit.asp?filename=tryhtml_optgroup

ManishLeadCommented:
Sorry I didnt get your question?

Do you want to show area in dropdown only or
area and subcategories.

In table how area and subcategory are related? I didnt see any relation between them?

-/karan
mmguideAuthor Commented:
Hi,
There is an answer here using a two-dimensional array.
This helped
http://php.net/manual/en/function.array.php

//get areas
if ($arearow = mysql_fetch_array($getarea)) {
	do{ 
		if($arearow['zone']==0){
			$arealoc[$arearow['area_ID']]=$arearow['area'];
			
		}
		else{
			
			$zonelist[$arearow['zone']][$arearow['area_ID']]=$arearow['area'];
		}
	}
	while ($arearow = mysql_fetch_array($getarea));
}

//print select box
 foreach ($arealoc as $akey => $avalue) {
					//while (list ($akey, $aval) = each($arealoc)){  
						if($akey==4){
							print'<optgroup label="' . $avalue . '">';
						}
						else{
							echo "<option value=\"".$akey."\">".$arealoc[$akey]."</option>";
						}
						foreach ($zonelist[$akey] as $zkey => $zvalue) {
							echo "<option value=\"".$zkey."\">".$zvalue."</option>";
						}
						if($akey==4){
							print'</optgroup>';
						}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.