Xampp: Programming for the masses, C, MySQL & HTML together!

When we last left off I had erroneously stated that you can't create a db from PHP. More accurately, I had tried for a little bit and given up and just whacked it off with phpMyAdmin, tables too. So here is the promised code to create a db and tables from PHP. First, to make sure we are all on the same page, here are the three files we will be playing with in their entirety.

~www/tutorial/index.php

<html>
  <head>
    <title>
	Page title goes here...
    </title>
    <style>
	CSS Style, if you've got it...
    </style>
    <script type="text/javascript">
		/*	These scripts are available globally to all the included files.  The scripts
		*	are executed by the client machine in response to inputs by the user, typing
		*	input and selecting menus and such.  
		*
		*	Unlike the php source, these scripts are visible to the client when view source
		*	is used. The scrip functions are generally called by form elements when they
		*	are manipulated by the user, and cause things to happen on the client page.
		*
		*	It is very important in general to test all objects for non-null values before
		*	attempting to write to them as writing to a null object will hang the script.
		*	You can tell this is happening when you see that the form doesn't update when
		*	the values of the <selects> are changed.
		*/
		function formSubmit() 
		{	// reload frm1 (everything is frm1) with the current input values
			document.getElementById('frm1').submit();
		}
		function updateMenu() // runs every time any menu <select> is updated
		{	// 			
			formSubmit();
		}
    </script>
  </head>
  <body>
	<form id="frm1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<?php 
$debug = true;
if($debug) {echo "In index.php<br />";$ta = array_keys($_POST);for($ti=0;$ti<count($ta);$ti++) {echo $ta[$ti]."=".$_POST[$ta[$ti]]."<br />";}}

	$FoodCount = $_POST["food_count"];  // Memory, Dr Memory 
	$FoodUnits = $_POST["food_units"];  
	$DrinkCount = $_POST["drink_count"]; 
	$DrinkUnits = $_POST["drink_units"];  
	$FavoriteFlavor = $_POST["favorite_flavor"]; 

	include "include/tutorial_functions.php";  // a collection of functions for tutorial
	include "include/connect.php";  // keys for db login
	include "include/check_tables.php";  // see that db tables are present, create if not

	echo "Hello World...<br />";
	echo "Fave Flav:<input type='text' value='".$FavoriteFlavor."' name='favorite_flavor' /> ";
	select_command($_POST, $base_name="command"); // pick a command
	echo "<br />Food: ";
	select_quantity($prefix='food_', $FoodCount, $FoodUnits); // pick a quantity of food
	echo "<br />Drink: ";
	select_quantity($prefix='drink_', $DrinkCount, $DrinkUnits); // pick a quantity of drink
	echo "<br />";
	echo "Hi there<br />";

	check_tables(); // check to see that tables present
	echo "<br />Hi Again<br />";

?>
	<form>
  </body>
</html>

~www/tutorial/connect.php

