Now we re going to get a little tricky. Because the list of powers could get quite large, you want to try to distribute them across multiple columns. However, you would probably like to distribute them fairly evenly. The following 13 lines of code do this for you (if math is not interesting to you at all, or you simply don t want to know how this part of the code works, skip this section). First, you get a count of the number of powers in the array. Next, you set the threshold to 5 lines (after which a second column will be created), and a maximum number of columns (in this case, 3). $numpwr = count($pwrlist); $thresh = 5; $maxcols = 3; Next, you determine how many columns to create. Assume there are 7 powers to display. First, you divide the count by the threshold (7/5), which gives you 1.4. Next, you use ceil() to round up to the nearest integer (ceil (1.4) = 2). Then you take the smaller of the two values (3 and 2), and store it in the $cols variable. In this example, $cols would equal 2. To figure out how many powers go into each column, you divide the count by the number of columns, and round up to the nearest integer. In this case, ceil(7/2) = 4. So, you ll have two columns, with four values in each column (the last column will contain the remainder of powers if there are fewer than four). $powerchk is a string that will contain each power, with a checkbox attached to it. For now, you initialize it to an empty string . $cols = min($maxcols, (ceil(count($pwrlist)/$thresh))); $percol = ceil(count($pwrlist)/$cols); $powerchk = ; Now you loop through each element of the $pwrlist array, which contains the ID as the key ($id), and power as the value ($pwr). The counter $i will start at 0 and increment each time through the loop. In each loop, you add the tag to create the checkbox, using the ID as the value, and the name of the power as the label. When the counter reaches a value that is divisible by $percol, you add a close table definition and start a new one. $i = 0; foreach ($pwrlist as $id => $pwr) { if (($i>0) && ($i%$percol == 0)) { $powerchk .=

n
; } $powerchk .= $pwr
n ; $i++; } In this example, increments 0, 1, 2, and 3 end up in the first column. When $i reaches 4 (the value of $percol), the script starts a new column. If this is confusing, don t worry. You can play around with it by changing your $thresh and $maxcols values and adding a bunch of random power values to see how the table is built. For now, let s check out the rest of the code. 304 Chapter 10
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

3. Enter an ultra-cool superpower such as invisibility or X-ray vision in the text box, and click Add Power. If you need help with power ideas, here are a few: super strength, invisibility, X-ray vision, speed, soccer mom, stretchable, flight, breathes underwater. Add a total of six powers. Moving on, you should now see a new button and a list of powers with checkboxes next to them. 4. Check one or two powers and click Delete Powers. They should go away. How It Works You will see this on every page, but we will mention it this one time only. You include the config.php file that contains the constants used in the next couple of lines. By putting these constants in an included file, you can make any required changes in one place. You use the require command instead of include because of the way PHP works: An included file will not stop the processing of the rest of the page, whereas a required file, if not found, would immediately stop processing. require( config.php ); Next, a connection to the server is made, and the appropriate database is selected. Notice the use of the constants you defined in config.php: $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); What follows is a somewhat simple SQL select statement. It grabs the id and power columns from the char_power table and sorts them by power. This way, when you iterate through them later and put the data on the Web page, they will be in alphabetical order. $sql = SELECT id, power FROM char_power ORDER BY power ; The following code executes the SQL statement and throws an error if there are any problems: $result = mysql_query($sql) or die(mysql_error()); Now the script checks to make sure at least one row was returned. If so, it iterates through each row, building up an array of powers, using the power ID as the array key. Note the use of mysql_fetch_array. Other options are mysql_fetch_row and mysql_fetch_assoc. Using mysql_fetch_array gives you the flexibility to reference the results by numerical index or named index. if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_array($result)) { $pwrlist[$row[ id ]] = $row[ power ]; } When the script retrieves data from the database, it will usually need to retrieve appropriate ids so that you can later insert or update the correct record. In this case, the ID serves as the key to the array, making it easy to retrieve the values. You could have certainly used a multi-value array, but that gets a little more confusing, and it s just not necessary here. Just be sure you understand that many times in this application (and many applications using relational databases) you will use the table IDas an array key. 303 Building Databases
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

?>

Comic Book
Appreciation

Editing Character Powers

Return to Home Page 2. Load poweredit.php in your browser. When the page appears (see Figure 10-1), it initially will be empty. Figure 10-1 302 Chapter 10
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.

