Web Scripting [PHP] CIS166AE

1. Web Scripting [PHP]CIS166AE Wednesdays 6:00pm – 9:50pm Rob Loy

2. Agenda • Presentations • SQL Update • Creating SQL Tables • SQL Updates • Mid-term Project

3. Contact UPDATE Use rob.loy@scottsdalecc.edu Do NOT use rob.loy@gmail.com

4. Canvas • Goto https://learn.maricopa.edu/login • Use MEID/Password to log-in

5. Database Infrastructure

6. IMPORTANT NOTE A Database Server can not have a DB with the same name. Since we use ONE DB Server, all our DBs have to have unique names.

7. Form POST v GET • Form METHOD=“POST” sends data from the form to the next page in the HTTP header information and is invisible to the user. Accessed by $_POST[“fieldname”] • Form METHOD=“GET” sends data in the URL string visible in the browser URL bar. Accessed by $_GET[“fieldname”]

8. $_POST v $_GET • POST • GET • http://www.robloy.com/index.php?uname=robloy&pword=1234567

9. PHP / SQL Update Steps • SELECT statement to pull ONE recordSELECT * FROM persons WHERE id = 1; • Use the result from step one to populate the HTML form fields<input name=“first” value=“<?php echo $fname; ?>” /> • After user submits, take value and UPDATE the tableUPDATE person SET fname=‘”$_POST[“first”] WHERE id=1;

10. Sort DB records • SELECT * FROM persons ORDER BY fname ASC; • SELECT * FROM persons ORDER BY fname DESC; • The ORDER BY keyword is used to sort the result-set by a specified column. • The ORDER BY keyword sort the records in ascending order by default. • If you want to sort the records in a descending order, you can use the DESC keyword. http://www.w3schools.com/sql/sql_orderby.asp

11. phpMyAdmin

12. Create a new mySQL Table • Log into the phpmyadmin site • Select a DB from right column • Create a table name and pick number of columns

13. Create mySQL Table Structure • Name each field • Select a Type • Assign a length for VARCHAR or CHAR types • Design default values • Determine if NULL values allowed by using NULL checkbox • Pick one field as ID and select INT type and A_I checkbox

14. mySQL Data Types • Numeric • Float • Integer • String • Char • Varchar • Date http://dev.mysql.com/doc/refman/5.0/en/data-types.html

15. phpmyadmin screen

16. Form Data Validation • Client side: Javascript • Make sure information is filled out BEFORE sending to the server. • Saves unnecessary traffic. • Maintains user entry seamlessly. • Sever side: PHP • Users can disable Javascript. • Content can be created maliciously access/update the DB. • More control closer to the SQL statement.

17. IMPORTANT NOTE The life cycle in PHP is the length of time it takes the server to find the file, process the information, and send the completed static HTML back to the client.

18. Questions?

19. Lab 2 • Create folder “class5” • Copy week4 files into week5 • Update index.php to display all the information. The information should be displayed in an <table> format. • Update update.php to allow age to be updated • Send email to rob.loy@scottsdalecc.edu with URL to input form file before 6pm on October 10.

20. Mid-term Project • Create folder called project1 • Create mySQL table that has at least 5 fields • SELECT all records from the table • Create a web form that has INSERT and UPDATE functionality for the table • Add a two-way sort functionality • At least TWO fields have to be required and display error messages • Send email to rob.loy@scottsdalecc.edu with URL to file before 6pm on October 24.