<?php
// this is an in-line and the only place where the db username
// and password should appear.
$debug = true;
$db = "tutorial_db";
$link = mysql_connect('localhost', 'admin', 'password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
if($debug == true) {echo 'Connected to MySQL successfully<br>';}
?>

~www/tutorial/check_tables.php

<?php
/*		file: include/check_tables.php
*		2012/02/17 - working initial cut
*		
*		Check for existance of tables and create any
*		missing ones.
*/
function check_tables() {
// Usage without mysql_list_dbs() deprecated
	$debug = true;
	include 'include/connect.php'; // connects us to db
	if($debug == true) {echo 'Connected SQL link='.$link.'<br />';}
	$res = mysql_query("SHOW DATABASES");
	if($debug == true) {echo "checking database...<br />";}
	while ($row = mysql_fetch_assoc($res)) {
		echo $row['Database'] . "\n";
	}
}
	// Create items table
function create_items_table() {
	include 'include/connect.php'; // connects us to tutorial_db
	echo "Creating Table items.<br />";
	$sql = "CREATE TABLE items
	(
	name varchar(256),
	upc bigint(16) NOT NULL,
	menus varchar(64),
	price varchar(32),
	amount varchar(32),
	calories_unit varchar(32),
	suppliers varchar(32),
	handling int(11),
	url varchar(512),
	level double,
	reorder float,
	CONSTRAINT item_pk	// primary key
		PRIMARY KEY (upc),
	CONSTRAINT item_uq	// unique fields
		UNIQUE (upc)
	)";		
	// Execute query
	mysql_query($sql,$link);
	mysql_close($link);	
}
?>

These have been edited a bit to suit my nefarious purposes so please do cut and paste. The main differences here are that we are making a connection to the db and getting a list of the databases present. We can then check to see if our db is among them. If so, we'll check the tables and if they are not present we will create them. We won't ask permission because without tables the rest of the tutorial is pretty useless, but in a real application it is usually wise to ask before doing anything rash.

After you have saved the files away and diddled one of the selects to get a refresh, you should see something like:

Whoops! See below:

drink_units=oz
Connected to MySQL successfully
Hello World...

Who ordered that? Looks like we have treated an inline, connect.php, like a collection of functions and it hurt us. So remove the include to prevent unnecessary connects. Fix this to be sure you understand.

The other thing that is going on here, the thing that we planned, is that we are connecting to the db successfully with a particular Resource id and we got a list of databases. Ours, "tutorial_db" is among them because we created it earlier using phpMyAdmin. Using that same tool, let's blow it away so we can recreate it auto-magically.

A quick word about debugging here. I use echo extensively for debugging (and the error logs). Sometimes it is temporary, like "Hi there". These statements get blown away forever when they have served their purpose. There are others though, that I think may just get used again, and they get hidden by if($debug==false){spray_information_to_page;} in the normal course of things. As the number of files grow, I like to identify what file or function the debugging information came from so I can turn it off. This is why "In index.php" now appears at the top of the output.

So let's add a little bit to our check_tables() function to create the necessary db and tables.

function check_tables() {
// Usage without mysql_list_dbs() deprecated
	include 'include/connect.php'; // connects us to db
	$debug = true;
	if($debug == true){echo 'Connected SQL link='.$link.'<br />';}
	$res = mysql_query("SHOW DATABASES");
	if($debug == true){echo "checking database...<br />";}
	$present = false;
	while ($row = mysql_fetch_assoc($res)) {
		if($debug == true){echo $row['Database']." ";}
		if($row['Database'] == $db){$present = true;}
	}
	if($debug == true){echo "<br />";}
	if($present == false) { // Create db
		echo "Database ".$db." not present. Creating...<br />";
		$sql = 'CREATE DATABASE '.$db;
		if (mysql_query($sql, $link)) {
			echo "Database ".$db." created successfully<br />";
		} else {
			echo 'Error creating '.$db.': ' . mysql_error() . "<br />";
			die(mysql_error());
		}
	}
	mysql_select_db($db);
	$sql = "SHOW TABLES FROM ".$db;
	$result = mysql_query($sql);
	if (!$result) {
		echo "DB Error, could not list tables\n";
		echo 'MySQL Error: ' . mysql_error();
		exit;
	}
	$items_present = false;
	while ($row = mysql_fetch_row($result)) {
		if($debug == true){echo "Table: {$row[0]} <br />";}
		//echo "Table: ."$row[0]." ";
		if($row[0] == 'items') {$items_present = true;}
	}
	if($items_present == false) { //make items table
		create_items_table();
	}
	mysql_free_result($result);
}

Copy, save, submit and you should see something like:

Which shows a database named 'tutorial_db' and table called 'items' created. Yea! Next we'll talk more about SQL in general.

We are using a version of SQL called MySQL. There are about 5 major flavors of SQL, all pretty much mutually incompatible at the level of fine detail. At the same time, they all work substantially alike. Understanding one version gets you 97% of the way to understanding any other. SQL was developed originally by IBM in the early 1970's and called SEQUEL. It was renamed due to a trademark dispute.

A pretty good tutorial on SQL in general can be found here. SQL is an acronym for Structured Query Language. Some formulations replace Structured with something less complementary. It became an ANSI standard in 1987 (the reason there are only five versions) which has been enhanced by vendors several times since then in the interest of keeping their customers locked to their particular product.

It's a language like any other. There are control structures (CASE statements) and a blizzard of data types, different for each version, of course. The whole business is optimized to work on data structured as tables. Think rows of individual entries with columns of fields of different data types. SQL is used to sort through a collection of such data looking for rows which meet the specification of the moment and returning those rows to the user for further processing.

Most of the actual mechanics of what we will be doing is putting together a string (called a query in SQL-speak) and stuffing it into the function $res = mysql_query($sql); with the results ending up in the array $res. It's the query string itself that does most of the work communicating with the db. The formulation while ($row = mysql_fetch_row($result)) {paw_through_the_rows;} lets you step through the rows of results looking for what you want. Ninety five percent of what we do is simple variations on this theme.

Most of the art of SQL lies in designing the tables. There are definitely right and wrong ways to go about it. You can even make the wrong way work, for a while. I'm not a database guy and I do have a penchant for learning things the hard way. I've been trying to formulate general rules for db design but have only come up with one so far. If you find yourself storing the same information in two different places, you are doing it wrong.

We have already designed one table. We slipped that in with the function create_items_table() in ~www/tutorial/check_tables.php. This is a table of items one might buy at the store. We enumerated the list of fields earlier and that list is present in the function above. One of those fields is called menu and it tells us which menu (a grouping) that item is to be found on. We need to somehow group items so as to keep the selects for the items from becoming unwieldy.

The initial thought was to group like items with like. Drinks with drinks and dairy with dairy and such. This turns out to be not such a bad idea and almost correct. The people doing the actual buying wanted a list organized by where the items physically were in the store. This meant that some items that were say fresh orange juice were not stored with the other drinks but instead are with the dairy items. Some flexibility in the menus is obviously required.

So we need a table for the item menus. Let's call it item_menus. It needs two fields, index0 type int(11) and name type varchar(64). We are using index0 instead of index for a name because the name index is special to SQL, name does not appear to be special. This is the table's primary key and it is also used to order the menu. The name part of the table is the text that actually appears in the menu used to organize the items.

I've reworked this file a bit so stuff this whole mess into ~www/tutorial/check_tables.php

<?php
/*		file: include/check_tables.php
*		2012/02/17 - working initial cut
*		2012/02/20 - added creating db, items
*		2012/02/20 - added creating item_menus
*		
*		Check for existance of tables and create any
*		missing ones.
*/
function check_tables() {
// Usage without mysql_list_dbs() deprecated
	include 'include/connect.php'; // connects us to db
	$debug = true;
	if($debug == true){echo 'In check_tables() Connected SQL link='.$link.'<br />';}
	$res = mysql_query("SHOW DATABASES");
	if($debug == true){echo "checking database...<br />";}
	$present = false;
	while ($row = mysql_fetch_assoc($res)) {
		if($debug == true){echo $row['Database']." ";}
		if($row['Database'] == $db){$present = true;}
	}
	if($debug == true){echo "<br />";}
	if($present == false) { // db not present, creat it.
		echo "Database ".$db." not present. Creating...<br />";
		$sql = 'CREATE DATABASE '.$db;
		if (mysql_query($sql, $link)) {
			echo "Database ".$db." created successfully<br />";
		} else {
			echo 'Error creating '.$db.': ' . mysql_error() . "<br />";
			die(mysql_error()); // die a horrible death, can't go on...
		}
	}
	mysql_select_db($db);
	$sql = "SHOW TABLES FROM ".$db;
	$result = mysql_query($sql);
	if (!$result) {
		echo "DB Error, could not list tables\n";
		echo 'MySQL Error: ' . mysql_error();
		exit; // die a horrible death, can't go on...
	}
	$items_present = false; // flags for table creation
	$item_menus_present = false;
	while ($row = mysql_fetch_row($result)) { // paw through the results
		if($debug == true){echo "Table: {$row[0]} <br />";}
		//echo "Table: ."$row[0]." ";
		if($row[0] == 'items') {$items_present = true;}
		if($row[0] == 'item_menus') {$item_menus_present = true;}
	}
	mysql_free_result($result); //return some memory
	if($items_present == false) { //make items table
		create_items_table();
	}
	if($item_menus_present == false) { //make item_menus table
		create_item_menus_table();
	}
}
	// Create items table
function create_items_table() {
	include 'include/connect.php'; // connects us to tutorial_db
	echo "Creating Table items. <br />";
	// do NOT put any comments in the string below
	$sql = "CREATE TABLE items (
	name varchar(256),
	upc bigint(16) NOT NULL,
	menus varchar(64),
	price varchar(32),
	amount varchar(32),
	calories_unit varchar(32),
	suppliers varchar(32),
	handling int(11),
	url varchar(512),
	level double,
	reorder float,
	CONSTRAINT item_pk	
		PRIMARY KEY (upc),
	CONSTRAINT item_uq	
		UNIQUE (upc)
	)";		
	// Execute query
	$res = mysql_query($sql);
	if($debug == true){echo "Result = ".$res;}
	mysql_close($link);	// we're done, clean up.
}
	// Create item menus table
