7. Forms and Webapps

What We Will Cover Announcements Please silence all cell phones and pagers When class is over, please shut down your computer if it is on Elucidations 7.1: Organizing Your Web Application 7.1.1: About Application Organization 7.1.2: Basic Application Structure 7.1.3: Including Files 7.1.4: Using include and require for Templates Exercise 7.1 7.1.5: Summary 7.2: Creating and Processing Web Forms 7.2.1: Creating Web Forms 7.2.2: Creating a Form Element 7.2.3: Input Elements 7.2.4: Other Form Elements 7.2.5: Processing Form Data with PHP Exercise 7.2 7.2.6: Summary 7.3: Working With Forms in PHP 7.3.1: Inserting Form Data into MySQL 7.3.2: How To Insert Data Into a Single MySQL Table 7.3.3: Saving Data in Two Tables Exercise 7.3 7.3.4: Issues in Writing to Databases 7.3.5: When to Use Locks 7.3.6: Using LOCK TABLES and UNLOCK TABLES 7.3.7: Summary Wrap Up Due Next: A7-Collecting User Input (4/16/12) Quiz 7 and Discussion 7 (4/16/12) Elucidations Questions from last class? What is missing from the following code? $result = mysql_query("SELECT * FROM Products"); print $result; Homework Questions? A6-PHP Connections (4/2/12) Quiz 6 and Discussion 6 (4/2/12) Quiz Questions? Blackboard ^ top 7.1: Organizing Your Web Application Learner Outcomes At the end of the lesson the student will be able to: Describe how to organize their web application project Use include and require commands ^ top 7.1.1: About Application Organization Your course project is a type of software known as a web application, or webapp A web application is a program accessed with a browser In our projects, the application is written using PHP The browser, using HTML, provides the user interface for the application Typically you start with a few web pages and your application grows over time Managing Growth Managing your PHP web application as it grows can be time consuming Unless it is implemented properly from the start As web sites grow, they tend to become less organized Pieces of code that you use become harder to locate When you want to change your site, mysterious bugs appear You can avoid these problem if you organize your application correctly How You Use Files in a Web Application HTML files provide the content organization and structure for web pages CSS files control the appearance of your pages Image files provide graphics and pictures PHP provides the intelligence and logic for your web application Other technologies can be added to these four core types such as JavaScript Check Yourself A computer program accessed with a browser is known as a(n) ________. True or false: you should organize your application so it can change and grow over time. Of the following, ________ is not one of the four core technologies for our webapps. CSS HTML Flash PHP ^ top 7.1.2: Basic Application Structure We organize our application code into folders (directories) Folders are organized in a tree-like structure starting at a root folder At the root of the tree is our starting folder You place all your hyperlinked Web pages in the root folder of your webapp (webapp root) This makes it easier to hyperlink from one page to another Note that HTML documents are usually saved with a PHP extension We should put all the files supporting your web pages in a subfolder This reduces the clutter in the webapp root Usually, we put files of different types in separate subfolders I suggest the following structure for your web applications Web Application Folder Structure webapp root (all web pages: .html, .php)      includes (supporting PHP files)      images (image files)      script (JavaScript files)      style (CSS and other style files) Note that the includes subfolder is required for your project The includes folder is where we put dbconvars.php, among other included files All the other folders are optional and you can rename them as you like Also, we may add other subdirectories depending on your application Other Considerations Keep the folder structure simple and avoid multiple levels of subfolders Never use spaces or special characters in folder or file names Web servers must do extra work and use special characters to handle spaces This makes spaces confusing for URLs and links Also, some programs cannot handle spaces in names at all Similar problems occur for special characters like: *, ?, >, <, # Avoid use of underline characters ( _ ) because they look like spaces in hyperlinks Also, avoid use of capital letters in folder and files names Some server operating systems are case sensitive and some are not Following an all lowercase convention can prevent problems when moving your application from one server to another Check Yourself True or false: your should organize webapp folders in a tree-like structure under one starting folder. The starting folder of a webapp is known as the webapp ________ folder. True or false: using spaces and special characters in file and folder names never causes a problem. ^ top 7.1.3: Including Files To include external files, PHP has four different commands: include include_once require require_once All these commands insert the content of one file into another We have used require_once() already when including the dbconvars.php file: require_once "includes/dbconvars.php"; Commands include and require act the same in most ways except how they handle errors The commands include only issues a warning while require produces a fatal error The commands include_once and require_once are similar to include and require The difference is they will not include code that has already been included before The commands with their descriptions are listed in the following table Note that when files are included, they start out in HTML mode Thus, any PHP code must be placed in <?php ... ?> tags Also note that you can use a variable to specify the file name Commands for Including External Scripts Command Description include Includes and evaluates the specified file. Failure during the evaluation produces a warning, which can be suppressed. include_once Includes and evaluates the specified file one time only. Failure during the evaluation produces a warning, which can be suppressed. require Includes and evaluates the specified file. Failure during the evaluation halts processing of the page. require_once Includes and evaluates the specified file one time only. Failure during the evaluation halts processing of the page. Caution: Always Append Included Files With .php For included files, some people like to use the file extension: .inc However, many Web servers will serve pages ending in .inc as text Thus we should always end our include files with .php This helps prevent accidental exposure of passwords and other sensitive data Check Yourself Of the following, ________ is NOT one of PHP's commands for inserting external files into the current document. import include include_once require If you want to prevent a file from being insert more than once into the current document, use either the commands include_once or ________. True or false: a great way to indicate included files is to use the file extension: ".inc". ^ top 7.1.4: Using include and require for Templates HTML and PHP code tends to repeat on many pages For instance: Every well-formed HTML page has opening and closing tags Connections to a database are usually made the same way each time Rather than repeating the code on every page, we put them into an included file instead Then when we need to make changes, we only change the included file Example of Including Files Let us put together a page layout for Artzy Art Supplies As use div tags to organize the page and CSS for positioning and style The organizational div tags have these id fields: wrapper: the main container div for all content header: defines the top banner area of the page main: defines the main content area of the page footer: defines the footer navigation area of the page We put the code for the start of a page into a file named header.php Similarly, we place the code for the end of a page into a file named footer.php Then we create our pages by including these files View the main page to see how it looks and the CSS file to see how the div tags are positioned Web Page File main.php <?php $title = "Page Title"; include "includes/header.php"; ?> <h1>Main Content Area</h1> <p>This is the main content area.</p> <p>Place HTML and PHP code between the includes for header and footer.</p> <?php include "includes/footer.php"; ?> Included File header.php <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8"> <?php if (isset($other)) echo $other; ?> <title><?php echo $title ?></title> <link href="style/artzy.css" rel="stylesheet" type="text/css"> </head> <body> <div id="wrapper"> <div id="header"> <img src="images/artzy.gif" alt="Artzy logo"> </div> <div id="nav"> <em>Quick Links</em> <br><a href="index.php">Home</a> <br><a href="about.php" title="About Us">About Us</a> <br><a href="products.php" title="Shop!">Products</a> <br><a href="cart.php" title="Your Cart">View Cart</a> <br><a href="members.php" title="Members Only">Members</a> <br><a href="logout.php" title="Log Out">Log Out</a> </div> <div id="main"> Included File footer.php </div> <div id="footer"> <a href="about.php" title="About Us">About Us</a> &nbsp; <a href="products.php" title="Shop!">Products</a> &nbsp; <a href="cart.php" title="Your Cart">View Cart</a> &nbsp; <a href="members.php" title="Members Only">Members</a> &nbsp; <a href="logout.php" title="Log Out">Log Out</a> &nbsp; </div> </body> </html> Modifying the Page Layout When you need to change the page layout, all the code is in one place Your whole site can change by modifying a single file Later on we will look at better ways to organize the content of each page Check Yourself True or false: one advantage of collecting repeating code into included files is that changes are easier to make. The purpose of the ________ div is to display a navigational area. The purpose of the ________ div is to display the main content. The purpose of the ________ div is to display a navigational footer area. ^ top Exercise 7.1 In this exercise we organize a simple webapp. While we are organizing the application we will look at how to use PHP includes. Specifications Open the XAMPP Control Panel by double-clicking the icon on the desktop or by using the Apache Friends entry in the start menu. Start the Apache and MySQL modules, if they are not already running. Locate the htdocs directory of your Apache (XAMPP) installation and create a subdirectory (folder) in htdocs named phptest. If you installed XAMPP on Windows using the default settings, this directory is C:\xampp\htdocs. On other systems you will need to search for the htdocs directory. In the phptest directory you just made, create four subdirectories named: includes, images, script and style. When you are finished, your directory structure should look like: phptest      includes      images      script      style We will use only some of the subdirectories during this lesson and others in future lessons. For more information see section 7.1.2: Basic Application Structure. Start TextPad (or another text editor), enter the following code, and save the code in a file named main.php to the phptest directory you just created. <?php $title = "Page Title"; include "includes/header.php"; ?> <h1>Main Content Area</h1> <p>This is the main content area.</p> <p>Place HTML and PHP code between the includes for header and footer.</p> <?php include "includes/footer.php"; ?> Notice that we have placed the main.php page in the webapp root. Also notice that we have referred to two included files but have not added the files to the includes directory yet. For more information see section 7.1.3: Including Files. View the file as a web page by opening a new Web browser tab or window and entering localhost/phptest/main.php in the address bar. You should see a page like the following in the browser, including the warnings: Notice the warnings about the missing header.php and footer.php files. We will correct these problem in the next steps. Since we used the keyword include rather than require, the page still displays though with added warnings. If we had used require instead, then the page would have stopped right after the first warning with a message, "Fatal error..." For more information see section 7.1.3: Including Files. Enter the following code in your text editor and save the code in a file named header.php to the includes directory you created earlier. <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8"> <?php if (isset($other)) echo $other; ?> <title><?php echo $title ?></title> <link href="style/artzy.css" rel="stylesheet" type="text/css"> </head> <body> <div id="wrapper"> <div id="header"> <img src="images/artzy.gif" alt="Artzy logo"> </div> <div id="nav"> <em>Quick Links</em> <br><a href="index.php">Home</a> <br><a href="about.php" title="About Us">About Us</a> <br><a href="products.php" title="Shop!">Products</a> <br><a href="cart.php" title="Your Cart">View Cart</a> <br><a href="members.php" title="Members Only">Members</a> <br><a href="logout.php" title="Log Out">Log Out</a> </div> <div id="main"> To finish the included files, enter the following code in your text editor and save the code in a file named footer.php to the includes directory you created earlier. </div> <div id="footer"> <a href="about.php" title="About Us">About Us</a> &nbsp; <a href="products.php" title="Shop!">Products</a> &nbsp; <a href="cart.php" title="Your Cart">View Cart</a> &nbsp; <a href="members.php" title="Members Only">Members</a> &nbsp; <a href="logout.php" title="Log Out">Log Out</a> &nbsp; </div> </body> </html> Reload (refresh) the main.php Web page again and notice that the warnings have disappeared and that you now see a traditional side menu and bottom menu on the page. Notice how small the main.php page is at this point. All the opening HTML tags and side menu code is in the header.php file. Also, the closing HTML tags and bottom of the page menu are in the footer.php file. This is a common page layout template but not the only possibility. For more information see section 7.1.4: Using include() and require() for Templates. Let us add an image by saving the artzy.gif file in the images subdirectory. When you reload (refresh) the main.php Web page you should see the Artzy logo at the top of the page. Notice the image tag is in the header.php file. This shows how you can add media tags to PHP included files. To finish the example, save the artzy.css file in the style subdirectory. When you reload (refresh) the main.php Web page you should see different colors and fonts. You can compare your finished page by comparing it to the online example. Save the HTML code generated by the three PHP files, using the name main.html, to submit to Blackboard as part of assignment 7. In Firefox, right click and select "Save Page As" with "Web Page, HTML only" as the file type. In IE, press the "Page" button and then "Save As" with the Save as type box set to "Webpage, HTML only". Make sure you save the HTML using the name main.html. As time permits, be prepared to answer the Check Yourself questions in the section: 7.1.5: Summary. ^ top 7.1.5: Summary Starting with an organized web site will save time even in the short term You can make additions and changes with minimal problems You organize your application code into directories like this: webapp root (all web pages)    includes (supporting PHP files)    images (image files)    script (JavaScript files)    style (CSS files) In this section we looked at how the content of one file in another To include external files, PHP has four different commands: include include_once require require_once You should keep common code, both HTML and PHP, in included files This lets you to reuse code from page to page Then when you need to make changes, you only need to edit one or two files Quick Quiz Using spaces in file or folder names is a good way to separate words. True False AnswerFalse You place your dbconvars.php file in the _______includes folder. Which of the following is NOT one of the four commands for including files in PHP? include() include_once() include_twice() require() AnswerC Check Yourself What is a web application? (7.1.1) What is the top level directory of your webapp called? (7.1.2) Why should you never use spaces in a file or folder name? (7.1.2) Where should you place all the hyperlinked Web pages of your application? (7.1.2) What is the name of the folder in which you must put dbconvars.php? (7.1.2) What are the four commands PHP supplies for including files? (7.1.3) What is the difference between using include() and require()? (7.1.3) What file extension should you use for included files? (7.1.3) What is the advantage of using included files for your page layout code? (7.1.4) ^ top 7.2: Creating and Processing Web Forms Learner Outcomes At the end of the lesson the student will be able to: Create HTML input forms Pass data from HTML forms to PHP scripts ^ top 7.2.1: Creating Web Forms An important of most webapps is to collect information from people This is often done using Web forms The elements of a form in which a user can enter information are called control elements Once the data from a form is collected, it must be sent to the server for processing We will use PHP to process the form data on the server You can see a form example below with several control elements You can view the web page for the form by clicking here Example Web Form ^ top 7.2.2: Creating a Form Element You construct a form using the syntax: <form attributes> page elements </form> Form attributes control how the form is processed Page elements include form control elements but can include other HTML elements as well such as tables, paragraphs and headings Here is an example form element with some attributes defined: <form action="formecho.php" method="GET" name="cust"> </form> Some important attributes include: action: specifies the URL where the form sends the data for processing method: specifies how to send the information The GET method appends the information to the URL The POST method sends the data within the body of the request You usually use the post method name: useful for differentiating between forms if a page has more than one form PHP processes a script using the name to identify the form element Check yourself The ________ element lets us create forms for collecting data. The purpose of the action attribute in a form element is to specify the ________ to send the form data for processing. We normally assign the method attribute either the value ________ or ________. ^ top 7.2.3: Input Elements Most form elements in which users enter data are marked with an input element Syntax of an input element: <input type="type" name="name" value="value"> The name attribute is required to process the data with PHP Many of the elements require the value attribute to send a value to a PHP script Which elements need the value attribute set is indicated in the following table When the form is submitted, the name and value are sent to the server as name=value pairs HTML has 10 different input types as shown in the following table Each of them has various attributes that specify their behavior You can view the source to see how some of the attributes are used For more information on possible attributes see: INPUT - Form Input Input Types Type Description Example type="button" Display a button to perform an action from a script type="checkbox" Display a check box type="file" Display a control to locate and select a file type="hidden" Create a hidden field not viewable in the form type="image" Display an inline image that can be clicked to submit data type="password" Display an input box that hides text entered by the user type="radio" Display an option (radio) button One Two type="reset" Display a button to clear the form data when clicked type="submit" Display a button to submit the form data when clicked type="text" Display an input box that displays text entered by the user Check Yourself True or false: the input element will display different controls depending on the type attribute. To create a checkbox, set the type attribute to ________. Of the following input types, ________ does NOT display a button. button reset submit text ^ top 7.2.4: Other Form Elements You can use some other form elements that input for entering data We look at some of those ways in this section Selection Lists A selection list is a list box from which a user selects a value or values Selection lists are a good idea when the field's input has a fixed set of possible responses By presenting the list of possibilities, the user is less likely to enter the wrong data You create a selection list with a select tag Nested within the select tag are the individual items specified with the option tag The syntax for the select and option elements is: <select name="name" id="id"> <option value="value1">Item1</option> <option value="value2">Item2</option> ... </select> Where: name: the name of the control id: the name of the element value: the value of the control when an item is selected Item: the label for the item presented to the user For example, this code produces the selection list that follows 1 2 3 4 5 <select name="state"> <option value="CA">California</option> <option value="OR" selected="selected">Oregon</option> <option value="WA">Washington</option> </select> California Oregon Washington When the form is submitted, the name and selected value are sent to the server as a name=value pair Text Area Boxes Text areas are like text boxes except a user can enter multiple lines The syntax to create a text area box is: <textarea name="name" rows="value" cols="value"> default text </textarea> For example, this code produces the text area that follows: <textarea name="comments" rows="3" cols="50"> Your comments here </textarea> Your comments here The name attribute allows the receiving script to identify the form element The rows attribute sets the number of lines The cols attribute sets the number of columns Text areas also need a closing tag Any text between the tags appears as the initial default text When the form is submitted, the name and entered text are sent to the server as a name=value pair More Information Form Elements: reference from the Web Design Group Forms in HTML Documents: the specification from W3C Check Yourself To create a multiple-line text area, use the ________ element. The select element displays preselected options created with the ________ element. ^ top 7.2.5: Processing Form Data with PHP PHP makes it easy to process input data from forms When a form is submitted, all the name and value data are sent to the server as name=value pairs We can access the name=value data using one of the superglobal arrays such as $_REQUEST For instance, given the following input element: <input type="text" name="myname" size="20"> We use $_REQUEST with square brackets and the element name like this: $myname = $_REQUEST["myname"]; print "myname = $myname"; If you only want to retrieve data sent using the GET method, use $_GET instead $myname = $_GET["myname"]; print "myname = $myname"; If you only want to retrieve data sent using the POST method, use $_POST instead $myname = $_POST["myname"]; print "myname = $myname"; We will explore this process in more depth in the next exercise Superglobal Arrays Used with Forms Array Description $_GET Access to variables passed via the HTTP GET method. $_POST Access to variables passed via the HTTP POST method. $_REQUEST Access to variables passed via both the HTTP GET and POST methods. Debugging Forms One valuable technique for debugging forms is to use function var_dump() For example, to view all the variable data sent from a browser use: var_dump($_REQUEST); Insert the above code near the top of your script that receives the form data Then you will see all the variables sent from the form Check Yourself PHP accesses the values from form fields using the ________ attribute. Enter the php code to access the value to the text field with the name attribute of "foo". One possible answer To view the content of the entire $_REQUEST array, use the PHP function ________ statement. More Information PHP and HTML Variables from outside PHP var_dump() ^ top Exercise 7.2 In this exercise we explore how to create forms and process form data using PHP. Specifications Copy the following PHP script into a text editor, save the file in your phptest directory as form.php, and then view the file as a Web page in your browser. <?php $title = "PHP Script"; include "includes/header.php"; ?> <h1><?php print $title; ?></h1> <!-- Enter HTML here --> <?php include "includes/footer.php"; ?> The page only displays a heading so far. Even though we are saving the file as a PHP page, presenting forms and collecting form data is all part of HTML. We only use PHP to include the header and footer files. Change the title to, "Delivery Form": $title = "Delivery Form"; When you reload (refresh) the Web page, notice that both the title bar and heading now display, "Delivery Form". Outside the PHP tags and after the h1 tag, add the following form tag where indicated: <form action="formecho.php" method="GET" name="cust"> <!-- Enter form elements here --> </form> Notice the attributes of the form tag. The action attribute tells the browsers where to send the form data. The method attribute tells the browser how to send the form data. We use the name attribute to uniquely identify the form element. For more information see section 7.2.2: Creating a Form Element. Complete the form by adding the following form controls inside the form element: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 <fieldset> <legend>Enter your shipping information</legend> <table> <tr> <td class="labelcell">First Name</td> <td class="inputcell"> <input type="text" name="fname" size="35"> </td> </tr> <tr> <td class="labelcell">Last Name</td> <td class="inputcell"> <input type="text" name="lname" size="35"> </td> </tr> <tr> <td class="labelcell">Street</td> <td class="inputcell"> <textarea name="street" cols="30"></textarea> </td> </tr> <tr> <td class="labelcell">City</td> <td class="inputcell"> <input type="text" name="city" size="35"> </td> </tr> <tr> <td class="labelcell">State</td> <td class="inputcell"> <select name="state"> <option value="CA">- Select One -</option> <option value="CA">California</option> <option value="OR">Oregon</option> <option value="WA">Washington</option> </select> </td> </tr> <tr> <td class="labelcell">ZIP</td> <td class="inputcell"> <input type="text" name="zip" size="10"> </td> </tr> </table> </fieldset> <p id="formbuttons"> <input type="button" name="prevb" value="Previous" onclick="history.back()"> <input type="submit" name="nextb" value="Next"> </p> Notice that we are using several types of form controls: input elements, text area elements and selection lists. For more information on input elements see section 7.2.3: Input Elements. For more information on text areas and selection lists see section 7.2.4: Other Form Elements. Reload (refresh) the Web page and verify you see the following form: If you press the Next button of your page, you will get an Object not found! (404) error. We still need to create the PHP script that processes the form data. Copy the following PHP script into a text editor and save the file in your phptest directory as formecho.php: <?php $title = "PHP Script"; include "includes/header.php"; print "<h1>$title</h1>"; // Enter PHP code here include "includes/footer.php"; ?> Then change the title to, "Form Data". $title = "Form Data"; Fill out your form with some sample data and press the Next button. So far you will not see any data from the form. To see the form data we have to tell PHP to access and display the data. Add the following PHP code after the print statement to display the title and before the include statement for the footer.php file: $fname = $_REQUEST["fname"]; print "<p>First Name: $fname</p>"; The first line accesses the form data and saves the value of the fname element in a variable named $fname. The second line displays the saved value. For more information see section 7.2.5: Processing Form Data with PHP. Reload (refresh) the formecho.php page and verify you see the first name displayed from your form data. If you do not see the first name, then verify your work including reentering the form data. If you still do not see the first name, ask a class mate or the instructor for help. Write PHP statements like the above to collect the values of every form element from you file and display the values in the Web page. When finished, your formecho.php page should like: Reload (refresh) the formecho.php page and verify you see the values of all the form data that you entered. In addition, notice that all the form element names and values are present in the address bar as name=value pairs. While this information is useful for debugging forms, usually you do not want this information as part of the URL. We will change this form behavior in the next step. In your form.php file, change the method of the form to use POST rather than GET: <form action="formecho.php" method="POST" name="cust"> The method attribute tells the browser how to send the form data. For more information see section 7.2.2: Creating a Form Element. Reload (refresh) the form.php page, fill out your form with some sample data and press the Next button. Notice that you no longer see the form element names and values in the address bar. Save your completed form.php and formecho.php files to submit to Blackboard as part of assignment 7. Note that there is no need to submit any other file from this exercise. As time permits, be prepared to answer the Check Yourself questions in the section: 7.2.6: Summary. ^ top 7.2.6: Summary In this section, we looked at how to create Web forms Forms are created using a form element like the following: <form action="formecho.php" method="GET" name="cust"> </form> Inside the form you put control elements like: <input type="text" name="myname" size="20"> When the form is submitted, the name and value data are sent to the server as name=value pairs You can access the name=value data using one of the superglobal arrays such as $_REQUEST For instance, given the following input element: <input type="text" name="myname" size="20"> We use $_REQUEST with square brackets and the element name like this: $myname = $_REQUEST["myname"]; print "myname = $myname"; Check Yourself What is the purpose of a form? (7.2.1) What is the syntax for creating a form element in an HTML page? (7.2.2) What is the purpose of the action attribute of the form element? (7.2.1) What is the purpose of the method attribute of the form element? (7.2.1) All input elements have a similar syntax though they display different form controls. What is the common syntax of a form input element? (7.2.3) What is the syntax for creating a selection list? (7.2.4) What is the syntax for creating a text area? (7.2.4) What PHP command can you use to access any submitted form variable? (7.2.5) What form control attribute is used by PHP to access form values? (7.2.5) What PHP command can you use to access only form values submitted using the POST method, while ignoring those submitted using the GET method? (7.2.5) ^ top 7.3: Working with Forms in PHP Learner Outcomes At the end of the lesson the student will be able to: Save data from HTML forms in MySQL ^ top 7.3.1: Inserting Form Data into MySQL So far we have collected data from a form and displayed the form values in another PHP page We accessed and displayed the form data using PHP statements like: $street = $_REQUEST["street"]; print "<p>Street: $street</p>"; $city = $_REQUEST["city"]; print "<p>City: $city</p>"; $state = $_REQUEST["state"]; print "<p>State: $state</p>"; $zip = $_REQUEST["zip"]; print "<p>Zip: $zip</p>"; Displaying information from a form is one thing, but we want to save our form data into a database To save the form data we access the form data using statements like those above We then use a SQL INSERT statement to map the values to the correct tables and columns of our database For example, if we use the variables from the above code, we create a SQL INSERT statement like: $sql = " INSERT INTO addresses(Address, City, State, Zip) VALUES ('$street', '$city', '$state', '$zip') "; Note that you can have extra whitespace in PHP strings. This can make formatting your SQL statements easier In this section we examined the steps need to process the form data and save it in a MySQL database As we shall see, adding data to two tables becomes more complicated We will look at how to solve the problem of inserting related data into multiple tables in the next section Checking and Filtering Input One of the largest problems for web applications is user input Users either accidentally or maliciously enter incorrect data You need to check and filter input before entry into a database We have not learned very many PHP tools to perform these checks yet We will add techniques to our "toolbox" as we progress through this course One tool you can use right away is PHP's trim function This function strips whitespace from the beginning and ending of a string Sometimes users enter leading or trailing spaces around their data SQL queries with extra spaces do not match data that does not have leading or trailing spaces To prevent this problem, you can filter user input like this: $street = trim($_REQUEST["street"]); Since the right-hand is evaluated first, then you can store "trimmed data" in your variables Check Yourself True or false: To insert data into MySQL from PHP, we write an INSERT statement. If the user enters the value "Ed" into the text field named "fname", the following code will produce the SQL statement ________: $fname = $_REQUEST["fname"]; $sql = "INSERT INTO people(fname) VALUES('$fname')"; To remove leading and training space from form input, use the PHP function ________. ^ top 7.3.2: How To Insert Data Into a Single MySQL Table Since we can get form data, we can enter it into a database In this section we look at the steps for inserting data into a single MySQL table Steps for Inserting Data Create a connection and select a database. require_once("includes/dbconvars.php"); $dbCnx = mysql_connect($dbhost, $dbuser, $dbpwd); mysql_select_db($dbname, $dbCnx); Save the $_REQUEST values in a variable like this: $supplierName = $_REQUEST["supplierName"]; $supplierCode = $_REQUEST["supplierCode"]; Code a SQL INSERT statement into a PHP string using the variables you created before: $sql = " INSERT INTO suppliers VALUES (NULL, '$supplierName', '$supplierCode') "; print "<p>$sql</p>\n"; Note that you can have extra whitespace in PHP strings. This can make formatting your SQL statements easier. Also note that the SQL statement should not contain a semicolon (according to the PHP manual) though you still need a semicolon at the end of the PHP statement. In addition, it is a good practice to print your SQL statement for verification and debugging. You can remove the print statement when the query works correctly. Execute your SQL query and check the results. mysql_query($sql) or die("Query failed: ".mysql_error()); Note that the mysql_query() function returns true if an INSERT operation was error-free and false on error. If you want to find out how many rows were affected, call the function mysql_affected_rows(). $numRows = mysql_affected_rows(); print "Rows affected: $numRows\n"; If the number of rows affected is zero then the INSERT failed. If the number of rows affected is one then the INSERT succeeded. Check Yourself True or false: it is good practice to print the SQL statements you generate in PHP so you can verify the statement is correct. If the $sql code in the following statement causes an error, the ________ function reports the error message from MySQL. mysql_query($sql) or die("Query failed: ".mysql_error()); To find out how many rows were affected by a SQL query, call the function ________. True or false: a successful INSERT operation normally affects one row of a table. ^ top 7.3.3: Saving Data in Two Tables Saving related data in two tables is more complicated than saving in one table We need the primary key of the row from the first table for use in the second table Because the primary key is a foreign key of the second table Recall that MySQL has a function called LAST_INSERT_ID() Returns the last automatically generated value inserted into an AUTO_INCREMENT column If many rows are inserted at the same time, it returns the ID of the first insertion PHP has a similar command called mysql_insert_id(). Runs the MySQL LAST_INSERT_ID() function and returns its value We can save the returned value for use as the foreign key in the second table Look at how this works in the following example Also note the use of single and double quotes in the following example Use single quotes for SQL strings and double quotes for PHP strings Example Inserting Data into Two tables // Insert form values into the first table $sql = " INSERT INTO addresses(Address, City, State, Zip) VALUES ('$street', '$city', '$state', '$zip') "; print "<p>sql=$sql</p>\n"; mysql_query($sql) or die("Query failed: ".mysql_error()); $numRows = mysql_affected_rows(); print "<p>Rows affected: $numRows</p>\n"; // Insert form values into the second table $addressID = mysql_insert_id(); $sql = " INSERT INTO customers(LName, FName, AddressID) VALUES ('$lname', '$fname', $addressID) "; print "<p>sql=$sql</p>\n"; mysql_query($sql) or die("Query failed: ".mysql_error()); $numRows = mysql_affected_rows(); print "<p>Rows affected: $numRows</p>\n"; Another Technique Another technique is to search for the highest AUTO_INCREMENT value after the first INSERT: $sql = "SELECT MAX(ID) FROM Addresses"; $result = mysql_query($sql); $addressID = mysql_result($result, 0, 0); This works because AUTO_INCREMENT always returns a higher number for every row inserted We then use the value extracted from the query as the foreign key in the second table However, this technique is not as safe as using mysql_insert_id() The reason is that if you use a SELECT query and someone else inserts a row in the milliseconds between your INSERT and the SELECT, you could get a wrong value returned On the other hand, the value of mysql_insert_id() is affected only by statements issued within the current client connection It is not affected by statements issued by another client connection Check Yourself The PHP function that calls MySQL's LAST_INSERT_ID() function is named ________. True or false: if table A needs a primary key from table B to use as a foreign key, then the data must be inserted into table B first. True or false: selecting the maximum value of a primary key field is a risky alternative to using MySQL's LAST_INSERT_ID() function. ^ top Exercise 7.3 In this exercise we explore how to save form data in MySQL using PHP. For this exercise you will need the Artzy database installed, which we completed in Exercise 2.1. Specifications Save the following connection information in a file named dbconvars.php in a subdirectory named includes of the phptest directory: <?php $dbhost = "localhost"; $dbuser = "root"; $dbpwd = ""; $dbname = "artzy"; ?> Change the values assigned to $dbuser and $dbpwd to match your MySQL installation. For more information see lesson 6.3.1: Connecting to a MySQL Database. Using your final form.php from the last exercise, change the action attribute to send the form data to a PHP file named formdb.php: <form action="formdb.php" method="POST" name="cust"> The action attribute tells the browser where to send the form data. For more information see section 7.2.2: Creating a Form Element. Without destroying your file, save the formecho.php file from the last exercise as formdb.php in the phptest directory. We will use the code we have generated so far as part of our script to save the form data into the Artzy database. Fill out your form with some sample data and press the Next button to verify your setup is correct. After pressing the Next button, your browser should be on the formdb.php page and you should see the form data you entered. If you do not see the form data, then verify your work including reentering the form data. If you still do not see the form data, ask a class mate or the instructor for help. In your formdb.php file, add the PHP statements to connect to the Artzy database. Add these statements just before the statement to include the footer.php file and after all the other statements to access and display form values. require_once "includes/dbconvars.php"; $dbCnx = mysql_connect($dbhost, $dbuser, $dbpwd) or die("Could not connect: ".mysql_error()); mysql_select_db($dbname, $dbCnx) or die("Could not select db: ".mysql_error()); For more information see lesson 6.3.1: Connecting to a MySQL Database. Now we need to write a SQL INSERT statement for our form data. Add the following code after the connection information: $sql = " INSERT INTO addresses(Address, City, State, Zip) VALUES ('$street', '$city', '$state', '$zip') "; print "<p>sql=$sql</p>\n"; For more information on INSERT statements see lesson 3.2.2: Inserting Data. Also, notice the extra whitespace in the PHP string named $sql. This layout can make formatting your SQL statements easier and MySQL ignores the extra whitespace. Reload (refresh) the formdb.php page and examine the SQL INSERT statement your page created. Verify that the SQL INSERT statement is correct. If there are any errors in syntax or logic, then make corrections before continuing. After the statements that creates the SQL INSERT query, and before the include statement for footer.php, add a statement to execute a query on the MySQL database: mysql_query($sql) or die("Query failed: ".mysql_error()); In addition, add the following PHP commands to check the number of rows affected by the query: $numRows = mysql_affected_rows(); print "<p>Rows affected: $numRows</p>\n"; For more information see section 7.3.2: Inserting Data Into a Single MySQL Table Reload (refresh) the formdb.php page and examine the number of rows affected. If the number of rows affected is zero then the INSERT failed. If the number of rows affected is one then the INSERT succeeded. Examine the artzy database using phpMyAdmin and verify the form data you entered now appears in the addresses table. Only part of our form data was saved in the addresses table and the rest of the data needs to be saved in the customers table. Add the following PHP statements after the rest of the statements but before the statement to include the footer.php file: $addressID = mysql_insert_id(); $sql = " INSERT INTO customers(LName, FName, AddressID) VALUES ('$lname', '$fname', $addressID) "; print "<p>sql=$sql</p>\n"; mysql_query($sql) or die("Query failed: ".mysql_error()); $numRows = mysql_affected_rows(); print "<p>Rows affected: $numRows</p>\n"; The mysql_insert_id() function returns the last automatically generated value inserted into an AUTO_INCREMENT column. For more information see section 7.3.3: Saving Data in Two Tables. Examine the artzy database using phpMyAdmin and verify the form data you entered now appears in both the addresses and customers tables. In addition, verify that the AddressID foreign key in the customers table matches the primary key of the address entered into the addresses table. One problem we have not addressed is checking the values entered into a form. Typically you use conditional statements to check form values. We have not covered conditional statements yet, but we can use the trim() function to prepare our data. In the formdb.php file, modify all the statements creating variables to use the trim() function using code like: $fname = trim($_REQUEST["fname"]); The trim() function removes all the leading and trailing whitespace from the form values. For more information see section 7.3.4: Checking and Filtering Input. Save your completed formdb.php file to submit to Blackboard as part of assignment 7. Note that there is no need to submit any other file from this exercise. As time permits, be prepared to answer the Check Yourself questions in the section: 7.3.7: Summary. ^ top 7.3.4: Issues in Writing to Databases A web application is a multiple-user environment Typically, some users are inserting, updating or deleting data while most users are reading data When multiple users read and write to a database at the same time (that is, concurrently) problems can occur The problems become complex when you need your script to process a series of SQL statements as a group A series of statements that must be processed as a group is known as a transaction The following are some of the more common problems of concurrent read and write transactions Lost Update Problem The lost update problem occurs when one completed update is overwritten by another user Consider the case of a two managers order supplies for Artzy Art Supplies One manager makes a query on the inventory for painting brushes and notices a shortage with only 3 remaining However, before ordering, the manager decides to refill his coffee cup In the meantime, another manager notices the shortage and orders 25 more brushes, updating the inventory to display 28, knowing the brushes will arrive the same day The first manager returns from refilling his coffee cup and orders 25 more brushes, also updating the inventory to show 28 brushes All 50 brushes come in that afternoon, but the system only shows 28 in inventory Incorrect Summary Problem Incorrect summaries occur when one transaction is reading many values but a second user updates one of the values in the middle of the reads For example, a manager for Artzy Art Supplies wants to produce a stock report of all the products in inventory The report is complex and has several steps: First the total sales for each product are tallied Then the sales for each supplier are tallied Finally, the total sales are tallied While the report is running, a customer buys a product such as a new canvas If this purchase happens during the second step, then the tallies for the 3 steps will be different Unrepeatable Read Problem A similar problem to the incorrect summary is the unrepeatable read One user reads a value from the database and later rereads the value for verification However, in the meantime, another user has updated the value read by the first user When the first user reads to verify the value, it is different or unrepeatable For instance, consider a purchase in Artzy Art Supplies One customer puts the last canvas into her shopping cart To complete the transaction, she must login and enter her password While she is fumbling with her password, another customer logs in and completes the purchase of the last canvas Now the first customer tries to complete her transaction but cannot because the database reports there are no more items in stock Dirty Read Problem A dirty read happens when one user updates the database but then changes his mind In the meantime, another user accesses the value before it is rolled back As an example, suppose that one manager at Artzy decides to increase the sales price of oil paints by 5% per tube, and updates the database Meanwhile, another manger notices that paint sales are slow and decides to put all paints on sale at 10% off, and updates the database The first manger decides the price increase was a mistake and wants to roll the price change back However, he cannot update correctly because the original price has been lost Check Yourself A series of statements that must be processed as a group is known as a ________. True or false: a web application may have several users accessing a database at the same time. True or false: it is possible to lose data when multiple users access a database. ^ top 7.3.5: When to Use Locks The problems discussed in the previous section can be solved using a system known as two-phase locking (or locks) Phase 1: locks are acquired Phase 2: locks are released The MyISAM table, which is all that many ISPs support, has two types of table locking that can be used to support two-phase locking: READ lock: all users can only read from a table (no updates or inserts allowed) WRITE lock: only the user holding the lock can write to or read from a table (other users cannot read or write) Note that locking can slow or stop databases processing until the lock is released Thus you want to minimize the use of locks in your database queries Situations NOT Requiring a Lock Simple inserts, updates or deletions of rows that do not use results of a previous SELECT statement or data entered by a user as input Examples: updating customer details, adding a new address entry or unconditionally deleting a row of data Applications where only one user can alter the data Mulit-User Situations Requiring a Lock A script first reads a value from a database and later writes that value to the same database For example: using SELECT with MAX() to find the highest value of a primary key and then using that value when inserting a new row A script first writes a value to a database and then reads the value back from the database For example: updating an inventory value and then reading that value back with a SELECT statement to show the user the result of the update Avoiding Locks Through Careful Query Design Use MySQLs AUTO_INCREMENT feature to create new primary key values Then use mysql_insert_id() to find the value of a newly-created primary key for insertion into another table Note that we did not need to lock our tables in the previous exercise because we used these two features. Use relative updates to change values For example: UPDATE products SET Price = Price * 1.1; Use advanced SQL to combine two queries into one For example: using a nested query (which we have not covered) to lookup a value used to create a row in a new table. Check Yourself True or false: if you write a value to a table and then read it back for verification, you should lock the table during these operations. True or false: if you use SELECT with MAX() to find the last inserted row primary key of table A and then insert that data into table B, you should lock both tables during the operations. ^ top 7.3.6: Using LOCK TABLES and UNLOCK TABLES So we have a series of SQL statements that we need to run using a lock Now how do we create locks and which locks do we use? The general syntax for locking a table is: LOCK TABLES tableName [AS alias] lockType [,tableName [AS alias] lockType] ... Where: tableName: the name of the table to lock alias: an alias name for the table (optional) lockType: either READ or WRITE For the lock type, remember that: READ lock: all users can only read from a table (no updates or inserts allowed) WRITE lock: only the user holding the lock can write to or read from a table (other users cannot read or write) For example: LOCK TABLE products READ, suppliers WRITE; To unlock tables you simply use: UNLOCK TABLES; Rules for Using Locks Remember that you use locks only for transactions susceptible to concurrency. When writing a transaction to a table, you obtain a write lock on that table When your transaction only needs to read, obtain a read lock on that table You must lock all the tables in a single LOCK statement You must release all locks when the database transaction is complete using an UNLOCK statement Example Inserting Data into Two tables with Locking If we had defined our addresses table without using AUTO_INCREMENT we would need to use a lock for the updates as shown below Note that the UNLOCK TABLES command is not strictly needed because the locks will be released at the end of the page However, it is good practice to explicitly unlock the tables // Lock the tables $sql = "LOCK TABLES addresses WRITE, customers WRITE"; mysql_query($sql) or die("Query failed: ".mysql_error()); // Find the maximum addresses ID in use $sql = "SELECT MAX(ID) FROM addresses"; $result = mysql_query($sql) or die("Query failed: ".mysql_error()); $addressId = mysql_result($result, 0, 0); // Set the new value for the primary key $addressId = $addressId + 1; // Insert form values into the first table $sql = " INSERT INTO addresses(ID, Address, City, State, Zip) VALUES ($addressId, '$street', '$city', '$state', '$zip') "; print "<p>sql=$sql</p>\n"; mysql_query($sql) or die("Query failed: ".mysql_error()); $numRows = mysql_affected_rows(); print "<p>Rows affected: $numRows</p>\n"; // Find the maximum customers ID in use $sql = "SELECT MAX(ID) FROM customers"; $result = mysql_query($sql) or die("Query failed: ".mysql_error()); $customerId = mysql_result($result, 0, 0); // Set the new value for the primary key $customerId = $customerId + 1; // Insert form values into the second table $sql = " INSERT INTO customers(ID, LName, FName, AddressID) VALUES ($customerId, '$lname', '$fname', $addressID) "; print "<p>sql=$sql</p>\n"; mysql_query($sql) or die("Query failed: ".mysql_error()); $numRows = mysql_affected_rows(); print "<p>Rows affected: $numRows</p>\n"; $sql = "UNLOCK TABLES"; mysql_query($sql) or die("Query failed: ".mysql_error()); More Information LOCK TABLES and UNLOCK TABLES: from the MySQL manual ^ top 7.3.7: Summary In this section we looked at how to use forms to save data in MySQL First we collected form data into variables like: $street = $_REQUEST["street"]; $city = $_REQUEST["city"]; $state = $_REQUEST["state"]; $zip = $_REQUEST["zip"]; Then we mapped the form values to tables and columns using a SQL INSERT statement like: $sql = " INSERT INTO addresses(Address, City, State, Zip) VALUES ('$street', '$city', '$state', '$zip') "; Note that you can have extra whitespace in PHP strings. This can make formatting your SQL statements easier One of the problems we discussed was how to save data in two tables, where the second table has a foreign key for the first table To solve this problem, you have to find the primary key of the data inserted into the first table The best solution is to use the mysql_insert_id() function However, you must call this function and save the AUTO_INCREMENT value immediately after inserting the data in the first table $addressID = mysql_insert_id(); Otherwise you may retrieve the wrong AUTO_INCREMENT value In addition, we discussed the issues of transactions susceptible to concurrency The solution with MyISAM tables is to use locks The MyISAM table, which is all that most ISPs support, has two types of table locking: READ lock: all users can only read from a table (no updates or inserts allowed) WRITE lock: only the user holding the lock can write to or read from a table (other users cannot read or write) Note that locking can slow or stop databases processing until the lock is released Thus you want to minimize the use of locks in your database queries We discussed when you need locks, when you do not need locks and some techniques to avoid using locks Then we discussed the syntax for creating locks and showed an example using locks Quick Quiz The PHP statement for executing a SQL INSERT query on MySQL is: query_mysql($sql) send_query($sql) query($sql) mysql_query($sql) AnswerD The PHP function you use to get the last AUTO_INCREMENT value inserted into a table is _____________mysql_insert_id(). You do need to verify all user data before saving it in a database. True False AnswerTrue Check Yourself How do we map form values to database tables and columns? (7.3.1) How can you remove leading or trailing spaces around data entered into text boxes? (7.3.1) What are the steps for inserting form data into a single MySQL table? (7.3.2) How can you find the value of an AUTO_INCREMENT column after an INSERT? (7.3.3) What are the steps for inserting data into two tables, where the second table has a foreign key for the first table? (7.3.3) What issues can occur when multiple users are reading and writing to databases? (7.3.4) When are locks required and when are they not required? (7.3.5) ^ top Wrap Up Due Next: A7-Collecting User Input (4/16/12) Quiz 7 and Discussion 7 (4/16/12) When class is over, please shut down your computer if it is on ^ top Home | Blackboard | Syllabus | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: May 13 2012 @16:29:39