Creating databases for Web Applications
Download
Report
Transcript Creating databases for Web Applications
Creating databases for Web
Applications
Continue with php basics: emailing
Introduction to databases.
Access demonstration. MySQL
Homework: Practice with Access or Open
Office Base. Use phpMyAdmin. Make
posting with [unique] source on MySql.
Did you?
• Get anything working?
• Will show code now
Reprise: PHP
• php files are interpreted by/on/at the server.
– php code within html
• One or more of the following happens:
– php interface to operating system is used to do
things such as interact with databases OR files
OR email
– HTML is produced (and sent to the client)
– cookie is set and/or accessed
Structure
CLIENT
SERVER
Browser
(Firefox):
Requests
URL
SERVER
DBMS
(MySQL)
PHP
interpreter
Build
HTML
page
php
• echo: prints material to the html document
to be returned/sent to the client
• Can use print
• Extra credit opportunity: is there a
difference?
Look at Flash
• Download source of Catching Bo
• http://newmedia.purchase.edu/~Jeanine/as3
0/chasebothedogemaila.fla
Actions for Catching Bo
• Set up call when player clicks button
sendbtn.addEventListener(MouseEvent
.CLICK,sendemail);
• Start of sendemail function. Doesn't send email if
player hasn't caught Bo.
function sendemail(ev:Event) {
if (caught=="") {
toaddress.text = "You
didn't catch Bo!";
}
else {
var request:URLRequest = new
URLRequest("sendemail.php");
var variables:URLVariables = new
URLVariables();
var gameURL:String =
"http://newmedia.purchase.edu/~Jeanine/
as30/chasebothedogemailb.html"
variables.body="I caught Bo on
"+caught+". Visit "+gameURL;
variables.to=toaddress.text;
variables.subject="news";
variables.from = fromaddress.text;
//trace("variables.body is
"+variables.body);
request.data = variables;
request.method=URLRequestMethod.POST;
sendToURL(request);
} }
sendemail.php (used for Bo)
<?php
$to = $_POST['to'];
$subject = $_POST['subject'];
$body = $_POST['body'];
$headers = "From: " . $_POST['from'];
if (mail($to, $subject, $body,$headers)) {
echo("Your message was sent");
} else {
echo("There was a problem.");
}
?>
html to php
Sets of html (set up) and php files
• sendemailfromhtmlp.html
– calls sendemailp.php
• sendemailfromhtmlg.html
– calls sendemailg.php
• sendemailfromhtmlgfancy.html
– calls sendemailgfancy.php
Note
• These all look the same when viewing the
displayed html
• The source shows that each calls a different
php file!
sendemailfromhtmlg.html
<html>
<head>
<title>Email
</title>
</head>
<body>
<form action="sendemailg.php">
<p>Your email (for reply)
<input type="text" name="from" /> </p> <p>
To email <input type="text" name="to" /> /p><p>
Subject <input type="text" name="subject" />
</p> <p>
Body of message (you may change it) <br/>
<TEXTAREA NAME="body" COLS=40 ROWS=6>
This is email sent from my own html/php application
</TEXTAREA>
</p>
<input type="submit" value="SEND" />
</form> </body> </html>
textarea
• Similar to input
• Used for multi-line
• Can put in default content
– Where should instructions go????
sendemailg.php
<?php
$to = $_GET['to'];
$subject = $_GET['subject'];
$body = $_GET['body'];
$headers = "From: " . $_GET['from'];
if (mail($to, $subject, $body,$headers)) {
echo("Your message was sent");
} else {
echo("There was a problem.");
}
?>
Question
• What is different for the post version?
Extra credit possibility
• Figure out how to get an error
• Note: the use of mail is an asynchronous
operation: initiated from php (sent to
operating system on the server)
Mechanics in php script
• Need to distinguish html versus php
• Common task will be to generate a string
(aka character string) made up of html
you know ahead of time and html
generated from variables.
phpinfo script
<html>
<head>
<title>PHP info test</title>
</head>
<body>
<?
echo phpinfo();
?>
</body>
</html>
Let's do it: phpinfo
• http://newmedia.purchase.edu/~Jeanine/db/
phpinfotest.php
• Is this more than we want to know? Is this
more than we should know????
Variables
• Concept in all computing
• Variable 'is' a name that holds a value. Can be
used in place of a literal value. The value can
vary over use
• PHP: variable names begin with $, no spaces,
avoid punctuation except for _
• Rule: long enough for you to remember what it
is used for and short enough to type.
php example
<html><head><title> Test </title></head>
<body>
<h1> Welcome to the store </h1>
<?php
….
Print("<h2>New Products</h2>");
Print( "<br>"+$newproduct1name);
?>
</body></html>
Variable set &
used
php variables within strings
• For all php variables, you can put the variable name in
a string:
print("The name input was: $fname");
– php figures out that $fname is a variable and gets its
value.
• NOTE: out of habit in using other programming
languages, sometimes I don't do that:
print ("The name input was :" .
$fname);
• NOTE: the string concatenation operator is .
• Caution: SQL often requires a single quotation mark
Form data in php
• Built-in functions
Name in form
$_GET[' ']
$_POST[' ']
• If you want to NOT distinguish (considered
less secure)
$_REQUEST[' ']
also includes $_COOKIE
So…
• Try writing [yet another] html file calling a
php file that uses $_Request
Checking if a form variable has been
set
• Common situation to check if this is first
time
$saywhich=@$_GET['saywhich'];
Prevents error if no
value exists
random
• rand (a, b) returns a pseudo-random choice
from a to b, inclusive
$choice=rand(0, sizeOf($states)-1);
• The arrays in php (like JavaScript and
ActionScript) start indexing at zero.
Overview
• Useful to think in terms of 3 language
domains (my terminology):
– client side browser (interpreter of html)
– server side interpreter of asp/JavaScript or php
• (There is another level here for the operating
system, but this is what the asp objects and the php
built-in functions & variables provide.)
– database interpreter of SQL (by Access or
MySQL)
Warnings
Applicable to php and other languages:
• To output quotation marks in the HTML, you may
use single quotes or 'escape' double quotation
marks:
print ("<a href=\"$filename\">");
• Preview: SQL statements require quotation marks
around values in certain cases.
• SQL statements use a single equals sign (=) for
checking for equality. JavaScript and php use ==
in IF statements.
Claim
• All database management systems share similar
structure.
– Tables Records Fields
– Records have primary keys
– Records may have fields that hold foreign keys, that is,
pointers to records in other tables or in that table.
• They differ on efficiencies noticeable in large[r],
production systems
– How many simultaneous queries
– Security issues
Example (NOT ACCURATE)
• Table of courses:
• Each record represents one course.
Course identifier: MAT3530
Cross-listing: NME3520
Name: Creating Databases for Web Applications
• ASSUMING a course has at most one prerequisite, each record has field that
Pre-req: MAT1420
NOTE: the pre-reqs are more complicated, since there are
many possibilities. This would require a new table.
Creating database
• Create tables
• [Create / specify relations.]
• If database to be used on stand-alone
computer, create Forms, Queries, Views.
Instead, we will create programs (html and
php scripts) to do this.
Table
• Define what is a record in the table: what are the
fields?
– What is the information associated with this one thing?
• What is the data type of each field?
– If the databases will contain many records, may be
important to be careful as to size
• Is there an intrinsic primary key (unique for the
record in the table) or should system supply one?
• Fields can have at most one value (can be null)
– Multiple values means you need another table
Caution
• Defining (designing) a database is not
trivial.
• May require revision.
• Interview process with
clients/customers/systems owners can take
time.
What should we do?
•
•
•
•
•
•
Budget (past and future expenses, dates)
Favorite web sites
Music collection (with features)
Courses taken (grades) / will take
Candidates
?
Access
• Part of Microsoft Office suite
Open Office Base
• Open source product: part of Open Office
• Very similar to Access
Create table
• Create table Use Design view
– To specify primary key. Give field a name
• Access: click in left column to specify as primary key.
Autonumber is a datatype
• Base: right click to specify as primary key. Select Integer.
Select Auto in property panel.
– Wizards are useful for common business applications
• Create form Use Wizard
– Use form to populate (that is, put data into table)
• Create report
Alternative: spreadsheet to db
Extra credit? Recovery Act
1. http://www.hhs.gov/recovery/reports/index.html
2. Click on weekly report: Excel file
3. In Excel: Massage file (remove first few rows) (
Protected cells may cause problems.)
4. Open Access. File/Open. Browse to the xls file.
Follow directions.
5. Create query: get data on NY, NJ, CT.
Spreadsheet vs Database
• Scale: DBMS can handle large amounts of information
• Efficiency: DBMS can handle different datatypes
• DBMS have facilities for MANAGING access by
multiple users
• DBMS supports multiple tables and queries across the
multiple tables.
• MySQL (and other DBMS) have easier (?)
connections to php and other programming languages
– Extra credit opportunity: do posting on php and
Excel or php and Open Office or VB.net & xls, etc.
Give explanation / examples, NOT JUST THE
LINK!
Trivia game
• Questions table
–
–
–
–
–
Question id
Question text
Question answer
Question category
Question score
• Player table
• History table
Trivia database: players
• Player id
• Player name
• Player password
Trivia database: history
•
•
•
•
•
Event id
Player id
Question id
Result (right or wrong)
Time stamp
Entity Relationship Diagram
Questions
Ques id
Text
Answer
Category
score
Players
Player id
Player name
Password
History
historyID
Ques id
Player id
Result
Time stamp
Access
• Create relationships using the Lookup
Wizard for the Data Type and specifying
getting values from a table.
Finalize web accounts (for MySql)
• Use secure ftp
• REMEMBER YOUR MYSQL ACCOUNT
NUMBER & PASSWORD
phpMyAdmin
• Will do this with one of your accounts or
mine if I get one!
Working environment
You will
– Write php scripts [just] to create tables. These scripts
may just be executed one time.
You will
– create html and php files using Dreamweaver or
Textpad or Textwriter or equivalents.
– upload to your account using suggested ftp
– Test
• There is an alternative: setting up your computer
to be a server. This is fairly complex.
Reprise
• Useful to think in terms of 3 language
domains (my terminology):
– client side browser (interpreter of html)
– server side interpreter of asp/JavaScript or php
• (There is another level here for the operating
system, but this is what the asp objects and the php
built-in functions & variables provide.)
– database interpreter of SQL (by Access or
MySQL)
Homework
• Get html email examples working if you haven't
done so
– $_Request approach
• Practice with Access
• Practice with Open Office Base
– Extra Credit: Make posting comparing the two.
• Required: Find a good source on MySql and make
posting.
• [Continue review of HTML and ActionScript]