function create_item_menus_table() {
	include 'include/connect.php'; // connects us to tutorial_db
	echo "Creating Table item_menus. <br />";
	// do NOT put any comments in the string below
	$sql = "CREATE TABLE item_menus (
	index0 int(11) NOT NULL,
	name varchar(64) NOT NULL,
	CONSTRAINT item_menus_pk	
		PRIMARY KEY (index0),
	CONSTRAINT item_menus_uq	
		UNIQUE (name)
	)";		
	// Execute query
	$res = mysql_query($sql);
	if($debug == true){echo "Result = ".$res;}
	mysql_close($link);	// we're done, clean up.
}
?>

The rework is in the table creation logic and in closing the links after individual table creation. This is probably a good idea to let the system reclaim memory in an orderly fashion. The system will also probably reclaim the memory when a new $link is opened, at least it should. Better to be explicit and safe than rely on how things should be and sorry. A few comments have been added for clarity. Hey, I tried.

Another thing to be aware of is that this whole check_tables() business runs every time the page is refreshed. You might not want to do that, it consumes resources (CPU) that you might want for something else, like running the program with > 10K users. It would be wise to only run this function when there are new tables to be made. On the other paw, it isn't very computationally expensive. For the moment we will leave things as they are. We'll be slipping new tables in pretty regularly for a while.

So, to recap; we know how to check to see if a db is present and create it if not. We can check for the presence of individual tables and create them if they are not in the db. In the future, for additional tables I will provide the function for table creation and you will graft it into check_tables() so that it all works. You should be able to do this now. If not, I need to know because I may not be communicating clearly.

Writing the Program: top

So now we have all of the pieces in place to begin writing our program and our understanding of all of the various bits is sufficient to proceed. There is a new factor I would like to introduce here, the notion of a hidden variable. This is an input in a form that doesn't appear on the rendered page. It's very useful in passing state information from page view to page view; it's really all the same page.

A hidden variable looks like this <input id='edit_menu_exists_id' type='hidden' value='exists' name='edit_menu_exists'> The simple existence of the variable can contain the information or you can attach a value to it. This variable is going to be used to indicate how we are editing items in the database. There are three types of edit we can do, add, edit and delete. We can also note that a page (or display interface) exists. We use simple obvious strings to convey those values to make the program more readable.

In the code below there is a hidden variable in the function edit_menu() that only shows up in $_POST when an instance of function edit_menu() exists. We assign the value of that variable to $EM_exists and it serves as a flag to let us know if we are editing menus or not.

We can light up an instance of edit_menu() by selecting 'Edit Menus' at the bottom of the global command select created by select_command($_POST); We removed the silly switch and base_name business, that was just for the tutorial. If we do select 'Edit Menus' the form is submitted with the value of the input 'command' as 'Edit Menus' which will appear in the array $_POST["command"] and gets stuffed into $Command.

