Transcript (referential integrity, SQL). - Purchase College Faculty Web Server

Creating Databases for Web
applications
Making a table of table information.
Reprise on database design. SQL.
Classwork/Homework: Projects!
Postings.
<?php
require("opendbo.php");
$query="show tables";
$rs=mysqli_query($link, $query);
?>
<table border=1> <tr> <th> Table names </th> </tr>
<?php
$i = 0;
while ($row=mysqli_fetch_array($rs)){
print("<tr> <td>");
$tablenames[$i] = $row[0]; $i++;
print($row[0]);
print("</td></tr>");
}
print("</table>");
for ($j=0;$j<$i;$j++) {
$query = "describe ".$tablenames[$j];
print ("<p><h2>" . $tablenames[$j]. " table </h2><table border=1>\n ");
print ("<th> Field </th> <th> Type </th> <th> Null </th> <th> Key </th> \n
");
$rt=mysqli_query($link,$query);
while ($fi=mysqli_fetch_array($rt)) {
print ("<tr> <td>". $fi['Field'] . "</td>\n ");
print ("<td>".$fi['Type'] . "</td>\n ");
print ("<td>".$fi['Null'] . "&nbsp;</td>\n ");
print ("<td>".$fi['Key'] . "&nbsp;</td>\n ");
print ("</tr>");
}
print ("</table><p>"); }
?>
my query on table info
http://socialsoftware.purchase.edu/jeanine.m
eyer/showtableinfo.php
Challenge
• What if you have a large set of queries for
the user
– one of my earliest assignments at a hospital:
the abominable abdominal report
–?
Table of queries
• If you have a large set of fixed SQL
queries, you may make a new table:
id
description
text
1
final diagnosis when
presenting signs of
appendicitis
Select final.diagnosis from
final, initial where initial.temp
> 100 AND initial.pain = 'left'
AND final.caseno =
initial.caseno
2
initial potential ulcer
cases
Select * from initial where
initial.pain = 'sharp' AND
initial.temp < 100
….
Present to user
Pick selection:
Don't show
the user the
messy SQL
description
final diagnosis when
presenting signs of
appendicitis
initial potential ulcer
cases
Produce responses
• Make the query the SQL corresponding to
the user's choice.
• Display recordset in a table
– Now, need generalized code that creates
headings for tables and extracts names of
fields 'on the fly' based on information in
recordset.
• php:
– mysqli_fetch_field
– mysqli_fetch_array
<table>
<?php
require("opendbo.php");
$query="Select * from customers";
$result=mysqli_query($link, $query);
$fieldnames= Array();
first for loop
print ("<table border=1><tr>");
$nf = mysqli_num_fields($result);
to set up
for ($i=0; $i<$nf;$i++) {
headers
$fieldobj= mysqli_fetch_field($result);
$fieldnames[$i]=$fieldobj->name;
print ("<th>".$fieldnames[$i]."</th>"); }
Second for
print ("</tr>\n");
loop, in while
while ($row=mysqli_fetch_array($result)) {
loop, to extract
print ("<tr>");
field data.
for ($i=0; $i<$nf; $i++) {
print ("<td>".$row[$fieldnames[$i]]."</td>");
}
print("</tr>"); }
mysqli_close($link); ?>
</table>
Note
• http://socialsoftware.purchase.edu/jeanine.
meyer/showCurrentCustomers.php
• If you do [ever] show field names to your
customers/clients/players/…,
– you need to make them longer/better/etc.
– Make sure database doesn't have blanks, etc.
MySQL Rights
• Possible to limit rights to specific users.
– specific table or whole database
– SELECT, INSERT, DELETE, UPDATE, DROP,
CREATE, ALTER
• NOTE: I don't know if your rights include right to
use GRANT!
• Limiting rights is to prevent unintentional errors
as much as / more than not trusting certain
people.
• Your php code is main line of protection.
SQL question
Generate list of customers who had orders made
in the summer of 2013.
• Tables needed:
– customers, orders
– JOIN … ON condition the natural one: when id fields
match
• WHERE condition?
– order date 'in' summer. Use BETWEEN
• Any grouping (aka aggregation)?
– No GROUP BY. Instead, use DISTINCT
SQL question
• SELECT DISTINCT cname FROM
customers as c JOIN orders as o ON c.cid
= o.cid WHERE o.date BETWEEN ‘201306-01’ AND ‘2013-08-31’
SQL question
• What products were ordered by customers
during Spring semester?
• Strategy:
– decide on tables and ON conditions
– determine dates and set up WHERE condition
using BETWEEN
– use DISTINCT
Left Join, etc.
• Searching for what is there and what isn't
there.
– Left Join
– NOT EXISTS. You can create (on the fly) a
new table and check if it has any rows.
– See
http://explainextended.com/2009/09/18/not-invs-not-exists-vs-left-join-is-null-mysql/
php: checking for value
• Using isset($_GET['guess']) is better than
using
$guess = @$_GET['guess'];
if ($guess != null) { }
php: strpos
• Check if string is present in another string
$n = strpos($answer,"SUNY");
if ($n>-1) {
}
• Alternative to regular expression
php: explode
• Breaks up string into an array
if $listofstuff is "orange,apple,peach"
$list = explode(",",$listofstuff);
produces $list = ["orange","apple","peach"];
Database administrator
• technical: working with software, including
maintaining security, performance.
Keeping aware of changes in products,
new products.
• administrative: working with technical staff
AND other staff
• specific business: knowing and keeping
up-to-date with knowledge of this
business.
Internationalization & Localization
How to make the php MySQL application work in
different places
• character sets (includes symbols, encodings for
symbols, collations (ordering rules)
• error messages (MySQL). Presumably your php
code does error handling, also.
• time zone
• way to express dates
See SET and SHOW commands
Reprise: Data base design
• Not obvious!
• Need to determine the information and the
location of information in one or more
tables.
• For each piece of information, what is the
data type (representation)
– different types of numbers
– different types of character strings
– MAY choose to incorporate media
Reprise: Database table design
Consider orders by customer
• what information goes with the order and
• what information goes with the customer
• You do not want information associated with a
customer to be repeated in every order!
– wasteful of space
– if (when) information changes, want to change in just
one place
Maintenance of database
• Change fields in records
• Delete records
• Need to maintain referential integrity
– Meaning that if a field in a table represents a
foreign key (a reference to a record in another
or this table), the referenced record exists!
Referential integrity
• Can handle in middleware (php) or MySQL
or both.
• MySQL
– CREATE statements specify field as
FOREIGN KEY. Add clause saying what to do
upon deletion
• ON UPDATE CASCADE
• ON UPDATE SET NULL
• ON UPDATE RESTRICT -- if there is a reference,
an error will be generated. This is the default
Database Transaction
• refers to a sequence of operations on the
database that are to be treated as a unit. Either
they all go through or none go through.
• php MySQL has commands to implement
transactions.
– Turn off the 'auto-commit feature'
– check each query
• if any problem, use mysqli_rollback()
– when all okay, use mysqli_commit()
Project presentation
• Prepare and printout 1 pager: project name,
members of team, abstract (1 paragraph
summary), screen shot
• Show (corrected/improved/finalized) design
diagrams
– DFD and ERD
• Demonstrate
• Show storyboard
– how project is implemented in html and php scripts
• Show and explain tricky parts of code.
Like
• SQL feature: stripped down regular
expression
– Wild card: % stands for 0, 1 or more letters
• SELECT * FROM states WHERE sname
LIKE ‘North%’
Two forms of INSERT
• INSERT INTO customers VALUE (v1,
v2,…vn)
Where order of the values v’s is the order of the
fields when the table was created
• INSERT INTO customers (fname, lname,
zipcode) VALUES
(‘Groucho’,’Marks’,10000)
QUIZ
• Take W3Schools SQL quiz
• http://www.w3schools.com/quiztest/quiztes
t.asp?qtest=SQL
Or put sql quiz into Google.
Classwork/Homework
• Keep working on projects
• Postings (and respond to postings) on
databases and related technologies in the
news.