Transcript WritingToDatabases

Internet Information Systems
Writing to Databases and
Amending Data
Objectives







Review
Inserting data
Cleaning data
Redirects/Receipts
Server-side validation
Amending and deleting data
Issues in writing data
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,
Data Insertion – Phases
Client: Data collection


Server: Check Data Submitted & Clean



Check data arrived
Check for dodgy characters.
Data Validation



Web Form + Client-side validation
Data is in correct format for entry into database.
Insert/Amend the data
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
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);

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“)
Server-Side Validation

Must ensure data is in correct format for
database – validation by:


Database


Database or Server-Side scripts
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] )
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))
Database Updates and Deletes

Update - usually 3 stages
1.
2.
3.

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', ……
Example - Delete
1.
2.

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
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
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