When we come to the if statement $Command == 'Edit Menus' is satisfied and none of the other variables exist so they are not a factor. edit_menu($_POST); gets invoked (called, executed) and an instance of edit_menu() is created with all of its various objects. We'll see those later.

The newly created instance creates it's hidden variable which eventually lands in $EM_exists which lets us "latch" the instance of edit_menu() until the 'Exit Edit Menus' condition in the if statement is satisfied.

Replace the entire body of ~www/tutorial/index.php with the following:

 
  <body>
	<form id="frm1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<?php 
$debug = true;
if($debug) {echo "In index.php<br />";$ta = array_keys($_POST);for($ti=0;$ti<count($ta);$ti++) {echo $ta[$ti]."=".$_POST[$ta[$ti]]."<br />";}}

	$Command = $_POST["command"];  // Memory, Dr Memory; global command select value
	$EM_command = $_POST["edit_menu_command"];  // edit_menu command select value
	$EM_exists = $_POST["edit_menu_exists"];  // edit_menu exists
	$New_menu = htmlspecialchars_decode($_POST["new_menu"]);  // used in edit_menus as a source (is a textfield)
	$Current_menu = $_POST["menu_current"];
	//$Feedback = ''; // Global string, produced by everybody, consumed by index.php and printed at the bottom of the page.

	include "include/tutorial_functions.php";  // a collection of functions for tutorial
	include "include/check_tables.php";  // see that db tables are present, create if not
	include "include/edit_menu.php";  // edit the menu catagories.
	include "include/select_generic.php";  // a generic select into the db

	select_command($_POST); // pick a command
	echo "<br />";
	check_tables(); // check to see that tables present
	
	if  (($Command == 'Edit Menus'  || $EM_exists == 'exists') && $EM_command != 'Exit Edit Menus')  {  
		edit_menu($_POST);  
	} 
	echo "<p class='fb'>".$Feedback."</p>"; // output at bottom of page
	echo "<p class='err'>".$Error."</p>"; // using the css defined at the beginning of the page
?>
	<form>
  </body>

We include two new .php files which we'll be using later. When they are missing things should fail silently but an error message is recorded in the error log. You have found the error log, yes?

The first file, edit_menu.php, we discussed above, it lets you create and edit menus (categories) for items you purchase. The second file, select_generic.php is used by edit_menu() and a whole lot more. With select_generic() we can begin to see the possibilities of maybe making all of this not so dreary and confusing. We'll get to that too.

Near the bottom is code that prints the contents of a couple of variables that are used to report errors and feedback. There is a new construct <p class='fb'>".$Feedback."</p>. This is the first sighting of the actual use of CSS. What we have done is assigned this entire paragraph to the class 'fb' (feedback). Later we will color it blue but that's a distraction for now.

Another thing to notice as we move forward is that the pages now start to seem to have two parts. There is a noun part that makes objects that show up on the page and there is a verb part that controls the action. I like to group the action at the end of the file. This seems to make sense, first you make things and then you act on them. When we Submit the page all of the values of the inputs with the names of their instances (most of the objects we are creating) are posted back to the server.

Back at the Server:

Whoops! Un-comment $Feedback = ""; in index.php, the example above. Sorry, slip of the mind. That statement zeros out that variable before it gets processed by the grammar of the includes. That is where it sees most of its use.

Back at the server, the very first thing we do in the form (modulo debug) is unwrap all of those inputs in the $_POST and stuff them into variables so that they can be evaluated by the verb part of the include files that follow the assignment. The include files to this point have been mostly functions. Functions don't actually exist until they are called. The verb parts, the included grammar (I did make that one up), do exist and the logic of it is executed as the files are included (processed by PHP) while it reads and evaluates the file. In order for the variables to be usable by the included grammar the variable assignment must precede the included files. You see this in index.php which is currently the only file with includes. This will also change.

We have redone the function select_command() so that it only need be passed the variable $_POST. By unwrapping $_POST we can get to all of the current state. We use a bit of that state, $EI_exists, to modify the creation of this select. We will be seeing this work shortly. We also added a new function, edit_menu_command() which creates a command select for the function edit_menu().

Replace the function select_command() in ~www/tutorial/include/tutorial_function.php with the following:

function select_command($_POST) {
	$EI_exists = $_POST["edit_item_exists"];
	$Command = $_POST["command"];

	echo "<select onchange='updateMenu();' name='".$base_name."'>";	
	echo "<optgroup label=\"List Operations\">";
	echo "<option value='Global Commands'>Global Commands</option>";
	echo "<option value='Add To List'>Add To List</option>";
	echo "<option value='Delete From List'>Delete From List</option>";
	echo "</optgroup>";
	echo "<optgroup label=\"DB Operations\">"; // are we permitted to save item?
	if(($Command != 'Save Item to DB' && $Command != 'Continue Unchanged') && ($EI_exists == 'add' || $EI_exists == 'edit' || $EI_exists == 'delete'
		|| $Command == 'Add Item to DB' || $Command == 'Edit Item in DB' || $Command == 'Delete Item in DB')) { // add the ask $Command == 
			echo "<option value='Save Item to DB'>Save Item to DB</option>";
			echo "<option value='Continue Unchanged'>Continue Unchanged</option>";
	} else {
		echo "<option value='Add Item to DB'>Add Item to DB</option>";
		echo "<option value='Edit Item in DB'>Edit Item in DB</option>";
		echo "<option value='Delete Item in DB'>Delete Item in DB</option>";
	}
	echo "</optgroup>";
	echo "</optgroup>";
	echo "<optgroup label=\"List Operations\">";
	echo "<option value='Edit Menus'>Edit Menus</option>";
	echo "</optgroup>";
	echo "</select>";
}

