Creating databases for web applications

Transcript Creating databases for web applications

Creating databases for web
applications
Report on using sources, getting examples working.
Retrieve information from database. Recordsets.
Arrays. Loops.
Homework: Get examples working. Post unique source
on entity relationship diagrams or data flow diagrams
Money example
… and process
First, quicktestexact:
<?
$test = $_GET["test"];
$pattern = "^(cat)|(dog)$";
if (eregi($pattern,$test)) {
print("Entry $test passed the test"); }
else {
print("Entry $test failed the test"); }
?>
Method…
Method in both senses of the word:
Use Get so I can check using the query
string:
http://newmedia.purchase.edu/~Jeanine/db/
quicktestexact.php?test=cat
…passes
http://newmedia.purchase.edu/~Jeanine/db/
quicktestexact.php?test=mcat
…fails
What does this do?
•
•
•
•
•
•
•
•
<?
$test = $_GET["test"];
$pattern = "^\\$[0-9]$";
if (eregi($pattern,$test)) {
print("Entry $test passed the test"); }
else {
print("Entry $test failed the test"); }
?>
Work up …
• http://newmedia.purchase.edu/~Jeanine/d
b/quicktestmcommas.php?test=$1,234
Money with commas
•
•
•
•
<?
$test = $_GET["test"];
print ("string to be tested is $test");
$pattern = "^\\$[1-9][0-9]{0,2}(\,[0-9]{3})*(\.[0-9]{2})?$";
•
•
•
•
•
•
•
•
print ("<br>");
print ("pattern is $pattern");
print ("<br>");
if (ereg($pattern,$test)) {
print("Entry $test passed the test"); }
else {
print("Entry $test failed the test"); }
?>
Report
• Did you create a table?
• Did you use sources?
Outline of work
• Understand php and MySQL examples
– Get your own versions working
• Learn [about] system design and documentation
– Find and study sources on ERD and DFD
– Create diagrams for a library
MIDTERM
• Design and build enhanced versions of sample
projects
• Propose, design, present and build your own
projects
FINAL
Sample projects
• HTML & php
– Quiz show
– Orders
– last time: Songs with features
• Flash ActionScript & php
– Design plans
– Video clip archive
Demonstrate
Reminder: code accessible from moodle
My goal is 1 team for each!
Questions?
Any problems
• creating tables in a database
– If a table with that name exists, you need to
DROP TABLE ….. before creating a new one
• INSERT INTO….
• SELECT
Making connection
• A standard practice is to put the private
information regarding the database in its own
file, say it is called connectcode.php and then
using
require("connectcode.php");
In each program that requires connection to the
database.
• My examples used files named opendb.php or
something similar.
• Some of my examples use $DBname. That is,
the code assumes that has been set with the
database name.
Your database
• In the email sent to you after the request for
MySql access, there was
– name,
– database name
– password
• In the following example, WKILLORAN is the
name, WKILLORAN_DB is the database name
and PASSWORD is the password.
• SUBSTITUTE FROM THE EMAIL SENT TO
YOU!
example of a fake connectcode.php
file
<?php
$link = mysql_connect('localhost', 'WKILLORAN',
'PASSWORD'); if (!$link) {
die('Not connected : ' . mysql_error());
}
if (!mysql_select_db('WKILLORAN_DB', $link)) {
echo 'Could not select database';
exit; }
$DBname = 'WKILLORAN_DB';
?>
Simple example (assumes a
connectcode.php file
createtable.php
enterstudent.html
enterstudent.php
showstudents.php
showdepartments.php
createtable.php
<html><head><title>Creating student table </title> </head> <body>
<?php
require("connectcode.php");
$tname = "students";
$fields = "sid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
sname CHAR(30), sgpa FLOAT(5,4), sdept CHAR(30)";
$query = "DROP TABLE students";
mysql_query($query);
$query = "CREATE TABLE $tname ($fields)";
if (mysql_query($query)) {
print ("The table, $tname, was created successfully.<br>\n");
}
else {
print ("The table, $tname, was not created. <br>\n");
}
mysql_close($link);
?>
</body> </html>
enterstudent.html
<html><head><title>Input student </title></head>
<body>
<form action="enterstudent.php">
Student Name <input type="text"
name="sn"/><br/>
Department <input type="text" name="sd"><br/>
GPA <input type="input" name="sgpa" /><br/>
<input type="submit" value="STORE"/>
</form> </body> </html>
enterstudent.php
<?php
require("connectcode.php");
$sn =$_GET['sn'];
$sn = ADDSLASHES($sn);
$sd=$_GET['sd'];
$sd = ADDSLASHES($sd);
$sgpa = $_GET['sgpa'];
if (!is_numeric($sgpa)) { print ("Bad GPA data."); }
else { $query="INSERT INTO students VALUES (";
$query.="'0','$sn',$sgpa,'$sd')";
print ("query is $query. ");
if (mysql_query($query)) {
print ("Student record added successfully"); }
Else {
print ("failure to add student record "); }
}
mysql_close($link);
?>
showstudents.php
<html><head><title>List contents of students
table</title></head><body>
<?php
require("connectcode.php");
$query="SELECT * FROM students";
$result=mysql_db_query($DBname, $query, $link);
print("<table border='1'>");
print("<tr><th>Student</th><th>Department</th><th>GPA </th></tr>");
while ($row=mysql_fetch_array($result)) {
print("<tr>");
print("<td> ".$row['sname']."</td>");
print ("<td>".$row['sdept'] ."</td>");
print ("<td>".$row['sgpa']."</td>");
print ("</tr>");
}
mysql_close($link);
?>
</table> </body></html>
showdepartments.php
<html><head><title>List department average gpa and
counts</title></head>
<body>
<?php
require("connectcode.php");
$query="SELECT sdept, AVG(sgpa) as a, COUNT(*) as c FROM
students GROUP BY sdept ORDER BY a DESC";
$result=mysql_db_query($DBname, $query, $link);
print("<table border='1'>");
print("<tr><th>Department</th><th>Average GPA </th><th>COUNT
</th></tr>");
while ($row=mysql_fetch_array($result)) {
print("<tr>");
print("<td> ".$row['sdept']."</td>");
print ("<td>".$row['a'] ."</td>");
print ("<td>".$row['c']."</td>");
print ("</tr>");}
mysql_close($link);
?>
</table></body></html>
MySQL queries
• ALL return something!
• This can be indication of success or a
pointer to array (like) object holding data
• php has built-in commands for interactions
with MySQL.
• NOTE: also has commands for generic
DBMS
– EXTRA CREDIT opportunity to report on php
and other DBMS or report on comparisons.
Extra credit
Report on
• Ruby on Rails
• Groovy
• ??
SELECT overview
• Extract some or all fields in table(s)
– Also operations: COUNT, SUM, AVG
• Set conditions on which records by expression
involving fields in one or more tables
– WHERE, JOIN … ON
• Aggregate GROUP records
• Set conditions on the aggregated data HAVING
• Set LIMITS
Basic
$query="SELECT * FROM movies";
Could have used
$query ="SELECT mname, mdesc, myear FROM
movies";
– Since I suggested not displaying the mid field
Use $mysql_fetch_array to extract a row.
This is an associative array that represents the
constructed record of the selected data
The results of $mysql_fetch_array can be used as a
condition. When it fails, there were no more rows.
Performance
• how much time is taken by an SQL
statement and/or php code
• This can be significant, that is, amount to
something significant in large databases.
• DBMS have tools to determine this and
you also can experiment.
Looping
$query="SELECT * FROM movies";
$rs=mysql_query( $query);
while ($row=mysql_fetch_array($rs)){
print($row['mname'] . " (" .$row['myear'] .
"): <br/> ");
print($row['mdesc']);
print("<hr width='500'/>");
}
What looping does
• Builds up a [long] string of html in this case
(string encoded using my own design in
the video clip archive case)
• Use the concatenate operator .
• Note [those pesky] single quotation marks
– For the indexing into the associative array that
is the row of data
– For the attribute values in the html
Example from trivia quiz
• Each question has a designated category
• Objective: In HTML, build a pulldown menu as
part of a form of the possible categories of
questions
• Solution: use DISTINCT modified SELECT
statement to get a recordset
• Use $mysql_fetch_array to extract a row.
– This is an associative array that represents the
constructed record of the selected data
– The results of $mysql_fetch_array can be used as a
condition. When it fails, there were no more rows.
• Use the data to build an <option> element
From choosecategory.php
Note: earlier code started the <form>
$query="SELECT DISTINCT category
FROM questions";
$categories = mysql_db_query($query);
while
($row=mysql_fetch_array($categories))
{ $cat=$row['category'];
print ("<option
value='$cat'>$cat</option><br>\n");
}
Example from trivia quiz
• Decided on policy to NOT ask any
question that a player had answered
correctly OR a question posed that had
been posed already that day.
• Solution:
– build temporary table of questions answered
correctly at any time or posed today.
– Use so-called left join to choose questions
that do NOT satisfy the constraint, that is, not
in the temporary table
Caution / Encouragement
• SELECT statements can be equivalent to many
lines of code
So
• Give yourself time to understand and create it
• You do not have to do it the shortest way and/or
only one statement. This may not even be the
most efficient.
• Build up $query in stages (using .) and
use print ("The query is $query"); at least with
html/php.
– Need to work harder with Flash! Set up a text area to
display results.
$query="CREATE temporary TABLE past (item_id INT)";
$query=$query . " SELECT question_id FROM history where
(player_id='".$player_id;
$query=$query . "' AND (whenplayed='".$today."' OR correct))";
$result=mysql_db_query($query);
$query="SELECT * FROM past";
$result = mysql_db_query($query);
$Num_past = mysql_num_rows($result);
if ($Num_past>0) {
$sel = "SELECT questions.question_id, question, answerpattern, value
from questions";
$sel =$sel . " LEFT JOIN past ON questions.question_id =
past.question_id WHERE ";
$sel = $sel . " category='" . $pickedcategory . "' AND past.question_id IS
NULL";
}
else {
$sel="SELECT question_id, question, answerpattern, value from
questions ";
$sel= $sel . " WHERE category= '" . $pickedcategory. "'";
}
$result=mysql_db_query( $sel);
Example from video clip archive
• Flash ActionScript implementation
• One table holds information on video clips
• Another table holds tags: one record for
each tag and clip combinations. Since
there can be any number of tags for a
video clip, this cannot be a field in the
main table.
Example, continued
• Objective: build tag area, with size dependent on
number of clips with that tag. When player clicks
on tag, build pulldown listing the clips. When
player clicks on option, play the clip.
• Solution: use SELECT … JOIN … ON to select
relevant records. Build string to be sent back to
Flash program.
• Demonstrate:
http://newmedia.purchase.edu/~Jeanine/videopr
oject/tagdisplay.html
$tag = $_GET['tag'];
$query = "SELECT v.fileaddr, v.cliptitle FROM videoclips2
AS v JOIN taggedclips AS t ON v.fileaddr=t.fileaddr ";
$query .= "WHERE t.tag='" . $tag . "'";
$result = mysql_query($query);
$sendback = "";
mysql_close();
$num = mysql_num_rows($result);
$i = 0;
while ($i<$num) {
$fa = mysql_result($result,$i,"fileaddr");
$ct = mysql_result($result,$i,"cliptitle");
$sendback = $sendback . "$fa:$ct";
$i++;
if ($i<$num) {
$sendback = $sendback . ","; }
}
print "$sendback";
Comments
• The AS t and AS v allow you to use a shorter
name in referencing the fields.
• Remember that equality condition in SQL is one
equal sign.
• Remember to use single quotation marks
around strings.
– If $tag is a variable holding a string, the SQL
statement needs the quotation marks!
• The 'sent back' data is itself encoded as a
comma separated list of clip addresses and clip
titles
GROUP HAVING
• SQL can aggregate (GROUP) records
• SQL can accept a condition applied to the
GROUPs
– The WHERE and the JOIN ON apply to the
individual records
• GROUP generally is applied using
aggregate functions, such as
SUM, COUNT, AVG, MIN, MAX
Question
• Assume a table of student information,
including major, gpa, credits
• What are the majors that have the highest
averages among their students AND let's
just consider juniors and seniors?
– Define junior and senior as having more than
60 credits
– Display the top 3 majors along with their
average GPA
SELECT example
SELECT department, AVG(gpa) FROM student_info
WHERE credits >60
GROUP BY department
HAVING AVG(gpa) > 3
ORDER BY AVG(gpa) DESC
LIMIT 3
Select student records that have more than 60 credits.
Group by common department. Take an average over
the gpa fields. Just select those groups (that is,
departments) where the average is over 3 (over B).
Order from highest to lowest. Just take the first (top) 3.
Select example from songs with
features (findothers2.php)
• $sid is the id of a selected song
SELECT sname, COUNT(*) as c from featuresinsongs as
f1, songs as s, featuresinsongs as f2 WHERE
f1.sid='$sid' AND
f1.fid=f2.fid AND
f2.sid!='$sid' AND
s.sid=f2.sid GROUP BY sname
ORDER BY c DESC, sname
The featuresinsongs table has song id field and feature id
field. The name of a song is in the songs table.
Select all the song and feature records that have features
in common with features possessed by $sid. But don't
select any song/feature row in which the song id IS $sid.
Group by sname. Order from biggest to smallest.
3-tier model
• Database (MySQL data) holds the
information.
• The middleware (php) is the application of
the business decisions.
• The HTML and/or the FLASH program
handles the interactions with people,
including the presentation of results.
• Reflect on this in terms of examples.
Motivation for Diagrams
• Diagrams are tools for the design stage and for
documentation.
• Databases that are more than one table are
represented by Entity Relationship Diagrams.
• Applications (using databases or not) are
represented by Data Flow diagrams.
• Database applications typically involve many
small[er] files/programs/scripts.
• The exact structure of the diagrams less important
than using some type of representation.
• Focus on this next class.
Homework
• Keep practicing with php and MySQL.
• Consider adding to the movies application.
• Find, review, and post reference and
comments on unique source for Entity
Relationship Diagrams and/or Data Flow
Diagrams.

Creating databases for web applications

Transcript Creating databases for web applications

Directory