Each case sets a destination page after running its queries. This command will now send the user to that destination. Try It Out Editing Superhero Powers The next page you re going to create is a script to allow you to create and modify superhero powers. 1. Enter the following code in your favorite PHP editor, and save it as poweredit.php: 0) { while ($row = mysql_fetch_array($result)) { $pwrlist[$row[ id ]] = $row[ power ]; } $numpwr = count($pwrlist); $thresh = 5; $maxcols = 3; $cols = min($maxcols, (ceil(count($pwrlist)/$thresh))); $percol = ceil(count($pwrlist)/$cols); $powerchk = ; $i = 0; foreach ($pwrlist as $id => $pwr) { if (($i>0) && ($i%$percol == 0)) { $powerchk .=

n
; } $powerchk .= $pwr
n ; $i++; } $delbutton =

deleting will remove all associated powers
from characters as well — select wisely

; } else { $powerchk =

; $delbutton = ; $cols = 1; } 301 Building Databases
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

By this time, queries should seem quite familiar to you. The DELETE query is one of the simplest of the SQL statements. In these DELETE queries, you need to delete each power that was selected on the Add/Delete Power page. You must do this not only in the char_power table, but in the char_power_link table as well. (In this application, if a power is removed, you remove that power from the characters as well.) To perform a DELETE on multiple rows, you use the IN keyword, with which each ID in the supplied commaseparated list of power IDs is matched against the id, and each matching row is deleted. case Delete Powers : if ($powers != ) { $powerlist = implode( , , $powers); $sql = DELETE FROM char_power WHERE id IN ($powerlist) ; $result = mysql_query($sql) or die(mysql_error()); $sql = DELETE FROM char_power_link . WHERE power_id IN ($powerlist) ; $result = mysql_query($sql) or die(mysql_error()); } When adding a power, you first check to make sure a value was passed (no need to run a query if there is nothing to add), and then attempt to insert the value into the power table. Once again, you use the IGNORE keyword in what follows to avoid duplication of powers. We have mentioned that you really use IGNORE only on tables that have a primary key that is not autogenerated. There is an exception. IGNORE will not allow any duplicate data in any column that is designated as UNIQUE. In the char_power table, the power column is a UNIQUE column, so attempting to insert a duplicate value would result in an error. The IGNORE keyword prevents the insertion, so you don t get an error returned. If the power already exists, the script simply returns to the poweredit.php page and awaits further instructions. case Add Power : if ($newpower != ) { $sql = INSERT IGNORE INTO char_power (id, power) . VALUES (NULL, $newpower ) ; $result = mysql_query($sql) or die(mysql_error()); } You should always have a default: option in your case statements. You don t need to do anything there, but it is good programming practice to include it. In this case, you are simply going to redirect the user back to the charlist.php page. default: $redirect = charlist.php ; Finally, you reach the last command of char_transact.php. To use the header() function, no data can have been previously sent to the client. If it has, you will get an error. In this case, char_transact.php has no data sent to the client, so the header() function will work as advertised. header( Location: $redirect ); 300 Chapter 10
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

did not change; invisibility will still be deleted and reinserted). When updating data in an M:N relationship, you will usually simply delete the old data, and insert the updated/new data. $sql = DELETE FROM char_power_link WHERE char_id = $cid ; $result = mysql_query($sql) or die(mysql_error()); if ($powers != ) { $val = ; foreach ($powers as $key => $id) { $val[] = ( $cid , $id ) ; } $values = implode( , , $val); $sql = INSERT IGNORE INTO char_power_link (char_id, power_id) . VALUES $values ; $result = mysql_query($sql) or die(mysql_error()); } This brings you to the Enemies data, where not only do you have to maintain referential integrity, but you have to worry about updating rows where the ID can be present in either of the two linking columns. You must maintain the reverse referential integrity. $sql = DELETE FROM char_good_bad_link . WHERE good_id = $cid OR bad_id = $cid ; $result = mysql_query($sql) or die(mysql_error()); if ($enemies != ) { $val = ; foreach ($enemies as $key => $id) { $val[] = ( $cid , $id ) ; } $values = implode( , , $val); if ($align == good ) { $cols = (good_id, bad_id) ; } else { $cols = (bad_id, good_id) ; } $sql = INSERT IGNORE INTO char_good_bad_link $cols . VALUES $values ; $result = mysql_query($sql) or die(mysql_error()); } How did you deal with referential integrity? It turns out that it takes care of itself when you follow the same method you employed when updating the char_power_link table. By simply running the same DELETE query you ran when deleting a character, and then immediately running the same INSERT query you ran when creating a new character, you ensure that only one set of rows exists to match up each character to his/her enemy. It s simple, elegant, and it works! 299 Building Databases
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

