Dr. Tom Hicks Computer Science Department

1. HTML Forms & PHP & MySQL Database Database Systems CSCI-3343 Dr. Tom Hicks Computer Science Department

2. Import DatabaseUniversity1with MySQL Workbench

3. It Should Have 3 Tables

4. Create Folders

5. Create Folders Faculty-Add & Faculty-Display Download Faculty-Display.php &Put It In Folder Faculty-Display

6. UpdateMain-Menu.php

7. Add/Link The Following To Main-Menu.php

8. Create UniversityUser

9. Create User UniversityUser With These Privileges ALTER USER 'UniversityUser'@'%' IDENTIFIED WITH mysql_native_password BY 'trinity'

10. Test Faculty-Display Uses UniversityUser!

11. Add Code To PageFaculty-Add.php

12. Add The Following Documentation BlockTo The Top  your Name & Date <!------------------------------------------------------------------------------ -------------------------------------------------------------------------------- -- Faculty-Add.php -- -- -- -- Purpose: Prompt the user to fill all of the data fields necessary to -- -- add a new faculty member to the Faculty Table of the University1 -- -- MySQL database. -- -- -- -- When the submit button is pushed, control shall transfer to -- -- page Faculty-Add-Confirmation.php; it will be this page which -- -- physically adds the record to the database. -- -- -- -- This will be a first attempt at adding records; no error -- -- processing will be done. -- -- -- -- Written By: Dr. Tom Hicks Date: xx/xx/xx -- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------->

13. Modify The Following Page Properties

14. Create PageFaculty-Add-Confirmation.php

15. Save A Copy Of Faculty-Add.php As Faculty-Add-Confirmation.php Alter The Banner

16. Add The Following Documentation BlockTo The Top  your Name & Date <!------------------------------------------------------------------------------ -------------------------------------------------------------------------------- -- Faculty-Add-Confirmation.php -- -- -- -- Purpose: Create a query from the data transferred from page -- -- Faculty-Add-Confirmation.php; use the query to add a new record -- -- to table Faculty of the University1 database. -- -- -- -- This will be a first attempt at adding records; no error -- -- processing will be done. -- -- -- -- Written By: Dr. Tom Hicks Date: xx/xx/xx -- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------->

17. Add A Link To Display Faculty!

18. FORMElement

19. FORM • HTML forms are used to pass data to a server. • An HTML form can contain input elements like text fields, checkboxes, radio-buttons, submit buttons and more. A form can also contain select lists, textarea, fieldset, legend, and label elements. • The form element begins with <FORM> and ends with </FORM> • Add the form to the body of the Faculty-Add.php <FORM> </FORM> .

20. GUI’s Helpful

21. GUI’s Helpful

22. GUI’s Helpful

23. FORM  METHOD = GET • METHOD = GET • Cacheable  Does not automatically re-request info • Should be used only if form is Indepotent • Browser will reprocess with no warning • Would Re-Bill a credit card with no browser warning • May require POST for Indepotent if URL is long • For getting/retrieving data only! <FORM METHOD = "GET" > </FORM> .

24. FORM  METHOD = POST • METHOD = POST • Browser will generally reprocess with a warning • Don’t want credit card rebilled twice • I always use with database queries  I always want the most recent data • Many folks simply use POST all of the time <FORM METHOD = "POST" > </FORM> .

25. FORM  GET  Illustration • METHOD = GET • Get can also expose more of the sensitive information because it is appended to the URL. (See Below!) • More Of A Security Risk Than Post ! <FORM METHOD = “GET" > </FORM> . I USE POST!

26. FORM  ACTION = AddFaculty-Confirmation.php • ACTION = Form Process Page URL  Relative or Absolute • Suppose the data collection page Faculty-Add.php • The contents of the form will often be sent to another page for processing; i.e. adding this faculty member record to the database. • The data collection page & the process page can be one and the same, but this is often a bit more complex and limiting. • I would call the page to add the confirmation page AddFaculty-Confirmation.php • It is not the purpose of this presentation to add the record to the database; we are only examining the HTML transfer of information. <FORM METHOD = "POST"ACTION = "Faculty-Add-Confirmation.php"> </FORM> .

27. FormSubmission

28. FORM  ID = form1  NAME = form1 • NAME = form1 ID = form1 • HTML offers the ability to logically navigate to named regions on the page. • You might choose to automatically move the cursor into one of the form textboxes when a form is loaded – you will need the NAME. <FORM METHOD = "POST"ACTION = "Faculty-Add-Confirmation.php"ID = "form1" NAME = "form1"> </FORM> .

