DIG 3134 – Lecture 14 MySQL and PHP Play Together Michael Moshell University of Central Florida

1. Media Software Design DIG 3134 – Lecture 14 MySQL and PHP Play Together Michael Moshell University of Central Florida

2. Two Concepts b4 We Begin 1. An INDEX is a way of speeding up a database If you just make a DB table with 4 "plain vanilla" fields, searches are order-N (that means as search examines EVERY record.) If you specify a field (or combination of fields) as KEY or PRIMARY INDEX, the system builds a special resource ("index") to speed up search to order-log-N. (i. e. 1000 records -> 10 steps) 2000 records -> 11 steps)

3. Two Concepts b4 We Begin 2. A QUERY with USER INPUT is Very Dangerous because of SQL INJECTION ATTACK A hostile user can type some junk that makes your query into TWO queries, the second of which destroys everything ... or reveals it Solution: Run all user input through a filter like this: $matchlastX=$_POST['matchlast']; $matchlast=$mysqli->real_escape_string($matchlastX)

4. Our objective today: Get Familiar with php and database code I give you 'address.php' which is a super-simple (add-only) Address Book 1) We analyze and discuss its code 2) You use phpMyAdmin to build the table 3) You extend the code by adding 'city' and 'state' fields. 4) You extend the code: add a title row to the HTML table 5) You extend the code by adding a 'delete record' functionality 6) For the Elite Premium-Plan People: radio button version

5. New Features in 'address.php' 1. An Auto-Increment Field in the Table 'addressbook' 2. An INSERT query in the function 'addperson()' Autoincrement: whenever a new record is INSERTed, this field takes a value that is larger (by 1) than its value in the previous new record. So it grows like 1, 2, 3, 4, 5. If you delete records (so list is now 1,2,4) and then add a record, its autoincrement number will be 6.

6. New Features in 'address.php' Elite Note: If you want to force a value into the auto-increment system (for the next INSERT), use this query: ALTER TABLE addressbook AUTO_INCREMENT = 3 Of course, this would set the next value to 3.

7. Use phpMyAdmin to build your table 'addressbook' Four columns: idnumber – int – index='primary' and check 'A_I' (which means auto-increment) lastname – varchar (30) firstname – varchar (30) address – varchar (30)

8. Make vanilla 'address.php' work Download 'address.txt' from the DIG3134 website, save it as 'address.php' in your WAMP or MAMP system's docroot (WAMP calls that place 'www') (MAMP calls that place 'htdocs') Modify its login info so that it works with your 'addressbook' table.

9. Save as 'address2.php' 3) Extend the code by adding 'city' and 'state' fields. How to do this? Give it a try, first. If you get stuck, the next pages are a step-by-step guide.

10. Modify 'address2.php': step by step 3) Extend the code by adding 'city' and 'state' fields. 3a) Add two new input fields to 'drawinput' <input type='text' name='city'>City<br /> <input type='text' name='state'>State<br />

11. Modify 'address2.php': step by step 3) Extend the code by adding 'city' and 'state' fields. 3b) Add two new fields to table 'addressbook'

12. Modify 'address2.php': step by step 3) Extend the code by adding 'city' and 'state' fields. 3c) Add two new fields to function 'addperson' $cityX=$_POST['city']; $stateX=$_POST['state']; -- use $mysqli->real_escape_string to clean these! -- $query = "INSERT into addressbook VALUES (null,'$lastname','$firstname',$address,$city,$state)"; And now it ought to work!

13. Now you're on your own (but you can HELP one another!) 4) You extend the code: add a title row to the HTML table

14. In the next lecture we will ... 5) extend the code by adding a 'delete record' functionality Add an input field for a 'deletenumber' value Add a submit button for action "Delete Person" Construct a function named 'deleteperson', similar to the 'addperson' function but with a different query.