case Delete Character : $sql = DELETE FROM char_main, char_lair . USING char_main m, char_lair l . WHERE m.lair_id = l.id AND m.id = $cid ; $result = mysql_query($sql) or die(mysql_error()); $sql = DELETE FROM char_power_link WHERE char_id = $cid ; $result = mysql_query($sql) or die(mysql_error()); You don t really need to put the results of the mysql_query command in a variable. We like to do this as a matter of habit because if you ever need the return value later, it will be available. In the case of a DELETE, you don t get a result set, you get a return value of either TRUE or FALSE. Remembering that the char_good_bad_link needs to maintain what we call reverse referential integrity (1, 3 matches 3, 1), you remove all rows that contain the character s ID in either column: $sql = DELETE FROM char_good_bad_link . WHERE good_id = $cid OR bad_id = $cid ; $result = mysql_query($sql) or die(mysql_error()); Updating a character is where things get interesting. First of all, you can simply do an INSERT IGNORE on the ZIP code table. If the address and ZIP code change, you don t really need to delete the old data because it might be used for other characters it s perfectly fine to leave the old data alone. So, you just do an INSERT IGNORE as you did for a new character, and leave it at that. case Update Character : $sql = INSERT IGNORE INTO char_zipcode (id, city, state) . VALUES ( $zip , $city , $state ) ; $result = mysql_query($sql) or die(mysql_error()); Here is the first UPDATE query, and incidentally, the only one in the entire application. It is very similar to INSERT and SELECT queries, with the exception of the SET keyword. The SET keyword tells MySQL what columns to set, and what values to set them to. The old values in the row are overwritten. This is a JOIN query because there is more than one table. The WHERE keyword specifies both the linking column (lair_id) and the condition that only rows for this character will be updated. $sql = UPDATE char_lair l, char_main m . SET l.zip_id= $zip , l.lair_addr= $address , . alias= $alias , real_name= $name , align= $align . WHERE m.id = $cid AND m.lair_id = l.id ; $result = mysql_query($sql) or die(mysql_error()); Because the char_power_link table does not have an automatically incremented column as the primary key, you don t have to do an update to the table. An update is possible, but it is much easier to simply delete all the old links of character to power, and insert new link rows. In some cases, you may be deleting and inserting the same data (for instance, you might be adding flight as a power, but invisibility 298 Chapter 10
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

There are a couple of concerns here. First, if there is already a power for this user (there shouldn t be; it s a new character, but always be prepared), you need to not insert the row. You already know how to take care of this by using the IGNORE keyword. Second, you must insert multiple rows of data with only one query. Easy enough; all you have to do is supply a comma-separated list of value groupings that matches up to the column grouping in the query. For example: INSERT INTO table (col1, col2) VALUES (val1, val2), (val3, val4) You accomplish this by looping through the $powers array and putting the values for character ID and power ID into a new array. You then concatenate that array with a comma separator, and voil ! There are your multiple rows of data to insert. You then do the same thing with the $enemies array that you did with $powers. This time, however, you insert into the columns based on whether the character is good or evil. It doesn t really matter too much which column gets which ID, but for the most part you want evil character IDs in the bad_id column. if ($enemies != ) { $val = ; foreach ($enemies as $key => $id) { $val[] = ( $charid , $id ) ; } $values = implode( , , $val); if ($align = good ) { $cols = (good_id, bad_id) ; } else { $cols = (bad_id, good_id) ; } $sql = INSERT IGNORE INTO char_good_bad_link $cols . VALUES $values ; $result = mysql_query($sql) or die(mysql_error()); } When it comes to the char_good_bad_link table, you have a little bit of referential integrity that you have to handle (beyond what MySQL does for you). Namely, you don t want to have a good_id/bad_id combination to match up to a bad_id/good_id combination. For the purposes of a relational database, that isn t bad, but for your purposes, that is considered a duplication. You will handle this contingency when updating a character, but because this is a new character (with a brand new id), you don t have to worry about that just yet. You re done inserting new character data, so you now set the page you are going to load next, and break out of the switch statement. $redirect = charlist.php ; break; When deleting a character, you simply remove all instances of it from all relevant tables. To remove the relevant data from the char_lair table, you have to JOIN it to the char_main table by matching up the lair ids first. Then you delete all matching rows where the character ID matches. 297 Building Databases
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

$sql = INSERT INTO char_lair (id, zip_id, lair_addr) . VALUES (NULL, $zip , $address ) ; $result = mysql_query($sql) or die(mysql_error()); You also could have left out the id field from the insert, and inserted values into the zip_id and lair_addr columns only. MySQL treats ignored columns as if you had attempted to insert NULL into them. We like to specify every column when doing an insert, though. If you needed to modify your SQL statement later, having all the columns in the INSERT query gives you a nice placeholder so all you have to do is modify the inserted value. The following is a neat little function. Assuming the insert worked properly ($result returned TRUE), the mysql_insert_id() function will return the value of the last auto_increment from the last run query. This works only after running a query on a table with an auto_incremented column. In this case it returns the primary key value of the row you just inserted into the char_lair table. You will need that value to insert into the char_main table. if ($result) { $lairid = mysql_insert_id($conn) } The connection variable is optional, but we think it s a good habit to always include it. If you omit it, it will use the most recently opened connection. In a simple application like this one, that s not a problem; in a more complex application where you might have more than one connection, it could get confusing. Again, note the use of NULL for the primary key id, and the use of mysql_insert_id() to return the primary key in the following: $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) } You are always interested in minimizing the number of times you run a query on the database. Each hit takes precious time, which can be noticeable in a more complex application. At this point, you need to figure out how to insert all the powers with only one SQL command: 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) . VALUES $values ; $result = mysql_query($sql) or die(mysql_error()); } 296 Chapter 10
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