function edit_menu_command() {
	echo "<select onchange='updateMenu();' name='edit_menu_command'>";	
	echo "<optgroup label='Menu Editing'>";
	echo "<option value='Menu Edit Commands'>Menu Edit Commands</option>";
	echo "<option value='Add Menu'>Add Menu</option>";
	echo "<option value='Rename Menu'>Rename Menu</option>";
	echo "<option value='Move Menu After'>Move Menu After</option>";
	echo "</optgroup>";
	echo "<optgroup label=\"Menu Deletion\">";
	echo "<option value='Delete Menu'>Delete Menu</option>";
	echo "</optgroup>";
	echo "<optgroup label=\"Exit Here\">";
	echo "<option value='Exit Edit Menus'>Exit Edit Menus</option>";
	echo "</optgroup>";
	echo "</select>";
}

This also adds selections and some decorations to group choices in the selects in such a way that is easy to grasp the function. The commands for edit menu are simple enough that I didn't feel the need to pass in the post, that may change.

Lovely, another bug in the previous post. Replace the first echo line in the function select_command() with echo "<select onchange='updateMenu();' name='command'>"; It doesn't seem to matter how long I look at this stuff, something is always wrong with the post. Here I missed part of the edit. I have multiple copies of the same code, the code that actually runs on my machine, and the 'neutered' code that will actually display on a web page rather than execute. I try to keep them "the same", but they aren't.

New Connect:

The connect.php has been reworked a bit to accommodate auto-magical db and table creation. This involved dividing connect.php in twain adding a file called ~www/tutorial/include/sql_password.php which contains:

<?php
// this is an in-line and the only place where the db username
// and password should appear.
$db = "tutorial_db";
$link = mysql_connect('localhost', 'admin', 'password');
?>

And copy the following into ~www/tutorial/include/connect.php

<?php
// this is an in-line
$debug = false;
include "include/sql_password.php";
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
if($debug == true) {echo 'Connected to MySQL successfully<br>';}
mysql_select_db($db);
?>

There is a bit of a hazard here in the $debug = false; assignment. Since this is an include and not a function the variable $debug is in scope anywhere after you drop in the connect.php include. This means if you do something like this:

$debug = true;
include "include/connect.php";

You might think that $debug == true, but you would be wrong. You might want to remove debugging entirely from the connect but hang on for a bit. We want all the debugging turned on for the moment so we can see how the bits are flying. Maybe just removing the assign in the connect will get us what we want. The important thing here is to be come aware of the scope of variables. This is covered in exquisite detail in the references I have previously pointed out. You need to know this, Buckaroo.

Now we want to replace the function check_tables() in ~www/tutorial/include/check_tables.php with:

function check_tables() {
// Usage without mysql_list_dbs() deprecated
	include 'include/sql_password.php'; // connects us to db
	if (!$link) {die('Could not connect: ' . mysql_error());}
	mysql_select_db($db);
	$debug = false;
	if($debug == true){echo 'In check_tables() Connected SQL link='.$link.'<br />';}
	$res = mysql_query("SHOW DATABASES");
	if($debug == true){echo "checking database...<br />";}
	$present = false;
	while ($row = mysql_fetch_assoc($res)) {
		if($debug == true){echo $row['Database']." ";}
		if($row['Database'] == $db){$present = true;}
	}
	if($debug == true){echo "<br />";}
	if($present == false) { // db not present, creat it.
		echo "Database ".$db." not present. Creating...<br />";
		$sql = 'CREATE DATABASE '.$db;
		if (mysql_query($sql, $link)) {
			echo "Database ".$db." created successfully<br />";
		} else {
			echo 'Error creating '.$db.': ' . mysql_error() . "<br />";
			die(mysql_error()); // die a horrible death, can't go on...
		}
	}
	mysql_select_db($db);
	$sql = "SHOW TABLES FROM ".$db;
	$result = mysql_query($sql);
	if (!$result) {
		echo "DB Error, could not list tables\n";
		echo 'MySQL Error: ' . mysql_error();
		exit; // die a horrible death, can't go on...
	}
	$items_present = false; // flags for table creation
	$item_menus_present = false;
	while ($row = mysql_fetch_row($result)) { // paw through the results
		if($debug == true){echo "Table: {$row[0]} <br />";}
		//echo "Table: ."$row[0]." ";
		if($row[0] == 'items') {$items_present = true;}
		if($row[0] == 'item_menus') {$item_menus_present = true;}
	}
	mysql_free_result($result); //return some memory
	if($items_present == false) { //make items table
		create_items_table();
	}
	if($item_menus_present == false) { //make item_menus table
		create_item_menus_table();
	}
}

The above code uses the new connect formula. The actual table creation stays the same. One of the things to notice is that the user will be informed whenever a db or table is created, no mater the state of $debug. Another thing is that if we fail to make the db or the table for some reason we die a horrible death. The screen goes blank and truth is hard to see. View Source is your friend at that point, it will often show you just where you went awry. echo Statements are useful too.

