PHP Web Hosting, Tomcat, JSP, J2EE, Servlets, Struts - PHP4, PHP5 Programming Blog

Try It Out Transaction Script Some of these files are a bit long. Don t let that scare you. Most of the code consists of SQL statements, and they are explained clearly for you in the How It Works section that follows. Remember that this code can also be downloaded from the Web site (www.wrox.com). 1. Let s start with a transaction script. This code is the longest, but that s because it contains a lot of SQL statements. It s not as bad as it looks. But if you want to download this code from the Web site, go ahead, and be guilt-free. Consider it our gift to you. If you are typing it, you know the drill. After entering it, save this one as char_transact.php: $value) { $$key = $value; } $conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS) or die( Could not connect to MySQL database. . mysql_error()); mysql_select_db(SQL_DB, $conn); switch ($action) { case Create Character : $sql = INSERT IGNORE INTO char_zipcode (id, city, state) . VALUES ( $zip , $city , $state ) ; $result = mysql_query($sql) or die(mysql_error()); $sql = INSERT INTO char_lair (id, zip_id, lair_addr) . VALUES (NULL, $zip , $address ) ; $result = mysql_query($sql) or die(mysql_error()); if ($result) { $lairid = mysql_insert_id($conn); } $sql = INSERT INTO char_main (id,lair_id,alias,real_name,align) . VALUES (NULL, $lairid , $alias , $name , $align ) ; $result = mysql_query($sql) or die(mysql_error()); if ($result) { $charid = mysql_insert_id($conn); } if ($powers != ) { $val = ; foreach ($powers as $key => $id) { $val[] = ( $charid , $id ) ; } $values = implode( , , $val); $sql = INSERT IGNORE INTO char_power_link (char_id, power_id) . 291 Building Databases
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

. Alignment (good or evil) . Powers . Enemies You also need a character input form. This form will serve two purposes. It will allow you to create a new character, in which case the form will load with blank fields and a create button, or it will allow you to edit an existing character, in which case it will load with the fields filled in, and an update button. The form will also have a reset button that either clears the new form, or restores the edited form fields. A delete button should also be available when editing an existing character. The fields on your form will be as follows: . Character name (alias) . Real name . Powers (multiple select field) . Lair address, city, state, and ZIP . Alignment (radio button: good/evil, default good) . Enemies (multiple select field) You also need a form for adding and deleting powers. This form will be relatively simple and will contain the following elements: . A checkbox list of every power currently available . A Delete Selected button . A text field to enter a new power . An Add Power button You also need a PHP script that can handle all database inserts, deletes, and so on. This is called a transaction page, and it simply does a required job and redirects the user on to another page. This page handles all transactions for the character application (with redirect), including the following: . Inserting a new character (character listing page) . Editing an existing character (character listing page) . Deleting a character (character listing page) . Adding a new power (power editor page) . Deleting a power (power editor page) That s basically all there is to the application. Four pages (five if you count the config.php file you created earlier it will be used again) shouldn t be too difficult. You ll write them first, and then we ll talk about how they work. 290 Chapter 10
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

The code auto_increment causes the column to increase the highest value in the table by 1 and store it in this column. A column set to auto_increment does not have a default value. . alias varchar(40) NOT NULL default : the alias column is set as a varchar datatype, a string of 0 to 255 characters. You are allotting 40 characters, which should be enough for any character name. Avarchar differs from a char datatype by the way space is allotted for the column. A varchar datatype occupies only the space it needs, whereas char datatypes will always take up the space allotted to them by adding spaces at the end. The only time you really need to use the char datatype is for strings of known fixed length (such as the State column in the char_zipcode table). . real_name varchar(80) NOT NULL default : Similar to alias. You are allotting 80 characters, which should be enough for your needs. Note that you did not separate the real_name column into first_name and last_name columns. If you wanted to do that, you certainly could, but in this small application it really isn t necessary. On the other hand, in a human resources application for your company, having separate columns for first and last name is almost a requirement, so that you can do things such as greet employees by their first names in a company memo. . lair_id int(11) NOT NULL default 0: The foreign key to the char_lair table is also an integer of length 11, with a default value of 0. . align enum( good , evil ) NOT NULL default good : The align column can be one of two values: good or evil. Because of this, you use an enum datatype, and default it to good. (Everyone has some good in them, right?) You now have a database. You have tables. If you just had a way to enter some data into your tables in your database, you d have an application you could give to your users, where they could store information about their favorite superheroes and villains. You could enter the data with some query statements in PHPMyAdmin, but that probably wouldn t be too efficient, not to mention that your users wouldn t have any access to it. You need some sort of interface for them that they can use to create and edit data, which means you need to design some Web pages for them. Creating the Comic Character Application It s back to the drawing board. Literally. Get away from your computer. You re going to put together some ideas for a Web application. First of all, you need a page to display a list of comic book characters along with some information about them. It doesn t need to include every detail about them (such as the location of their secret lair), but it should have enough data so that users can distinguish who they are and read a little bit of information about them. You will list the following information: . Character name (alias) . Real name 289 Building Databases
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

How It Works Every PHP script that needs to access your database on the MySQL server will include config.php. These constants will be used in your scripts to gain access to your database. By putting them here, in one file, you can change the values any time you move servers, change the name of the database, or change your username or password. Any time you have information or code that will be used in more than one PHP script, you should include it in a separate file. That way, you ll only need to make your changes in one location. define( SQL_HOST , localhost ); define( SQL_USER , bp5am ); define( SQL_PASS , bp5ampass ); define( SQL_DB , comicsite ); The make_tables.php file is a one-time script: You should never have to run it again, unless you needed to drop all of your tables and re-create them. So, rather than explain all of the code in the page, we ll just look at one of the SQL statements: CREATE TABLE IF NOT EXISTS char_main ( id int(11) NOT NULL auto_increment, alias varchar(40) NOT NULL default , real_name varchar(80) NOT NULL default , lair_id int(11) NOT NULL default 0, align enum( good , evil ) NOT NULL default good , PRIMARY KEY (id) ) The syntax for creating a table in MySQL is the following: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,…)] [table_options] [select_statement] Obviously, you are not using the TEMPORARY keyword, because you want this table to be permanent and exist after your connection with the database. You are using the IF NOT EXISTS keyword, but only if this page is loaded twice. If you attempt to load the page again, MySQL will not attempt to re-create the tables and will not generate an error. The table name in this case is char_main. The columns the script creates are id, alias, real_name, lair_id, and alias, which are the names we came up with earlier. Let s look at each column: . id int(11) NOT NULL auto_increment: The id column is set as an integer, with 11 maximum places. An integer datatype can contain the values -2147483648 to 2147483648. A sharp observer would note that the max value is only 10 digits. The eleventh digit is for negative values. NOT NULL will force a value into the column. With some exceptions, numeric columns will default to 0, and string columns will default to an empty string ( ). Very rarely will you allow a column to carry a NULL value. 288 Chapter 10
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

