Flexible Middle Tier
Flexible Middle TierNow we are prepared to build a PHP script that, simply by being pointed to the right database and table and being given some very vague specifications (the existence of two columns and their names ), can build an interactive form for displaying and editing all the records. We write a script that allows us to add a new user and to log returning users in properly and allow them to review and update all the data about their record. To do this we pass an XML object, called <member> , back and forth. Each tag on a child of <member> follows the same pattern. Output<name-of-data type valid>Current Value</name-of-data> The name-of-data will be the same as the name of the column that that datum will be placed in. The type attribute will be either "Fixed", "Number", "String16," or "String32". Number types ask the user to input pure numbers . "String16" indicates a string with a length limit of 16 characters . The actual type of a fixed element is irrelevant because the client does not permit it to be edited. Obviously, to simplify this portion of the code, we introduced to the other tiers knowledge about the limited variety of types actually in this database. If we were to extend our code to allow any type to be used, we would learn nothing special and would only increase the likelihood of contracting carpal tunnel syndrome. Stateless DesignEach transaction between client and server is independent; there is no automatic way to follow the history of a session (there really is no session). It is best to infer the state of a transaction from the client's XML upload (Figure 17.2). We have six distinct actions that can be triggered by the incoming data: Figure 17.2. Flowchart for the Stateless Database-Interaction Program. Yes, we must write code that does all this.
Note that we change all the data in the record, including the Password, but we never alter the UserName. One reason is that this makes the code much easier to write, but it also allows us to have the password change. The reason for this is that, client-side, to submit more than UserName and Password we have to have already either logged in or started the process of generating a new record. From that point on the UserName is fixed. Since we know that the password was known earlier by this client, we can accept any other password they give us as valid. PHP<? header( "Content-type: text/xml"); echo "<Member>"; // Parser taken from Chapter 15 global $HTTP_RAW_POST_DATA; $theElements=array(); ... reset( $theElements ); //End of borrowed code (Puts XML in Array titled $theElements) Is the array empty? We need at least a username and password! PHP if(sizeof($theElements)==0 ) { echo( "<UserName type = \ "String16\ " required = \ "True\ "/>" ); echo( "<Password type = \ "String16\ " required = \ "True\ "/>" ); die("</Member>"); } $UserName = $theElements["UserName"]; $Password = $theElements["Password"]; MySQL_connect("sql.useractive.com","bigfun","[REDACTED]"); MySQL_select_db("bigfun"); $is_old = MySQL_query ("select * from Members where UserName = \ "$UserName\ ""); If it is a new record, add it if all the required information is there. PHP if( !MySQL_num_rows( $is_old ) ) { $TableArchtype = MySQL_list_fields("bigfun", "Members"); for($i=0; $i < MySQL_num_fields( $TableArchtype ); $i++) { $Field = MySQL_fetch_field ($TableArchtype, $i); if($Field->not_null and !$theElements[$Field->name]) $Incomplete = 1; if($theElements[$Field->name]) { if($NotFirstTime++) { $ColumnsUsed .= ", "; $ValuesAssociated .= ", "; } $ColumnsUsed .= $Field->name; if($Field->numeric) $ValuesAssociated .= $theElements[$Field->name]; else $ValuesAssociated .= "\ "".$theElements[$Field->name]."\ ""; } } if(!$Incomplete) MySQL_query( "insert into Members ( $ColumnsUsed ) values ( $ValuesAssociated )"); } Now see if the password is valid. PHP $is_valid = MySQL_query ("select * from Members where UserName = \ "$UserName\ " && Password = \ "$Password\ ""); if(!(MySQL_num_rows($is_valid) or $Incomplete and (sizeof($theElements) > 2))){ echo '<UserName type = "Fixed">'.$UserName.'</UserName>'; echo '<Password type = "String16" valid = "False" required="True"> Try again </Password>'; die("</Member>"); } MySQL_free_result($is_valid); MySQL_free_result($is_old); $result = MySQL_query("select * from Members where UserName = \ "$UserName\ ""); $MemberData = MySQL_fetch_row($result); $TableArchtype = MySQL_list_fields("bigfun", "Members"); Output what data we have and update records. PHP for($i=0; $i < MySQL_num_fields($TableArchtype); $i++) { $Key = MySQL_field_name ($TableArchtype, $i); $Field = MySQL_fetch_field ($TableArchtype, $i); if($Key == "UserName") $Type = "Fixed"; else if($Field->numeric) $Type = "Number"; else if(MySQL_field_len($TableArchtype, $i) == 16) $Type = "String16"; else $Type = "String32"; $Value = $theElements[$Key]? $theElements[$Key] : ( $MemberData[$i]? $MemberData[$i] : "Fill Me In"); if( !$Incomplete ) $success = MySQL_query("update Members set $Key = \ "$Value\ " where UserName = \ "$UserName\ ""); echo "<$Key type=\ "$Type\">$Value</$Key>"; } echo "</Member>"; ?> Code DeconstructionPHP if(sizeof($theElements)==0 ) { echo( "<UserName type = \ "String16\ " required = \ "True\ "/>" ); echo( "<Password type = \ "String16\ " required = \ "True\ "/>" ); die("</Member>"); } This is a hardcoded response to an empty <member> XML object. Since the $theElements array consists of every tag within the <member> element, if the array is empty there must be no tags, hence no username and password. This is an initial request. The user requesting access needs to supply a username and password. So we send out the proper empty tags, letting the user fill them in. Then when the XML returns, it can be easily processed . PHP $is_old = MySQL_query ("select * from Members where UserName = \ "$UserName\ ""); if( !MySQL_num_rows( $is_old ) ) Essentially, these two lines test to see if a record for that username currently exists. If the record does not exist (the if is true), we know that this is a new user. PHP { $TableArchtype = MySQL_list_fields("bigfun", "Members"); for($i=0; $i < MySQL_num_fields( $TableArchtype ); $i++) { $Field = MySQL_fetch_field ($TableArchtype, $i); This code generates a result consisting of field names , not data records. We then loop through every field, assigning each to the variable $Field. PHPif($Field->not_null and !$theElements[$Field->name]) $Incomplete = 1; This code tests to see whether a field is required, and if so whether it was omitted by the user. If a required field is missing, then the record is still considered new. No data is added to the data base (which would reject any attempt to add the data because of the missing required value). Further, if this is a new record, the code for testing the password is skipped . Other than these required values, the database can accept any sparsely filled-in record. It looks to see if there is a value for each field. PHP if($theElements[$Field->name]) { If the field's name matches a tag, we want to insert that value into the database. To do so, we build up concatenated strings. One consists of the names of the columns we have data for. The second stores the associated values in the same order. PHP if($NotFirstTime++) { $ColumnsUsed .= ", "; $ValuesAssociated .= ", "; } If it is any time other than the first, we concatenate a comma and space to the end of our strings so that the list will be readable to MySQL. PHP$ColumnsUsed .= $Field->name; if($Field->numeric) $ValuesAssociated .= "\ ""$theElements[$Field->name]."\ ""; else $ValuesAssociated .= "".$theElements[$Field->name].""; } } Then concatenation of the name and the values occurs. If the field is not numeric, quotation marks need to be placed around the value to make it a string literal when MySQL reads it. The backslash double-quote is the escape sequence that produces a double-quote within a double-quote wrapped string literal. PHP if(!$Incomplete) MySQL_query("insert into Members ($ColumnsUsed) values ($ValuesAssociated)"); If it is not incomplete, meaning that all the required fields were present with some value, then a new record is created. The variable name $Incomplete flags whether the record will be inserted into the database in time to be tested by the password validator. So if it is ready, the record is created; otherwise , it is not. NOTE If we used an AUTO_INCREMENT ing field to produce a UIN, we could find its value with this query (used right after the insert operation). MySQL_insert_id(); It returns the AUTO_INCREMENT field value in the insert operation. PHP $is_valid = MySQL_query ("select * from Members where UserName = \ "$UserName\ " && Password = \ "$Password\ ""); if(!(MySQL_num_rows($is_valid) or $Incomplete or (sizeof($theElements) > 2))) { The if statement has three conditions. If any one is true, the script ends early and sends an invalid password tag back to the client. MySQL_num_rows ($is_valid) This statement is true if MySQL_num_rows ($is_valid) points to a non-empty table. There is a record whose UserName and Password fields match PHP's $UserName and $Password. $Incomplete This statement resolves to true if the record is not in the database and the user has not supplied enough information yet to add it. (sizeof ($theElements) > 2) More than two fields indicate the user has already logged in, because the client is echoing tags that the server has supplied from the database. The client's new information can be safely written to the server except the UserName, which the client software makes static. PHP echo '<UserName type = "Fixed">'.$UserName.'</UserName>'; echo '<Password type = "String16" valid = "False" required="True"> Try again </Password>'; die("</Member>"); } If the password is bad, the login fields are returned after the bad password is overwritten and the flag for invalid entry is set. Then the script exits while completing the XML packet. PHP $result = MySQL_query("select * from Members where UserName = \ "$UserName\ ""); $MemberData = MySQL_fetch_row($result); $TableArchtype = MySQL_list_fields("bigfun", "Members"); for($i=0; $i < MySQL_num_fields($TableArchtype); $i++) { $Key = MySQL_field_name ($TableArchtype, $i); $Field = MySQL_fetch_field ($TableArchtype, $i); Here we cycle through all the fields. if($Key == "UserName") $Type = "Fixed"; else if($Field->numeric) $Type = "Number"; else if(MySQL_field_len($TableArchtype, $i) == 16) $Type = "String16"; else $Type = "String32"; This code chooses a valid value for the XML type attribute by examining the MySQL field's properties. The username is the only read-only field because it is the key for this table and therefore must be unique and static. Obviously, if a field is numeric its type is a number. Since the only things not numbers are strings, and since the only two sizes of a string are 16 and 32, we can use the length of the two to differentiate. Of course, we cheated just a tad by restrict ing the nonnumeric values, but it is easy to see how we could handle the relaxation of these restrictions. $Value = $theElements[$Key]? $theElements[$Key] : ( $MemberData[$i]? $MemberData[$i] : "Fill Me In"); if( !$Incomplete ){ $success = MySQL_query( "update Members set $Key = \ "$Value\ " where UserName = \ "$UserName\ ""); echo "<$Key type=\ "$Type\">$Value</$Key>"; } First the value that will be passed back is determined. The priority of possible things passed back is new information submitted by the user, old information currently in the database, and default information (constant). We then update the data in the table. We do this only if a record exists, so we test the new record flag in the if statement. If we want to check how many rows were affected by our update (it should be one), we report PHPMySQL_affected_rows(); This code returns the number of rows affected by the last query delete or update. Finally, we format the tags and send them off.  |
![Flash and XML[c] A Developer[ap]s Guide](/icons/blank_book.jpg "Flash and XML[c] A Developer[ap]s Guide"){kind=icon}