Edit Menu:

Now we need to add the two new files. The first is edit_menu.php I am a bit torn about this. The object (noun) part of this is pretty small and the only really new elements are the function htmlspecialchars_decode() and the function select_generic() which is where we have been trying to get. The verb part though, I could have stripped out everything but the functionality of 'Add Menu', but then I'd have to put it right back in. Sigh.

Copy all of this into: ~www/tutorial/include/edit_menu.php

<?php 
/*	This function provides for the editing of the Item, Ingredient, and Meal
 * 	menus.
 * 	copyright 2011 by mumble all rights reserved
 *	file: edit_menu.php
 *	
 * 	2011/10/07 - initial working cut
 * 	2011/10/14 - update to current UI - working
* 	2012/02/21 - modified for tutorial - working
 * 
 * 	This function edits the menus created by the select_generic() objects which are used to 
 * 	look into the database.
*/

function edit_menu($_POST) 
{
	$New_menu = htmlspecialchars_decode($_POST["new_menu"]);
	$Current_menu = $_POST["menu_current"];
	$EM_command = $_POST["edit_menu_command"];
	$New_menu = htmlspecialchars_decode($_POST["new_menu"]);  // used in edit_menus as a source (is a textfield)
	
	$debug = false; if($debug) {echo "<br />In edit_menu() post_length=".count($_POST)."<br />";}	 	 
	if($debug) {$ta = array_keys($_POST);for($ti=0;$ti<count($ta);$ti++) {echo $ta[$ti]."=".$_POST[$ta[$ti]]."<br>";}} 	
		
	if ($Current_menu == '') {$Current_menu = '0';}

	// create a selector for which menu to edit
	// this probably ought to be a function but so far this is the
	// only time I have to do this one.
	echo "<select onchange='updateMenu()' name='menu_current'>";
	if($Current_menu == '0') {echo "<option selected='selected' value='0'>Items</option>";}
	else {echo "<option value='0'>Items</option>";}
	echo "</select>";
		if($Current_menu == '0') {select_generic('item',$_POST,'menu_only','_source');}
	echo "<input id='em_text' type='text' size='32' maxlength='64' value='".$New_menu."' name='new_menu'><br /> Destination: ";
		if($Current_menu == '0') {select_generic('item',$_POST,'menu_only','_destination');}
	echo "<br>Menu Commands: ";
	edit_menu_command();
	echo "<input id='em_exists' type='hidden' value='exists' name='edit_menu_exists'>";

}
// end of the function, the rest is included as the grammar of the function

// we begin with a little general housekeeping.
// set the value of the data bases being worked with
if ($Current_menu == '0') {$menu_db = 'item_menus';}

// before we actually try to execute any commands we see if the inputs are even vaguely reasonable.
if ($EM_command == 'Rename Menu' || $EM_command == 'Add Menu') {  // check for good inputs 
	if($New_menu == "") {
		$Error = "There must be text in the textfield to ".$EM_command; 
		$EM_command = "Menu Edit Commands"; // don't want to return here
	}
}
// fix up the global menu vars to point to the right menu set
if($Current_menu == '0') {$Menu_source = $_POST['item_menu_source'];$Menu_destination = $_POST['item_menu_destination'];}
/*
if($Current_menu == '1') {$Menu_source = $_POST['ingredient_menu_source'];$Menu_destination = $_POST['ingredient_menu_destination'];}
if($Current_menu == '2') {$Menu_source = $_POST['food_menu_source'];$Menu_destination = $_POST['food_menu_destination'];}
*/
//and now we should be ready to actually execute some commands

if ($EM_command == 'Rename Menu') {  // rename menu
	$debug = false;
	include 'include/connect.php';
	$query_str = "UPDATE ".$menu_db." SET name='".$New_menu."' WHERE index0='".$Menu_source."'";
	if($debug) {echo $query_str."<br>";}
	if(!$debug) {$result = mysql_query($query_str) or die(mysql_error());}
	if(!$debug) {$Feedback = "Menu: ".$Menu_source." named ".$New_menu." saved to DB.";}
	if($debug) {echo "Menu: ".$Menu_source." named ".$New_menu." NOT saved to DB.***debugging in edit_menu_rename***<br>";}
}

if ($EM_command == 'Add Menu') {  // add menu
	$debug = true;
	include 'include/connect.php';
	$query_str = "SELECT MAX(index0) as index0 FROM ".$menu_db."";
	if($debug) {echo $query_str."<br>";}
	// SELECT doesn't need a debug shield
	$result = mysql_query($query_str) or die(mysql_error());
	$row = mysql_fetch_array( $result );
	$new_index = $row['index0'] + 1 ;  // next new index
	if($debug) {echo "new index = ".$new_index."<br>";}
	$query_str = "INSERT INTO ".$menu_db." 
	(index0, name) 
	VALUES('".$new_index."', '".$New_menu."' )";
	if($debug) {echo $query_str."<br>";}
	if(!$debug) {$result = mysql_query($query_str) or die(mysql_error());}
	if(!$debug) {$Feedback = "Menu: ".$New_menu." at position ".$new_index." saved to DB.";}
	if($debug) {echo "Menu: ".$New_menu." at position ".$new_index." NOT saved to DB.***debugging in edit_menu_add***<br>";}
}

