Creating databases for web applications

1
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

2
Money example

• and process
• First, quicktestexact
• lt?
• test _GET"test"
• pattern "(cat)(dog)"
• if (eregi(pattern,test))
• print("Entry test passed the test")
• else
• print("Entry test failed the test")
• ?gt

3
Method

• Method in both senses of the word
• Use Get so I can check using the query string
• http//newmedia.purchase.edu/Jeanine/db/quicktest
  exact.php?testcat
• passes
• http//newmedia.purchase.edu/Jeanine/db/quicktest
  exact.php?testmcat
• fails

4
What does this do?

• lt?
• test _GET"test"
• pattern "\\0-9"
• if (eregi(pattern,test))
• print("Entry test passed the test")
• else
• print("Entry test failed the test")
• ?gt

5
Work up

• http//newmedia.purchase.edu/Jeanine/db/quicktest
  mcommas.php?test1,234

6
Money with commas

• lt?
• test _GET"test"
• print ("string to be tested is test")
• pattern "\\1-90-90,2(\,0-93)(\.0-
  92)?"
• print ("ltbrgt")
• print ("pattern is pattern")
• print ("ltbrgt")
• if (ereg(pattern,test))
• print("Entry test passed the test")
• else
• print("Entry test failed the test")
• ?gt

7
Report

• Did you create a table?
• Did you use sources?

8
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

9
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!

10
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

11
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.

12
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!

13
example of a fake connectcode.php file

• lt?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'
• ?gt

14
Simple example (assumes a connectcode.php file

• createtable.php
• enterstudent.html
• enterstudent.php
• showstudents.php
• showdepartments.php

15
createtable.php

• lthtmlgtltheadgtlttitlegtCreating student table
  lt/titlegt lt/headgt ltbodygt
• lt?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.ltbrgt\n")
• else
• print ("The table, tname, was not created.
  ltbrgt\n")
• mysql_close(link)
• ?gt
• lt/bodygt lt/htmlgt

16
enterstudent.html

• lthtmlgtltheadgtlttitlegtInput student lt/titlegtlt/headgt
  ltbodygt
• ltform action"enterstudent.php"gt
• Student Name ltinput type"text" name"sn"/gtltbr/gt
• Department ltinput type"text" name"sd"gtltbr/gt
• GPA ltinput type"input" name"sgpa" /gtltbr/gt
• ltinput type"submit" value"STORE"/gt
• lt/formgt lt/bodygt lt/htmlgt

17
enterstudent.php

• lt?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)
• ?gt

18
showstudents.php

• lthtmlgtltheadgtlttitlegtList contents of students
  tablelt/titlegtlt/headgtltbodygt
• lt?php
• require("connectcode.php")
• query"SELECT FROM students"
• resultmysql_db_query(DBname, query, link)
• print("lttable border'1'gt")
• print("lttrgtltthgtStudentlt/thgtltthgtDepartmentlt/thgtltthgt
  GPA lt/thgtlt/trgt")
• while (rowmysql_fetch_array(result))
• print("lttrgt")
• print("lttdgt ".row'sname'."lt/tdgt")
• print ("lttdgt".row'sdept' ."lt/tdgt")
• print ("lttdgt".row'sgpa'."lt/tdgt")
• print ("lt/trgt")
• mysql_close(link)
• ?gt
• lt/tablegt lt/bodygtlt/htmlgt

19
showdepartments.php

• lthtmlgtltheadgtlttitlegtList department average gpa
  and countslt/titlegtlt/headgt
• ltbodygt
• lt?php
• require("connectcode.php")
• query"SELECT sdept, AVG(sgpa) as a, COUNT() as
  c FROM students GROUP BY sdept ORDER BY a DESC"
• resultmysql_db_query(DBname, query, link)
• print("lttable border'1'gt")
• print("lttrgtltthgtDepartmentlt/thgtltthgtAverage GPA
  lt/thgtltthgtCOUNT lt/thgtlt/trgt")
• while (rowmysql_fetch_array(result))
• print("lttrgt")
• print("lttdgt ".row'sdept'."lt/tdgt")
• print ("lttdgt".row'a' ."lt/tdgt")
• print ("lttdgt".row'c'."lt/tdgt")
• print ("lt/trgt")
• mysql_close(link)
• ?gt
• lt/tablegtlt/bodygtlt/htmlgt

20
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.

21
Extra credit

• Report on
• Ruby on Rails
• Groovy
• ??

22
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

23
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.

24
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.

25
Looping

• query"SELECT FROM movies"
• rsmysql_query( query)
• while (rowmysql_fetch_array(rs))
• print(row'mname' . " (" .row'myear' . ")
  ltbr/gt ")
• print(row'mdesc')
• print("lthr width'500'/gt")

26
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

27
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 ltoptiongt element

28
From choosecategory.phpNote earlier code
started the ltformgt

• query"SELECT DISTINCT category FROM questions"
• categories mysql_db_query(query)
• while (rowmysql_fetch_array(categories))
• catrow'category'
• print ("ltoption value'cat'gtcatlt/optiongtltbrgt\n
  ")

29
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

30
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.

31

• query"CREATE temporary TABLE past (item_id
  INT)"
• queryquery . " SELECT question_id FROM history
  where (player_id'".player_id
• queryquery . "' AND (whenplayed'".today."'
  OR correct))"
• resultmysql_db_query(query)
• query"SELECT FROM past"
• result mysql_db_query(query)
• Num_past mysql_num_rows(result)
• if (Num_pastgt0)
• 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. "'"
• resultmysql_db_query( sel)

32
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.

33
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
  /videoproject/tagdisplay.html

34

• tag _GET'tag'
• query "SELECT v.fileaddr, v.cliptitle FROM
  videoclips2 AS v JOIN taggedclips AS t ON
  v.fileaddrt.fileaddr "
• query . "WHERE t.tag'" . tag . "'"
• result mysql_query(query)
• sendback ""
• mysql_close()
• num mysql_num_rows(result)
• i 0
• while (iltnum)
• fa mysql_result(result,i,"fileaddr")
• ct mysql_result(result,i,"cliptitle")
• sendback sendback . "fact"
• i
• if (iltnum)
• sendback sendback . ","
• print "sendback"

35
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

36
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

37
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

38
SELECT example

• SELECT department, AVG(gpa) FROM student_info
  WHERE credits gt60
• GROUP BY department
• HAVING AVG(gpa) gt 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.

39
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.fidf2.fid AND
• f2.sid!'sid' AND
• s.sidf2.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.

40
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.

41
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
  smaller files/programs/scripts.
• The exact structure of the diagrams less
  important than using some type of representation.
• Focus on this next class.

42
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.