29. Page Still Loads

30. INPUT TYPE = SUBMIT • NAME = form1 ID = form1 • HTML offers the ability to logically navigate to named regions on the page. • You might choose to automatically move the cursor into one of the form textboxes when a form is loaded – you will need the NAME. <FORM METHOD = "POST" ACTION = "Login-Confirmation.php " ID = "form1" NAME = "form1"> <center><INPUT TYPE ="submit" VALUE ="Add Faculty Member Now!"></center> </FORM> .

31. Push The Submit Button

32. Form Submit Can Navigate Control To Other Web Pages • The confirmation page may be a relative address as seen above. • The confirmation page may be absolute as shown to google below. <FORM METHOD="POST" ACTION="http://google.com"> <INPUT TYPE ="submit" VALUE = "Go To Google Now"> </form>

33. Text InputElement& PHP Form Processing

34. INPUT TYPE = Text • The INPUT element is used for collecting data entered by keyboard. • The data may be alpha or numeric • The data must be inside <Form> and </Form> • Add the code below to your form on Faculty-Add.php First <INPUT NAME = "First" TYPE = "text" SIZE = "15"><P> Contents Of First Are SentTo Confirmation On Submit!

35. Recover Data Passed To Confirmation Page • Data processed in the confirmation can be recovered from &_POST • First < INPUT NAME = "First" TYPE = "text" SIZE = "15"><P> • Add the following block of code to the confirmation page. <HR COLOR=Navy SIZE=5 NOSHADE /> <?PHP $First = $_POST['First']; print "First = " . $First . "<BR>"; ?>

36. Recover Data Passed To Confirmation Page

37. Text InputElement& PHP Form Processing

38. INPUT TYPE = Text • The INPUT element is used for collecting data entered by keyboard. • The data may be alpha or numeric • The data must be inside <Form> and </Form> • Add the code below to your form on Faculty-Add.php Last <INPUT NAME = "Last" TYPE = "text" SIZE = "20"><P> Contents Of First & Last Are SentTo Confirmation On Submit!

39. Recover Data Passed To Confirmation Page • Data processed in the confirmation can be recovered from &_POST • First < INPUT NAME = "First" TYPE = "text" SIZE = "15"><P> • Add the following block of code to the confirmation page. <HR COLOR=Navy SIZE=5 NOSHADE /> <?PHP $First = $_POST['First']; $Last = $_POST['Last']; print "First = " . $First . "<BR>"; print "Last = " . $Last . "<BR>"; ?>

40. Recover Data Passed To Confirmation Page

41. Consider Building Your Insertion Query As You Develop The Page

42. Add Connection - Confirmation Page - 1 <?PHP $testing = true; /*============================================================= === Connect To MySQL Database === =============================================================*/ $server ="localhost"; $username="UniversityUser"; $password="trinity"; //use your password $database="university1"; $con=mysqli_connect($server, $username, $password, $database);

43. Add Connection - Confirmation Page - 2 /*============================================================= === Gather The Data Transferred With POST === =============================================================*/ $First = $_POST['First']; $Last = $_POST['Last']; /*============================================================= === Build The Query === =============================================================*/ $Query = "INSERT INTO Faculty " . "(First, Last) " . "VALUES " . "(" . $First . ", " . $Last . "); " ;

44. Add Connection - Confirmation Page - 3 /*============================================================= === Testing === =============================================================*/ if ($testing) { print "First = " . $First . "<BR>"; print "Last = " . $Last . "<P>"; print "Query = <P>" . $Query . "<P>"; } ?>

45. This Will Be A Complex Query • Build It In Parts  Test Things As You Go! Copy Query To Clipboard

46. Test Query • You may notice errors simply by visual examination  IF SO FIX If it appears good, test it. What Is The Error?

47. Correct The Query /*============================================================= === Build The Query === =============================================================*/ $Query = "INSERT INTO Faculty " . "(First, Last) " . "VALUES " . "('" . $First . "', '" . $Last . "'); " ;

48. Re-Test The Query

49. The Web Page Will Provide No Meaningful Database Insertion Error MessagesPasting The Query Into The Command Line, MySQL Workbench, Navicat, etc. Will Often Provide Meaningful Error Messages!

50. Checkbox InputElement& PHP Form Processing