if ($EM_command == 'Delete Menu') {  // delete menu
	$debug = false;
	if($debug) {echo "Menu src = ".$Menu_source." Menu dst = ".$Menu_destination."<br>";}
	if ($Menu_source == $Menu_destination) { 
		$Error = "Source and Destination menus must be different.<br> Source menu contents get copied to the destination menu.<br> Delete Failed!</p><br>";
	} else {
		include 'include/connect.php';
		$query_str = "SELECT name FROM ".$menu_db." WHERE index0='".$Menu_source."'";
		if($debug) {echo $query_str."<br>";}
		// SELECT needs no debug shield
		$result = mysql_query($query_str) or die(mysql_error());
		$row = mysql_fetch_array( $result );
		$sname = $row['name'];  // source name
		$query_str = "SELECT name FROM ".$menu_db." WHERE index0='".$Menu_destination."'";
		$result = mysql_query($query_str) or die(mysql_error());
		$row = mysql_fetch_array( $result );
		$dname = $row['name'];  // destination name
		$query_str = "SELECT MAX(index0) as index0 FROM ".$menu_db."";
		$result = mysql_query($query_str) or die(mysql_error());
		$row = mysql_fetch_array( $result );
		$max_index = $row['index0'];  // biggest index before delete
		if($debug) {echo "max index = ".$max_index."<br>";}
		$query_str = "DELETE FROM ".$menu_db." WHERE index0='".$Menu_source."'";
		if($debug) {echo $query_str."<br>";}
		$result = mysql_query($query_str) or die(mysql_error());
		//echo "Menu: ".$sname." at position ".$Menu_source." deleted from DB.<br>";
		$Feedback = "Menu: ".$sname." at position ".$Menu_source." deleted from DB.<br>";
		$mv_index = $Menu_source;
		while ($mv_index <= $max_index) {
			//echo "working index = ".$mv_index." max = ".$max_index."<br>";
			$Feedback = "working index = ".$mv_index." max = ".$max_index."<br>";
			$query_str = "UPDATE ".$menu_db." SET index0='".$mv_index."' WHERE index0='".($mv_index + 1)."'";
			if($debug) {echo $query_str."<br>";}
			$result = mysql_query($query_str) or die(mysql_error());
			if($mv_index == $Menu_source) {
				//echo "Moving ".$thing_db."  on Menu ".$sname." to ".$dname."<br>";
				$Feedback = "Moving ".$thing_db."  on Menu ".$sname." to ".$dname."<br>";
				$query_str = "UPDATE ".$thing_db." SET menus='".$Menu_destination."' WHERE menus='".$Menu_source."'";
				if($debug) {echo $query_str."<br>";}
				$result = mysql_query($query_str) or die(mysql_error());
				//echo "".$thing_db."  moved from ".$sname." to ".$dname."<br>";
				$Feedback = "".$thing_db."  moved from ".$sname." to ".$dname."<br>";
			} else {
				echo "Adjusting menu ".$mv_index." in ".$thing_db."  db...<br>";
				$Feedback = "Adjusting menu ".$mv_index." in ".$thing_db."  db...<br>";
				$query_str = "UPDATE ".$thing_db."  SET menus='".($mv_index-1)."' WHERE menus='".$mv_index."'";
				if($debug) {echo $query_str."<br>";}
				$result = mysql_query($query_str) or die(mysql_error());
			}	
			$mv_index++;
		}
	}
}