is set to OFF by default in PHP versions after 4.2.0. (If you would like more information about this, visit www.php.net/register_globals.) You should always assume that register_globals is turned OFF to make your application more portable, and for this reason, we assume that you have access to the posted variables through the $_POST array only. What you are doing here is looping through $_POST and setting each variable yourself. If username was passed as $_POST[ username ], then it will now be accessible as $username, regardless of the register_globals setting. foreach($_POST as $key => $value) { $$key = $value; } Remember that each button is named action and that each one has a different value. In the code that follows, you determine which button was clicked, and run the appropriate code. For example, if the Delete Character button was clicked, you want to run the SQL commands only for removing character data. switch ($action) { The switch command is a fancy way of providing a multiple choice. It is easier to read than a complex if…else statement. The only gotcha you need to be aware of is to use break; at the end of each case to prevent the rest of the code in the other case blocks from executing. The INSERT query that follows is relatively simple. In plain English: Insert the values $zip, $city, and $state into the columns id, city, and state in the char_zipcode table. The IGNORE keyword is a very cool option that allows you do an insert without first using a SELECT query to see if the data is already in the table. In this case, you know there might already be a record for this ZIP code. So, IGNORE tells the query If you see this ZIP code in the database already, don t do the INSERT. case Create Character : $sql = INSERT IGNORE INTO char_zipcode (id, city, state) . VALUES ( $zip , $city , $state ) ; $result = mysql_query($sql) or die(mysql_error()); Note that the IGNORE statement compares primary keys only. Therefore, even if another ZIP code is in the database with the same state, the INSERT still takes place. Using IGNORE when inserting into a table where the primary key is automatically incremented has no effect at all; the INSERT will always happen in that case. This might seem obvious to you, but just keep this fact in mind; with some complex tables it won t be so intuitive. In the INSERT that follows, you see the use of NULL as the first value. When you insert NULL into a column, MySQL does the following: If the column allows NULL values, it inserts the NULL; if it does not allow NULL (the column is set to NOT NULL), it will set the column to the default value. If a default value has not been determined, then the standard default for the datatype is inserted (empty string for varchar/char, 0 for integer, and so on). If, as is the case here, the column is set to auto_increment, then the next highest available integer for that column is inserted. In this case, id is the primary key, so this is what you want to happen. 295 Building Databases
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.