PRIMARY KEY (id) ) ; $sql2 = CREATE TABLE IF NOT EXISTS char_power ( id int(11) NOT NULL auto_increment, power varchar(40) NOT NULL default , PRIMARY KEY (id) ) ; $sql3 = CREATE TABLE IF NOT EXISTS char_power_link ( char_id int(11) NOT NULL default 0, power_id int(11) NOT NULL default 0, PRIMARY KEY (char_id, power_id) ) ; $sql4 = CREATE TABLE IF NOT EXISTS char_lair ( id int(11) NOT NULL auto_increment, zip_id varchar(10) NOT NULL default 00000 , lair_addr varchar(40) NOT NULL default , PRIMARY KEY (id) ) ; $sql5 = CREATE TABLE IF NOT EXISTS char_zipcode ( id varchar(10) NOT NULL default , city varchar(40) NOT NULL default , state char(2) NOT NULL default , PRIMARY KEY (id) ) ; $sql6 = CREATE TABLE IF NOT EXISTS char_good_bad_link ( good_id int(11) NOT NULL default 0, bad_id int(11) NOT NULL default 0, PRIMARY KEY (good_id,bad_id) ) ; mysql_query($sql1) or die(mysql_error()); mysql_query($sql2) or die(mysql_error()); mysql_query($sql3) or die(mysql_error()); mysql_query($sql4) or die(mysql_error()); mysql_query($sql5) or die(mysql_error()); mysql_query($sql6) or die(mysql_error()); echo Done. ; ?> 4. Run make_table.php by loading it in your browser. Assuming all goes well, you should see the message Done in your browser. The database now should contain all six tables. 287 Building Databases
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

. Another option is to run your SQL statement from a PHP file. Most likely, if you are hosted by an ISP, it won t allow the creation of databases in this manner. However, almost any other SQL statement will work using this method. This is the way we will be running SQL commands through the rest of this chapter. Once you have determined how you are going to run that SQL command, go ahead and do it. Make sure you substitute your own database name for yourdatabase. Because you are going to develop a comic book appreciation Web site, you could call it comicsite: CREATE DATABASE IF NOT EXISTS comicsite; Now that you have a design mapped out and a database created in MySQL, it is time to create some tables. Try It Out Creating the Tables In this exercise, you ll create the file that will hold the hostname, username, password, and database values. 1. Open your favorite text editor, and enter the following code (making sure you use the proper values for your server): 2. Save the file as config.php. This file will be included in each subsequent PHP file that needs to access the database. 3. Type the following code in your favorite PHP editor, and save it as make_table.php: We recommend high quality webhost to host and run your jsp application: christian web host services.

id* city state varchar(10) varchar(40) char(2) good_id* bad_id* int(11) int(11) We think it is about time you actually created these tables on the server. Ready? Not so fast: You have to create the database first. Creating a Database in MySQL You can create a database in a number of ways. All require the execution of a SQL statement in one way or another, so let s look at that first: CREATE DATABASE yourdatabase; What, were you expecting something more complicated? Well, an optional parameter is missing: IF NOT EXISTS. We re pretty sure you know whether or not it exists, but if it makes you feel better, you can certainly add that: CREATE DATABASE IF NOT EXISTS yourdatabase; That s all there is to it. Think of the database as an empty shell. There is nothing special about it, really. The interesting stuff comes later, when you create the tables and manipulate the data. That said, you still have to figure out how you are going to execute that SQL statement. Here are a few suggestions: . From the MySQL command prompt. Do it this way only if you have access to the server on which MySQL is installed. If you are running your own server, or you have telnet access to the server, this may be an option for you. . If you are being hosted by an ISP, you may need to request that the ISP create a database for you. For example, on one author s site the ISP has CPanel installed, and he simply clicks the module called MySQL Databases. From the next page, he simply types in the database he wants to create and clicks a button, and it s created for him. ISPs will usually give you this option because you have a limit in your contract on how many databases you are allowed to create. On one of our sites, for example, the limit is 10 databases. . If you have PHPMyAdmin installed (either on your own server or through your ISP), you can then run the SQL command from there. PHPMyAdmin is a valuable tool, and we recommend you use it if that is an option for you. It allows you to see your table structures and even browse data. It is a dangerous tool, however, because you can easily drop tables or entire databases with the click of a button, so use it carefully. 285 Building Databases
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

. Column names: Table columns are similar to table names. All column names will be in lowercase. They will be kept short, but multiple words (such as lair and address) will be separated by an underscore _ (lair_addr). . Primary keys: Single primary keys will always be called id . Except in special cases, primary keys will be an integer datatype that is automatically incremented. If they consist of a single column, they will always be the first column of the table. . Foreign keys: Foreign keys will end with _id . They will start with the table descriptor. For example, in the char_lair table, the foreign key for the char_zipcode table will be called zip_id. Finalizing the Database Design One other thing we like to do during the database design process is put the datatypes into the empty cells of each table. You can print these tables and easily refer to them when you are writing the SQL code. You may want to do this yourself (or just use the tables provided). If you don t understand datatypes, you can learn about them in Chapter 3, and datatypes are discussed in more detail a little later in this chapter as well. For now, just understand that datatypes are the type of data stored in each table column, such as INT (integer), VARCHAR (variable-length character string), or ENUM (enumerated list). When appropriate, they are followed by the length in parentheses; for example, varchar(100) is a character column that can contain up to 100 characters. If you have been working in a spreadsheet, simply erase all of the actual data in those tables. If you used a pad and pencil, just follow along. Reduce the tables to two rows, one with column names, the other row blank. If you want, you can make a copy before erasing the data. In keeping with the previously listed table standards, we arrive at the following tables. Yours should look very similar. id* lair_id name real_name align int(11) int(11) varchar(40) varchar(80) enum( good , evil ) id* power int(11) varchar(40) char_id* power_id* int(11) int(11) id* zip_id lair_addr int(11) varchar(10) varchar(40) 284 Chapter 10
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

What s So Normal About These Forms? Remember we told you to call the first table zero ? That s called Zero Form. It is basically the raw data, and is usually a very flat structure, with lots of repeated data. You see data like this sometimes when a small company keeps records of its customers in a spreadsheet. The first pass through the table, which you called pass one, was the first step of normalization, called First Normal Form, or 1NF. This step requires that you eliminate all repeating data in columns (which you did with the power column), create separate rows for each group of related data, and identify each record with a primary key. The first step satisfies the requirements of 1NF. You can see where we re going with this, can t you? The Second Normal Form (2NF) requirements state that you must place subsets of data in multiple rows in separate tables. You did that by separating the power data into its own table. Second Normal Form also requires that you create a relationship with the original table by creating a foreign key. You did that in pass two, when you satisfied the requirements for 2NF. On your third pass, you removed all the columns not directly related to the primary key (city and state), and used the ZIP code as the foreign key to the new city_state table. Third Normal Form (3NF) is then satisfied. Congratulations! You normalized a database just like the pros do. There are further requirements for database normalization, but Third Normal Form is generally accepted as being good enough for most business applications. The next step is Boyce-Codd Normal Form, followed by Fourth Normal Form and Fifth Normal Form. In this case, the other Forms don t apply the database is as normalized as it needs to get. All tables are easily modifiable and updateable, without affecting data in the other tables. We know there are some database gurus out there who would tell you that in order to completely satisfy the Forms of normalization, that the align column should be put into its own table and linked with a foreign key. While that may be true in the strictest sense of the rules, we usually think of normalization as a guideline. In this case, we have only two values, good and evil. Those values will never change, and they will be the only values available to the user. Because of this, we can actually create a column with the ENUM datatype. Because the values good and evil will be hardcoded into the table definition, and we don t ever see a need to change the values in the future, there is no problem with keeping those values in the char_main table. Standardization When you are designing a new application, it is a very good idea to come up with standards, or design rules, that you adhere to in all cases. These can be extensive, such as the standards published by the W3C for HTML, XML, and other languages. They can be very short, but very strict, such as the list of 10 standards brought down from a mountain by an old bearded man. For now you ll just standardize your table structure. For this application, we came up with the following table standards: . Table names: Table names should be descriptive, but relatively short. Table names will be in lowercase. They should describe what main function they serve, and what application they belong to. All six tables should start with char_ to show that they belong to the character application. 283 Building Databases
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

id* lair_id name real name align 1 1 Clean Freak John Smith Good 2 1 Soap Stud Efram Jones Good 3 2 The Dustmite Dustin Hare Evil id* zip_id lair address 1 45293 123 Poplar Avenue 2 45201 452 Elm Street #3D id* city st 45293 Townsburg OH 45201 Burgtown OH You may have noticed that you have created a many-to-many (M:N) relationship between the characters and their powers (a character can have multiple powers, and many characters may have the same power). There are two tables with primary keys, and a linking table between them has two foreign keys, one for each of the tables. The combination of the foreign keys is a primary key for the char_power table. This enables the M:N relationship. Just for fun, add a small table that links the superheroes to villains, and vice versa. This is another M:N relationship because any superhero can have multiple villain enemies, and any villain can have multiple superhero enemies. Of course, you have the character table as one of the many sides of the equation can you figure out what table to use for the other many side? If you said the character table, you are correct! This is just like the character-power relationship, but this time you reference the table to itself via a good_bad linking table. The goodguy_id and badguy_id columns each link to the id column in the character table. Each column in the good_bad table is a foreign key, and both columns make up a composite primary key. goodguy_id* badguy_id* 1 3 2 3 And just like that, you have created your database design. Congratulations! You now have a map that will help you create your database tables on the server. Not only that, but you just normalized your database design as well by modifying your database table structure so that dependencies make sense, and there is no redundant data. In fact, you have actually gone through the proper normalization steps of First, Second, and Third Normal Form. 282 Chapter 10
In case you need quality webspace to host and run your web applications, try our personal web hosting services.