if ($EM_command == 'Move Menu After') { // check for valid inputs to move after 
	$debug = false;
	//if($debug) {$ta = array_keys($_POST);for($ti=0;$ti<count($ta);$ti++) {echo $ta[$ti]."=".$_POST[$ta[$ti]]."<br>";}} 	
	if($debug) {echo "Menu_source = ".$Menu_source." Menu_destination = ".$Menu_destination."<br>";}
	include 'include/connect.php';
	$query_str = "SELECT name FROM ".$menu_db." WHERE index0='".$Menu_source."'";
	$result = mysql_query($query_str) or die(mysql_error());
	$row = mysql_fetch_array( $result );
	$sname = $row['name'];  // source name
	$query_str = "SELECT name FROM ".$menu_db." WHERE index0='".$Menu_destination."'";
	$result = mysql_query($query_str) or die(mysql_error());
	$row = mysql_fetch_array( $result );
	$dname = $row['name'];  // destination name
	if($Menu_source == $Menu_destination) { // 
		//echo "<p class='ex'>Can not move ".$sname." after ".$dname."</p><br>";
		$Error = "Move Menu After: Can not move ".$sname." after ".$dname." ";
		$EM_command = 'Menu Edit Commands'; // make command go away.
	}
}
if ($EM_command == 'Move Menu After') {  // move source menu after destination menu
	include 'include/connect.php';
	$query_str = "SELECT name FROM ".$menu_db." WHERE index0='".$Menu_source."'";
	$result = mysql_query($query_str) or die(mysql_error());
	$row = mysql_fetch_array( $result );
	$sname = $row['name'];  // source name
	$query_str = "SELECT name FROM ".$menu_db." WHERE index0='".$Menu_destination."'";
	$result = mysql_query($query_str) or die(mysql_error());
	$row = mysql_fetch_array( $result );
	$dname = $row['name'];  // destination name
	if($Menu_source == $Menu_destination) { // this should never execute
		//echo "<p class='ex'>Can not move ".$sname." after ".$dname."</p><br>";
		$Error = "Can not move ".$sname." after ".$dname." ";
		$EM_command = 'Menu Edit Commands'; // make command go away.
	}
	//echo "Moving menu item ".$sname." after ".$dname."<br>";
	$Feedback = "Moving menu item ".$sname." after ".$dname."<br>";
	// get the index one larger than is being used for temporary storage
	$query_str = "SELECT MAX(index0) as index0 FROM ".$menu_db."";
	$result = mysql_query($query_str) or die(mysql_error());
	$row = mysql_fetch_array( $result );
	$max_index = $row['index0'];  // biggest index 
	if($debug) {echo "max index = ".$max_index."<br>";}
	$mv_index = $Menu_source;
	if($debug) {echo "working index = ".$mv_index." max = ".$max_index."<br>";}

	if($Menu_source > $Menu_destination) { //moving source up, do this
		// move source to max+1  
		$query_str = "UPDATE ".$menu_db." SET index0='".($max_index + 1)."' WHERE index0='".($mv_index)."'";
		if($debug) {echo $query_str."<br>";}
		$result = mysql_query($query_str) or die(mysql_error());
		$query_str = "UPDATE ".$thing_db."  SET menus='".($max_index + 1)."' WHERE menus='".($mv_index)."'";
		if($debug) {echo $query_str."<br>";}
		$result = mysql_query($query_str) or die(mysql_error());
		
		// shift everyone from source-1 to dest up one
		for($mv_index = $Menu_source-1; $mv_index > $Menu_destination; $mv_index--) {
			$query_str = "UPDATE ".$menu_db." SET index0='".($mv_index+1)."' WHERE index0='".($mv_index)."'";
			if($debug) {echo $query_str."<br>";}
			$result = mysql_query($query_str) or die(mysql_error());
			$query_str = "UPDATE ".$thing_db."  SET menus='".($mv_index+1)."' WHERE menus='".($mv_index)."'";
			if($debug) {echo $query_str."<br>";}
			$result = mysql_query($query_str) or die(mysql_error());
		}
		// move max+1 to dest+1
		$query_str = "UPDATE ".$menu_db." SET index0='".($Menu_destination + 1)."' WHERE index0='".($max_index + 1)."'";
		if($debug) {echo $query_str."<br>";}
		$result = mysql_query($query_str) or die(mysql_error());
		$query_str = "UPDATE ".$thing_db."  SET menus='".($Menu_destination + 1)."' WHERE menus='".($max_index + 1)."'";
		if($debug) {echo $query_str."<br>";}
		$result = mysql_query($query_str) or die(mysql_error());
	} else {  // Moving src down the menu, do this
		// move source to max+1  
		$query_str = "UPDATE ".$menu_db." SET index0='".($max_index + 1)."' WHERE index0='".($mv_index)."'";
		if($debug) {echo $query_str."<br>";}
		$result = mysql_query($query_str) or die(mysql_error());
		$query_str = "UPDATE ".$thing_db."  SET menus='".($max_index + 1)."' WHERE menus='".($mv_index)."'";
		if($debug) {echo $query_str."<br>";}	
		$result = mysql_query($query_str) or die(mysql_error());
		
		// shift everyone from source+1 to dest up one
		for($mv_index = $Menu_source+1; $mv_index <= $Menu_destination; $mv_index++) {
			$query_str = "UPDATE ".$menu_db." SET index0='".($mv_index-1)."' WHERE index0='".($mv_index)."'";
			if($debug) {echo $query_str."<br>";}
			$result = mysql_query($query_str) or die(mysql_error());
			$query_str = "UPDATE ".$thing_db."  SET menus='".($mv_index-1)."' WHERE menus='".($mv_index)."'";
			if($debug) {echo $query_str."<br>";}
			$result = mysql_query($query_str) or die(mysql_error());
		}
		// move max+1 to dest
		$query_str = "UPDATE ".$menu_db." SET index0='".($Menu_destination)."' WHERE index0='".($max_index + 1)."'";
		if($debug) {echo $query_str."<br>";}
		$result = mysql_query($query_str) or die(mysql_error());
		$query_str = "UPDATE ".$thing_db."  SET menus='".($Menu_destination)."' WHERE menus='".($max_index + 1)."'";
		if($debug) {echo $query_str."<br>";}	
		$result = mysql_query($query_str) or die(mysql_error());
	}
}
	
?>

This is an interesting file. A lot of the complexity of the verb part has to do with deleting menus (the items need to be moved to a destination menu) and changing the ordering of menus. I chose to order the menus by the value of index0 which implies that as the index0 for a particular menu item, say 'dairy' changes from 4 to 0 to put it at the top of the menu, then all of the items that reference the menus pointed to by 0 or 4 need to be reworked. This is probably not so great. We really need probably yet another table that actually keeps the ordering. We'll try to remember to fix this.

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.

Disclaimer: Opinions posted on Free Republic are those of the individual posters and do not necessarily represent the opinion of Free Republic or its management. All materials posted herein are protected by copyright law and the exemption for fair use of copyrighted works.

Writing the Program: top

Back at the Server:

New Connect:

Edit Menu:

Select Generic:

Edit Items: