[Mycroft Holmes]

Select Generic:

Now we get to the good part. This next function builds a select that uses two tables in a database. The first table, mumble needs to have a primary key called 'name' or if the $base_name == 'item' then it is called 'upc'. This table holds the data, or collection of information. The second table, mumble_menu has a much smaller bit of data that helps us organize the first table. You can point this select at any pair of tables that are organized like this, and we do.

The function requires $base_name, $_POST, $mode and $postfix = "". The mode switches the select between 'menu_only' which just shows the categories available in the collection, or not, which shows the categories and the data in two adjacent selects. The postfix part we use to differentiate separate instances of the same function and we set that to nothing as the default case.

And finally ~www/tutorial/include/select_generic.php

<?php
/*		
*		file: include/select_generic.php
*		
*		
*		2011/10/05 - working initial cut
*		2012/02/21 - fixed hang on empty table
*		
*		This function is a template for a select object that provides a single level of
* 		menu selection to a collection of objects.  The menus are stored in the table $base_name_menus
* 		and the collection is in $base_name.  A $postfix is available to further differentiate
* 		select objects that reference the same db but need to have different names. $mode is
* 		used to get the menu only without the collection.
* 
* 		
*/
function select_generic($base_name, $_POST, $mode, $postfix = "") //$Menu, $Upc, $select_menu_id, $select_item_id, $mode, $menu_name
{
	if($base_name == 'item') {$generic_key_name = 'upc';}
	else {$generic_key_name = 'name';}  // base names 'ingredient', 'food'
	
	$Menu = $_POST[$base_name.'_menu'.$postfix];	// integer index into the menu
	$IndexX = $_POST[$base_name.'_name'.$postfix];  // $Ucp or name depending on table, PRIMARY key
	
	if( $Menu == "") {$Menu = 0;} // if not initialized, point somewhere reasonable
	
	if($mode != 'menu_only') { 
		echo "<select onchange='validateUPC();'  id='".$base_name."_menu_id".$postfix."'  name='".$base_name."_menu".$postfix."' >";
	} else {
		echo "<select onchange='updateMenu();'  id='".$base_name."_menu_id".$postfix."'  name='".$base_name."_menu".$postfix."' >";
		echo "<optgroup label='".str_replace('_',' ',$base_name.$postfix)."'>";// decoration for menu only, lets us distinguish menus
	}
	
	include 'include/connect.php';
	// check to see that there are entries in the table.
	
	// Get all the data from the "item_menus" table
	$result = mysql_query("SELECT * FROM ".$base_name."_menus ORDER BY index0") 
		or die("getting item_menus table ".mysql_error());  
	
	while($row = mysql_fetch_array( $result )) {
		// Print out the contents of each row into a table
		if ($row['index0'] != $Menu) {
			echo "<option value='".$row['index0']."'>".htmlspecialchars_decode($row['name'],ENT_QUOTES)."</option>";
		} else {
			echo "<option selected='selected' value='".htmlspecialchars_decode($row['index0'],ENT_QUOTES)."'>".$row['name']."</option>";	
		}
	} 
	if($mode == 'menu_only') { echo "</optgroup>";} // decoration for menu only
	mysql_close($link);
	echo "</select>";
	if($mode != 'menu_only') { 
		echo "<select onchange='updateMenu();' id='".$base_name."_select_id".$postfix."'   name='".$base_name."_name".$postfix."'>";
		include 'include/connect.php';
		// Get all the data from the "items" table
		$result = mysql_query("SELECT * FROM ".$base_name."s WHERE menus=".$Menu." ORDER BY name") 
			or die(mysql_error());
		while($row = mysql_fetch_array( $result )) {
			// Print out the contents of each row into a table
			if ($row[$generic_key_name] != $IndexX){
				echo "<option value=\"".$row[$generic_key_name]."\">".htmlspecialchars_decode($row['name'],ENT_QUOTES)."</option>";
			} else {
				echo "<option selected=\"selected\" value=\"".$row[$generic_key_name]."\">".htmlspecialchars_decode($row['name'],ENT_QUOTES)."</option>";	
			}
		} 
		mysql_close($link);
		echo "</select>";
	}
}
?>

This should all work out of the box just so long and you don't do anything but 'Add Menu'. With the new programmatic creation of the db and tables I haven't bullet-proofed edit_menu() with respect to moving and deleting menu items when there are no items in the collection. So, you can break the tables if you do anything other than Add Menu. If you do break the tables, and I hope you do, simply delete them with phpMyAdmin and submit the form again to recreate the empty tables. Get very familiar with phpMyAdmin, it too is a wonderful debugging tool.

Have Fun.

[Mycroft Holmes]

Edit Items:

Here is a nice dataset for our database. I've done the typing already so there is no reason for you to do it. Besides, it's an excuse to exercise our phpMyAdmin skills. So poke at: db download and put it someplace you will remember. Open phpMyAdmin and select tutorial_db then Structure. Empty the tables. The tables need to exist and be empty for the next part to work. Import the file tutorial_db.xml.zip. Scroll down and click Go. This should complete successfully and give you 147 items and 13 item_menus. This is much easier than typing.

You might want to look at the file in the download. It is in our old friend XML and completely human readable. After the first few entries it gets pretty boring.

Stuff the following in index.php in the appropriate spot. With the code you have, if you have imported the database data above, you should see your command select and then a generic select pointed at the db. Fiddle with it. There is a bug that you need to understand.

	select_command($_POST); // construct global commands
	check_tables(); // check to see that tables present
	select_generic('item',$_POST,'all'); // we need tables for this to work.
	echo "
";

You should see: