Unit – II Part III Scripting Essentials PHP and MySQL Form Handling Cookies

1. Unit – II Part III Scripting Essentials PHP and MySQL Form Handling Cookies

2. MySQL Basics A database is a structured collection of records or data stored in a computer system and organized in such a way that it can be quickly searched and information can be rapidly retrieved. The SQL in MySQL stands for Structured Query Language. It is designed to allow simple requests from a database via commands such as SELECT title FROM publications WHERE author = 'Charles Dickens'; A MySQL database contains one or more tables, each of which contains records or rows. Within these rows are various columns or fields that contain the data itself. Table 8-1 shows the contents of an example database of five publications detailing the author, title, type, and year of publication.

3. Summary of Database Terms Database The overall container for a collection of MySQL data Table A subcontainer within a database that stores the actual data Row A single record within a table, which may contain several fields Column The name of a field within a row

4. MySQL Commands

5. Accessing MySQL Using PHP • The process of using MySQL with PHP is as follows: • Connect to MySQL and select the database to use. • Build a query string. • Perform the query. • Retrieve the results and output them to a web page. • Repeat steps 2 to 4 until all desired data has been retrieved. • Disconnect from MySQL. Creating a Login File Most websites developed with PHP contain multiple program files that will require access to MySQL and will thus need the login and password details. Therefore, it’s sensible to create a single file to store these and then include that file wherever it’s needed.

6. Connecting to a MySQL Database • You can include login.php in any PHP files that will need to access the database by using the require_oncestatement. • Using require_once instead of require means that the file will be read in only when it has not previously been included, which prevents wasteful duplicate disk accesses. • This example creates a new object called $conn by calling a new instance of the mysqlimethod, passing all the values retrieved from the login.php file. • Error checking is achieved by referencing the $conn->connect_error property. • The -> operator indicates that the item on the right is a property or method of the object on the left. • In this case, if connect_error has a value, then there was an error, so we call the die function and display that property, which details the connection error.

7. Building and executing a query Sending a query to MySQL from PHP is as simple as issuing it using the query method of a connection object. • Here the variable $query is assigned a string containing the query to be made, and then passed to the query method of the $connobject, which returns a result that we place in the object $result. • If $result is FALSE, there was a problem and the error property of the connection object will contain the details, so the die function is called to display that error. • All the data returned by MySQL is now stored in an easily interrogatable format in the $resultobject.

8. Fetching a result Once you have an object returned in $result, you can use it to extract the data you want, one item at a time, using the fetch_assocmethod of the object.

9. To seek to the correct row each time around the loop, we call the data_seek method of $result before fetching each item of data. Then we call the fetch_assoc method to retrieve the value stored in each cell, and output the result using echo statements.

10. Fetching a row • Only one seek into the object is made in each iteration of the loop, because each row is fetched in its entirety via the • fetch_arraymethod. • This returns a single row of data as an array, which is then • assigned to the array $row.

11. The fetch_arraymethod can return three types of array according to the value passed to it: MYSQLI_NUM Numeric array. Each column appears in the array in the order in which you defined it when you created (or altered) the table. In our case, the zeroth element of the array contains the Author column, element 1 contains the Title, and so on. MYSQLI_ASSOC Associative array. Each key is the name of a column. Because items of data are referenced by column name (rather than index number), use this option where possible in your code to make debugging easier and help other programmers better manage your code. MYSQLI_BOTH Associative and numeric array. Closing a connection Calls to the close methods of the objects $result and $conn in the preceding scripts, as soon as each object is no longer needed

12. A Practical Example

14. The first section of new code starts by using the issetfunction to check whether values for all the fields have been posted to the program. • Upon confirmation, each line within the if statement calls the function get_post, which appears at the end of the program. • This function has one small but critical job: fetching input from the browser.

16. The $_POST Array • A browser sends user input through either a Get request or a Post request. • $_POST is an associative array, depending on whether a form has been set to use the Post or the Get method, either the $_POST or the $_GET associative array will be populated with the form data. • Each field has an element in the array named after that field. • So, if a form contained a field named isbn, the $_POST array contains an element keyed by the word isbn. • The PHP program can read that field by referring to either $_POST['isbn'] or • $_POST["isbn"]. • The get_post function, which passes each item it retrieves through the real_escape_string method of the connection object to strip out any characters that a hacker may have inserted in order to break into or alter your database.

17. Deleting a Record • The program checks whether the variable $_POST['delete'] has a value. If so, the user has clicked the DELETE RECORD button to erase a record. In this case, the value of $isbn will also have been posted. • If $_POST['delete']) is not set (and so there is no record to be deleted), $_POST['author']) and other posted values are checked. If they have all been given values, then $query is set to an INSERT INTO command, followed by the five values to be inserted. • The string is then passed to the query method, which upon completion returns either TRUE or FALSE. If FALSE is returned, the error message held in the error property of the $conn object is displayed, like this: Displaying the Form • The HTML form section simply sets the form’s action to sqltest.php. This means that when the form is submitted, the contents of the form fields will be sent to the file sqltest.php, which is the program itself. • The <pre> and </pre> tags here, which have been used to force a monospaced font and allow all the inputs to line up neatly. • The carriage returns at the end of each line are also output when inside <pre> tags.

