[Resolved] Chained Selects from database

aelma · May 27, 2005

Hi !!!!

I need to include in my page a triple and a double chained combo boxes like:

http://www.javascriptkit.com/script/cut183.shtml

and

http://www.javascriptkit.com/script/script2/triplecombo.shtml

The only issue here is that I don't want a static values, I need the values of the combo boxes come from database.

Where can I find a script with this feature?

I'm looking at Google, and everything is static values, could somebody help me?

Thx

Ana Elisa Martínez

g0dzuki · May 27, 2005

If I understand you correctly, you're looking for something lile:


echo "<select name='something'>";

$query = "SELECT something FROM somewhere";
$result = mysql_query($query) or die (mysql_error());

while ($data = mysql_fetch_assoc($result)) {
   echo "<option>$data['yourfield']</option>";

}

echo "</select>";

aelma · May 27, 2005

Well, when the user selects the option that he wants from the 1st. select, then he has to select another option from a second select, but that second options depends on the selection of the 1st. select, and like the 1st select the source of the options is a query.

It's something like this:
http://www.javascriptkit.com/script/cut183.shtml

But instead use a static values as the code of the script shows, I wanna that the values come from a database.

kburger · May 27, 2005

Just use the JavaScript source code that is on the pages you mentioned, but instead of hard-coding the javascript arrays into your page source, build them dynamically with PHP using queries and echo statements where appropriate.

aelma · May 30, 2005

I was trying to do what you said, but anyway it doesn't work, cause the way of the function is always looking for the values in the array: group [x] [x]=new option(" "," ").

aelma · Jun 2, 2005

I really need a chained select, but I wanna the options that appear in every select come from a database, instead a static value, which is the way that all the chained selects that i had checked works.

How can I obtain the values from a table, instead from an array?

kburger · Jun 2, 2005

Since this is something that needs to happen on the client side it will HAVE to be done using javascript. Javascript will need some arrays on the page in its source. Build those javascript arrays using PHP by querying your database and echoing the proper values in the proper places. See my previous post.

kburger · Jun 2, 2005

Let me try to flesh it out a little. Here's an example of something I wrote that I use that's similar to your problem. Obviously, the javascript that's used is a little different than yours. But you should be able to see, at least conceptually, what's going on here.

<?
$categoryquery = "SELECT DISTINCT category FROM subcategories ORDER BY category";
$result = mysql_query($categoryquery);

echo "<!-- Begin
subcategory = new Array(
new Array(\n";
while (list($kidscat) = mysql_fetch_row($result)) {
	echo "new Array(\"Main Page\", 0),\n";
	echo "new Array(\"ALL Subcategories\", \"all\")";
	$subcatquery = "SELECT subcategory, display FROM subcategories WHERE category = 
	 '$kidscat' ORDER BY display";
	$subcatresult = mysql_query($subcatquery);
	while (list($subcat, $display) = mysql_fetch_row($subcatresult)) {
		echo ",\nnew Array(\"$display\", \"$subcat\")";
	}
	echo "\n),\nnew Array(\n";
}
echo ")\n);\n";
?>
function fillSelectFromArray(selectCtrl, itemArray, goodPrompt, badPrompt, defaultItem) {
	var i, j;
	var prompt;
	// empty existing items
	for (i = selectCtrl.options.length; i >= 0; i--) {
		selectCtrl.options[i] = null; 
	}
	prompt = (itemArray != null) ? goodPrompt : badPrompt;
	if (prompt == null) {
		j = 0;
	}
	else {
		selectCtrl.options[0] = new Option(prompt);
		j = 1;
	}
	if (itemArray != null) {
		// add new items
		for (i = 0; i < itemArray.length; i++) {
			selectCtrl.options[j] = new Option(itemArray[i][0]);
			if (itemArray[i][1] != null) {
				selectCtrl.options[j].value = itemArray[i][1]; 
			}
			j++;
		}
		// select first item (prompt) for sub list
		selectCtrl.options[0].selected = true;
	}
}
//  End -->

The page source that's generated looks like this (in my case):

<!-- Begin
subcategory = new Array(
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Arts and Crafts", "artsandcrafts"),
new Array("Culinary", "culinary"),
new Array("Dance", "dance"),
new Array("Enrichment", "enrichment"),
new Array("Music", "music"),
new Array("Scouting", "scouting"),
new Array("Sports", "sports"),
new Array("Tutoring", "tutoring")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Art", "art"),
new Array("Crafts", "crafts"),
new Array("Culinary", "culinary"),
new Array("Dance", "dance"),
new Array("Music", "music"),
new Array("Theater", "theater")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Art Camp", "art"),
new Array("Day Camps", "daycamp"),
new Array("Educational", "educational"),
new Array("Group", "groups"),
new Array("Overnight Camps", "overnight"),
new Array("Religious", "religious"),
new Array("Special Needs", "specialneeds"),
new Array("Sports", "sports"),
new Array("Travel", "travel"),
new Array("Wilderness", "wilderness")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Baby Sitting", "babysitting"),
new Array("Daycare/Preschool", "preschool"),
new Array("Foster Care", "foster"),
new Array("Nanny Agencies", "nanny"),
new Array("Special Needs", "specialneeds")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Kid Friendly Restaurant", "restaurant"),
new Array("Kids Eat Free", "eatfree")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Educational Activities", "activity"),
new Array("Enrichment", "enrichment"),
new Array("Libraries", "library"),
new Array("Schools", "school"),
new Array("Teacher Resources", "resource"),
new Array("Tutoring", "tutor")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Animal Adventures", "animal"),
new Array("Attractions", "attraction"),
new Array("Museums", "museum"),
new Array("Nature/Geology", "nature"),
new Array("Overnight", "overnight"),
new Array("Planetariums", "observatory"),
new Array("Seasonal", "seasonal"),
new Array("Technology", "tech"),
new Array("Theater/Arts", "theater"),
new Array("Transportation", "transportation")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Attractions", "attraction"),
new Array("Fun Centers", "funcenter"),
new Array("Museums", "museum"),
new Array("Nature & Geology", "outdoor"),
new Array("Sporting Events", "event"),
new Array("Zoos", "zoo")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Kid Friendly Lodging", "lodging")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Family Memberships", "membership"),
new Array("Kids Clubs", "clubs")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Business Opportunities", "business"),
new Array("Crafts & Classes", "crafts"),
new Array("Event and Party Planners", "planning"),
new Array("Health & Fitness", "health"),
new Array("Home & Garden", "home"),
new Array("Lunch & Outings", "lunch"),
new Array("Maternity", "maternity"),
new Array("Spas & Salons", "spas")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Business Opportunities", "business"),
new Array("Child Safety", "safety"),
new Array("Financial Planning", "financial"),
new Array("Foster Care", "fostercare"),
new Array("Haircuts", "haircuts"),
new Array("Insurance", "insurance"),
new Array("Medical Services", "medical"),
new Array("Mother's Helpers", "mothers"),
new Array("Orthodontics", "orthodontics"),
new Array("Photography", "photography"),
new Array("Special Needs", "specialneeds")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Birthday Gift Ideas", "gift"),
new Array("Cakes/Caterers", "caterer"),
new Array("Entertainers", "entertainer"),
new Array("Event and Party Planners", "planning"),
new Array("Party Packages/Places", "packages"),
new Array("Party Supplies/Rentals", "supplies")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Education", "education"),
new Array("Independent Schools", "independent"),
new Array("Kindergarten/Preschool", "kindergarten"),
new Array("Language", "language"),
new Array("Montessori Schools", "montessori"),
new Array("Private Schools", "private"),
new Array("Teacher Resources", "resource"),
new Array("Tutoring", "tutor")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Book Stores", "book"),
new Array("Clothing Stores", "clothing"),
new Array("Music Stores", "music"),
new Array("Nutrition", "nutrition"),
new Array("Online Stores", "online"),
new Array("Playsets", "playset"),
new Array("Safety Products", "safety"),
new Array("Specialty Shops", "novelty"),
new Array("Toy Stores", "toy")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Baseball", "baseball"),
new Array("Basketball", "basketball"),
new Array("Equestrian", "equestrian"),
new Array("Golf", "golf"),
new Array("Gymnastics", "gymnastics"),
new Array("Martial Arts", "karate"),
new Array("Pro Sports", "prosports"),
new Array("Skating/Hockey", "skating"),
new Array("Skiing/Boarding", "skiing"),
new Array("Soccer", "soccer"),
new Array("Swimming", "swimming"),
new Array("Tennis", "tennis")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Biking", "biking"),
new Array("Camping / Hiking", "camping"),
new Array("Fishing", "fishing"),
new Array("Horseback Riding", "horseback"),
new Array("Rafting / Kayaking", "rafting"),
new Array("Summer Camps", "summercamp"),
new Array("Summer Sports", "sports"),
new Array("Swimming", "swimming")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Field Trips", "fieldtrips"),
new Array("Teachers Education", "workshops"),
new Array("Teaching Supplies", "teachsupplies"),
new Array("Tutoring", "tutoring")
),
new Array(
new Array("Main Page", 0),
new Array("ALL Subcategories", "all"),
new Array("Ice Skating", "skating"),
new Array("Indoor Activities", "indoor"),
new Array("Skiing/ Snowboarding", "skiing"),
new Array("Sledding/ Tubing", "sledding"),
new Array("Snowmobiling", "snowmobiling"),
new Array("Swimming", "swimming")
),
new Array(
)
);




function fillSelectFromArray(selectCtrl, itemArray, goodPrompt, badPrompt, defaultItem) {
	var i, j;
	var prompt;
	// empty existing items
	for (i = selectCtrl.options.length; i >= 0; i--) {
		selectCtrl.options[i] = null; 
	}
	prompt = (itemArray != null) ? goodPrompt : badPrompt;
	if (prompt == null) {
		j = 0;
	}
	else {
		selectCtrl.options[0] = new Option(prompt);
		j = 1;
	}
	if (itemArray != null) {
		// add new items
		for (i = 0; i < itemArray.length; i++) {
			selectCtrl.options[j] = new Option(itemArray[i][0]);
			if (itemArray[i][1] != null) {
				selectCtrl.options[j].value = itemArray[i][1]; 
			}
			j++;
		}
		// select first item (prompt) for sub list
		selectCtrl.options[0].selected = true;
	}
}
//  End -->

dotty · Jun 2, 2005

Or you could just use a form with one select menu holding the top level categories from the database and when the form submits, a second menu is populated based on the first menu choice.

I've used this myself and it works fine. You can also add a "show if" statement to hide the second menu until the first selection has been made. The user does need to wait while the page reloads but it eliminates the need for javascript so guarantees that it is cross-browser friendly.

aelma · Jun 6, 2005

Hello everybody,

I found the solution to my problem:
http://www.zazzybob.com/onchange.html

Thx for help, Now I now a bit more because of your posts.

I have another question, that I'll post in another thread.

Ana Elisa

[Resolved] Chained Selects from database

Aaelma

Gg0dzuki

Aaelma

Kkburger

Aaelma

Aaelma

Kkburger

Kkburger

Ddotty

Aaelma