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.
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.