18. Querying the Database A query is sent to MySQL asking to see all the records in the classics table, like this: After that, $rows is set to a value representing the number of rows in the table: Using the value in $rows, a for loop is then entered to display the contents of each row. Within each iteration of the loop, the data_seekmethod of the $result object is called to seek to the relevant items of data Then the array $row is populated with a row of results by calling the fetch_array method of $result, passing it the constant value MYSQLI_NUM, which forces the return of a numeric (rather than associative) array, like this: After the display of each record, there is a second form that also posts to sqltest.php (the program itself) but this time contains two hidden fields: delete and isbn. The delete field is set to yes and isbn to the value held in $row[4], which contains the ISBN for the record. Then a Submit button with the name DELETE RECORD is displayed, and the form is closed.

19. Practical MySQL Here, you can use in PHP to access the MySQL database, including tasks such as creating and dropping tables; inserting, updating, and deleting data; and protecting your database and website from malicious users. Creating a Table Let’s assume that you are working for a wildlife park and need to create a database to hold details about all the types of cats it houses. You are told that there are nine families of cats—Lion, Tiger, Jaguar, Leopard, Cougar, Cheetah, Lynx, Caracal, and Domestic. A unique identifier is also needed for each animal, so you also decide to create a column for that called id.

20. Describing a Table It simply issues the query DESCRIBE cats and then outputs an HTML table with four headings—Column, Type, Null, and Key—underneath which all columns within the table are shown. To use it with other tables, simply replace the name cats in the query with that of the new table

21. Dropping a Table Adding Data

22. You may wish to add a couple more items of data by modifying $query as follows The id column is of type AUTO_INCREMENT, and MySQL will decide what value to assign according to the next available number in sequence, so we simply pass a NULL value, which will be ignored. Retrieving Data Now that some data has been entered into the cats table, Example 10-11 shows how you can check that it was correctly inserted.

23. This code simply issues the MySQL query SELECT * FROM cats and then displays all the rows returned. Here you can see that the id column has correctly auto-incremented. Updating Data

24. Deleting Data Using AUTO_INCREMENT

25. Note that the previous id value of 2 is not reused, as this could cause complications in some instances. Using insert IDs When inserting new data in multiple tales this with an auto-increment column, you will need to retain the insert ID returned for storing in the related table. Let’s assume that these cats can be “adopted” by the public as a means of raising funds, and that when a new cat is stored in the cats table, we also want to create a key to tie it to the animal’s adoptive owner. Now the cat is connected to its “owner” through the cat’s unique ID, which was created automatically by AUTO_INCREMENT.

26. Using locks • A completely safe procedure for linking tables through the insert ID is to use locks. • It can slow down response time a bit when there are many people submitting data to the same table, but it can also be worth it. The sequence is as follows: • 1. Lock the first table (e.g., cats). • 2. Insert data into the first table. • 3. Retrieve the unique ID from the first table (the insert_id property). • 4. Unlock the first table. • 5. Insert data into the second table. • You can safely release the lock before inserting data into the second table, because the insert ID has been retrieved and is stored in a program variable.

27. Building Forms • Handling forms is a multipart process. • First a form is created, into which a user can enter the required details. • This data is then sent to the web server, where it is interpreted, often with some error checking. • If the PHP code identifies one or more fields that require reentering, the form may be redisplayed with an error message. • When the code is satisfied with the accuracy of the input, it takes some action that usually involves the database, such as entering details about a purchase. • To build a form, you must have at least the following elements: • An opening <form> and closing </form> tag • A submission type specifying either a Get or Post method • One or more input fields • The destination URL to which the form data is to be submitted

28. Retrieving Submitted Data Example 11-2 expands on the previous program to include data processing. Type it or modify formtest.php by adding in the new lines, save it as formtest2.php, and try the program for yourself.

30. Default Values Suppose you put up a loan repayment calculator widget on a real estate website. It could make sense to enter default values of, say, 25 years and 6 percent interest, so that the user can simply type either the principle sum to borrow or the amount that she can afford to pay each month. By populating the value attribute, you display a default value in the field, which the users can then change if they wish.

31. Input Types HTML forms are very versatile and allow you to submit a wide range of input types, from text boxes and text areas to checkboxes, radio buttons, and more. Text boxes It accepts a wide range of alphanumeric text and other characters in a single-line box. The general format of a text box input is as follows: The size attribute specifies the width of the box (in characters of the current font) as it should appear on the screen, and maxlength specifies the maximum number of characters that a user is allowed to enter into the field. The only required attributes are type, which tells the web browser what type of input to expect, and name, for giving the input a name that will be used to process the field upon receipt of the submitted form. Text areas When you need to accept input of more than a short line of text, use a text area. This is similar to a text box, but, because it allows multiple lines, it has some different attributes.

32. The first thing to notice is that <textarea> has its own tag and is not a subtype of the <input> tag. It therefore requires a closing </textarea> to end input. Instead of a default attribute, if you have default text to display, you must put it before the closing </textarea>, and it will then be displayed and be editable by the user: To control the width and height, use the cols and rows attributes. Both use the character spacing of the current font to determine the size of the area. You can control how the text entered into the box will wrap (and how any such wrapping will be sent to the server) using the wrap attribute. Table 11-1 shows the wrap types available. If you leave out the wrap attribute, soft wrapping is used.

33. Checkboxes When you want to offer a number of different options to a user, from which he can select one or more items, checkboxes are the way to go. If you include the checked attribute, the box is already checked when the browser is displayed. The string you assign to the attribute should be either a double quote or the value "checked", or there should be no value assigned. If you include the checked attribute, the box is already checked when the browser is displayed. The string you assign to the attribute should be either a double quote or the value "checked", or there should be no value assigned.

34. If you wish to offer a newsletter to your readers when submitting a form, you might want to have the checkbox already checked as the default value: If you want to allow groups of items to be selected at one time, assign them all the same name. If only one of the checkboxes is selected, such as the second one, only that item will be submitted (the field named ice would be assigned the value "Chocolate"). But if two or more are selected, only the last value will be submitted, with prior values being ignored.

35. To allow multiple submissions, you have to slightly alter the HTML, as in Example 11-5 (note the addition of the square brackets, [], following the values of ice). Now, when the form is submitted, if any of these items have been checked, an array called ice will be submitted that contains any and all values. In each case, you can extract either the single submitted value, or the array of values, to a variable like this: If ice was defined in the form as an array (like Example 11-5), $ice will be an array, and its number of elements will be the number of values submitted. Table 11-2 shows the seven possible sets of values that could be submitted by this HTML for one, two, or all three selections. In each case, an array of one, two, or three items is created.

36. If $ice is an array, the PHP code to display its contents is quite simple and might look like this: This uses the standard PHP foreach construct to iterate through the array $ice and pass each element’s value into the variable $item, which is then displayed via the echo command. The <br> is just an HTML formatting device to force a new line after each flavor in the display. By default, checkboxes are square.

37. Radio buttons They are used when you want only a single value to be returned from a selection of two or more options. All the buttons in a group must use the same name and, because only a single value is returned, you do not have to pass an array. For example, if your website offers a choice of delivery times for items purchased from your store, you might use HTML like that in Example 11-6 Here, the second option of Noon–4pm has been selected by default. This default choice ensures that at least one delivery time will be chosen by the user. By default, radio buttons are round.

38. <select> The <select> tag lets you create a drop-down list of options, offering either single or multiple selections. The attribute size is the number of lines to display. Clicking on the display causes a list to drop down, showing all the options. If you use the multiple attribute, a user can select multiple options from the list by pressing the Ctrl key when clicking. If you want to have a different default option offered first (such as Beans), use the <selected> tag, like this:

39. Labels With it, you can surround a form element, making it selectable by clicking any visible part contained between the opening and closing <label> tags. For example, going back to the example of choosing a delivery time, you could allow the user to click the radio button itself and the associated text, like this When the mouse passes over, it will change to an arrow instead of a text cursor, indicating that the whole item is clickable. The submit button You can also replace the standard text button with a graphic image of your choice To match the type of form being submitted, you can change the text of the submit button to anything you like by using the value attribute, like this:

40. Using Cookies in PHP A cookie is an item of data that a web server saves to your computer’s hard disk via a web browser. It can contain almost any alphanumeric information (as long as it’s under 4 KB) and can be retrieved from your computer and returned to the server. Common uses include session tracking, maintaining data across multiple visits, holding shopping cart contents, storing login details, and more. Because of their privacy implications, cookies can be read only from the issuing domain. This prevents other websites from gaining access to details for which they are not authorized. Because of the way the Internet works, multiple elements on a web page can be embedded from multiple domains, each of which can issue its own cookies. When this happens, they are referred to as third-party cookies. Most commonly, these are created by advertising companies in order to track users across multiple websites. Because of this, most browsers allow users to turn cookies off either for the current server’s domain, third-party servers, or both. Cookies are exchanged during the transfer of headers, before the actual HTML of a web page is sent, and it is impossible to send a cookie once any HTML has been transferred. Figure 12-1 illustrates a typical request and response dialog between a web browser and web server passing cookies.

42. Setting a Cookie As long as no HTML has yet been transferred, you can call the setcookie function as So, to create a cookie with the name username and the value Hannah that is accessible across the entire web server on the current domain, and will be removed from the browser’s cache in seven days, use the following:

44. Accessing a Cookie Reading the value of a cookie is as simple as accessing the $_COOKIE system array. For example, if you wish to see whether the current browser has the cookie called user name already stored and, if so, to read its value, use the following: Note that you can read a cookie back only after it has been sent to a web browser. This means that when you issue a cookie, you cannot read it in again until the browser reloads the page (or another with access to the cookie) from your website and passes the cookie back to the server in the process. Destroying a Cookie To delete a cookie, you must issue it again and set a date in the past. It is important for all parameters in your new setcookie call except the timestamp to be identical to the parameters when the cookie was first issued; otherwise, the deletion will fail. Therefore, to delete the cookie created earlier, you would use the following: As long as the time given is in the past, the cookie should be deleted.