Internet Information Systems

1. Internet Information Systems Writing to Databases and Amending Data

2. Objectives • Review • Inserting data • Cleaning data • Redirects/Receipts • Server-side validation • Amending and deleting data • Issues in writing data

3. Review • Create tables & display data • DEMO • phpMyAdmin, filmscript • Viewfilm.php • Querying data -DEMO examples • Demo cat_products.php • As well as reading data, also need to Insert, Amend and Delete • Uses - Shopping carts, personalisation,

4. Data Insertion – Phases • Client: Data collection • Web Form + Client-side validation • Server: Check Data Submitted & Clean • Check data arrived • Check for dodgy characters. • Data Validation • Data is in correct format for entry into database. • Insert/Amend the data

5. Data Submitted • Demo AddAnimal.php <?php $country = $_GET['country']; $animal = $_GET['animal']; // If no user input, then present Form if (empty($animal) || empty($country)) { //Error – go back to form} • Could use empty function if (empty($animal) || empty($country)) • Demo Create User • isset function to see if form is submitted

6. Cleaning Data • Avoid bad characters = ‘ “” \ nulls • Magic quotes - all escaped with a backslash automatically • Set on server • mysql_real_escape_string($somestring); • Clean white space at beginning or end. • Trim $newuser = trim($newuser);

7. Reloading Data Problem • http is ‘Stateless’ => ‘reload’ problem with inserts/updates . • Submit details – then Refresh • Variables & values are resubmitted • Can avoid with redirection -> receipt? • http Location:header • CreateUserWithReceipt if (record added) header("Location: userReceipt.php?status=T“); else header("Location: userReceipt.php? status=F“)

8. Server-Side Validation • Must ensure data is in correct format for database – validation by: • Database or Server-Side scripts • Database • Could php mysql_error() function to trap errors returned from DB • Server-Side Scripts • Submit form – then check fields • Can use regular expressions int ereg ( string pattern, string string [, array &regs] )

9. Regular Expressions • PHP Functions ereg() and eregi() int ereg ( string pattern, string string [, array &regs] ) $String = “Course Technology” ereg(“course technology”, $String) //returns false ereg(“Course Technology”, $String) //returns false eregi(“course technology”, $String) //returns false • You can see these functions would be useful in searches ^ means start of a string $ means the end of a string [0-9] means there must be a digit from 0 – 9 {4} means there must be 4 in quantity • Demo – CustomerDetailsError.php ereg("^([0-9]{2})/([0-9]{2})/([0-9]{4})$", $dob, $parts))

10. Database Updates and Deletes • Update - usually 3 stages • Using key value - matching data is read from the database • The data is presented for modification • The data is updated using the key value from the first step. • Demo - CustUpdateInsert $query = "UPDATE customer SET surname = '$surname', ……

11. Example - Delete • Identify row or rows to be deleted • Remove data with SQL DELETE • Demo DeleteAnimal – getting ID $query = "DELETE FROM symbols WHERE id = ".$_GET['id']; mysql_affected_rows() • Demo Cust_delete

12. Writing To Databases - Issues • Multiple users – simultaneous insertion, deletion and updating • Unrepeatable reads (stock, planes) • Transactions – viable and complete • Concurrency – same row being altered • Locking mechanisms provided by mySQL

13. Summary • Reviewed • Create tables in MySQL • View and Query data • Collect, clean, validate data before inserting or amending • Validation (searching) using regular expressions • Avoid ‘Reload’ problem with redirect • Updates and Deletes