Creating databases for web applications

1
Creating databases for web applications

• Regular expression homework
• Database datatypes. Creating database
• Homework Create tables in database. Add records
  to database.

2
Homework

• Design (and test) regular expressions to search
  for each of the following
• a string with "curley", "larry", or "moe"
  somewhere in it. Case does not matter.
• a dollar amount for example, accept 2.59, 10,
  1,200 and reject 1.2345, 3.4.5.
• Valid date in MM/DD/YYYY or MM/DD/YY format (for
  example, 14/2/2001 would not be acceptable. See
  if you can allow 1/4/04 as well as 01/04/2004.
• For state caps quiz New York or NY, St. Paul or
  Saint Paul
• accept Obama, Barack Obama, Barack Hussein Obama,
  but NOT Michelle Obama, Malia Obama, etc.

3
Homework, cont.

• Design (create) 3 questions for a quiz show game
  and design regular expressions that validate the
  answers. The challenge is to be no more and no
  less exacting than a human checker.

4
Database design

• Not easy!
• Will discuss formal methods next week
• Review databases are made up of
• Tables tables made up of
• Records records made up of fields
• Speaking of rows and columns is misleading
• Critical issue fixed number of fields, though a
  specific field may be optional (aka not required)
• NOT NULL in MySQL jargon means required!
• MySQL does support variable length strings.

5
Data types

• Terminology varies for different DBMS products
• Performance (speed) of operations varies with
  different datatypes
• Size varies with different datatypes
• Performance and size limits are points of
  competition among the different products

6
MySQL datatypes numbers

• INT (aka INTEGER), can be UNSIGNED (Size 4 bytes
  32 bits)
• TINYINT, SMALLINT, MEDIUMINT, BIGINT
• Different sizes
• float (4 bytes), double (8 bytes), can specify
  precision within these limits
• more

7
MySQL datatypes, strings

• CHAR(specified length)
• VARCHAR(maximum length)
• TINYBLOB short, variable length string, up to
  255 characters
• BLOB, TEXT variable length string
• MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT

8
MySQL datatypes enum

• ENUM
• Specify one of a set of values
• Stored as an integer, with 0 indicated unset or
  not in the specified set
• Doing this may be more efficient because built-in
  MySQL routines do the searching

9
MySQL datatypes date/time

• DATE
• TIME
• DATETIME
• YEAR
• TIMESTAMP

10
Tables

• Specify one field as the primary key
• Primary keys are unique IN THAT TABLE
• Let the DBMS create the primary key OR
• Depend on intrinsic value that is guaranteed to
  be unique
• Email addresses
• ISBN numbers
• ?
• A field in one table may be a foreign key. This
  is a reference to a primary key in another table
  (or this table). MORE ON THIS LATER.

11
Database

• Assume database itself is created for us AND we
  have permissions to create new tables.
• NOTE permissions can be set by MySQL commands,
  including queries sent by php.
• Start off talking general SQL and then specific
  php and MySQL

12
Create table example

• CREATE TABLE movies (mid INT NOT NULL
  AUTO_INCREMENT PRIMARY KEY,mname CHAR(30),
• mdesc TEXT,
• myear YEAR
• )

13
Create table example

• CREATE TABLE players ( pid INT NOT NULL
  AUTO_INCREMENT PRIMARY KEY,pname CHAR(30),
• score INT NOT NULL,
• lastplayed DATE
• )

14
Create example

• CREATE TABLE games (gid INT NOT NULL
  AUTO_INCREMENT PRIMARY KEY,pid INT,
• gtime TIMESTAMP,
• score INT
• )

The pid field will refer to / have the value of
the pid field (the primary key) of a specific
player. Here in this table, it is called a
foreign key.
15
Foreign keys

• Some versions of MySQL (and other DBMS) have ways
  to specify the the pid value is a foreign key
• pid INT REFERENCE players
• The DBMS will check to make sure it is a valid
  value.
• Since the php coding should guarantee this, I
  omit this from my examples.
• Extra credit opportunity for posting on this.

16
Class Exercises

• Write the CREATE TABLE statement for a table
• MySQL generated id, course 'number' (MAT3530.45),
  name, cap, credits, teacher, time slot, building
• Why can't MAT3530.45 be the primary key?
• Published book the ISBN number can serve as
  primary key, title, year of publication
• Your own idea

17
Now, show the code

• Application includes 5 files!
• createatable.php
• Drops table and creates new one. No problem if
  table does not exist.
• entertable.html
• Points to inputmovies.php in form action
• inputmovies.php
• Inserts record using the passed form data
• connectcode.php
• File for connecting to database examples used
  different names, such as opendb.php
• showmovies.php
• Retrieves records from table and displays them

18
connectcode.php

• lt?php
• link mysql_connect('localhost', 'WKILLORAN',
  'PASSWORD') if (!link)
• die('Not connected ' . mysql_error())
• DB
• if (!mysql_select_db('WKILLORAN_DB', link))
• echo 'Could not select database'
• exit
• ?gt

19
Comment

• Put the connect code in a file by itself, to be
  included
• require (connectcode.php)
• This means that you can share the other files and
• Just need to change one file.
• NOTE check my use of Dbname and link.

20

• lt?php
• function createtable(tname,fields)
• global DBname, link
• query "DROP TABLE tname"
• 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")
• ?gt

21

• lthtmlgtltheadgtlttitlegtCreating movie table lt/titlegt
  lt/headgt ltbodygt
• lt?php
• require("connectcode.php")
• tname "movies"
• fields "mid INT NOT NULL AUTO_INCREMENT
  PRIMARY KEY, mname CHAR(30), mdesc TEXT, myear
  YEAR"
• createtable(tname, fields)
• mysql_close(link)
• ?gt
• lt/bodygt lt/htmlgt

22
Add records to table

• INSERT INTO movies VALUES (0,
  'WALL-E','computer generated animation, with
  environmental theme',2008)
• OR
• INSERT INTO movies SET mname 'WALL-E', mdesc
  ' 'computer generated animation, with
  environmental theme', myear 2009

MySQL will generate the mid
23
HTML form

• lthtmlgtltheadgtlttitlegtInput movies lt/titlegtlt/headgt
  ltbodygt
• ltform action"inputmovies.php"gt
• Movie Name ltinput type"text" name"mn"/gt
• Description lttextarea name"desc" rows"10"
  cols"50"gtlt/textareagt
• ltinput type"input" name"my" /gt
• ltinput type"submit" value"STORE"/gt
• lt/formgt lt/bodygt lt/htmlgt

24
php handler to do INSERT

• lt?php
• require("connectcode.php")
• mn _GET'mn'
• desc_GET'desc'
• my _GET'my'
• query"INSERT INTO movies VALUES ("
• query."'0','mn','desc',my)"

Value of variables mn, desc,my are made part
of string
25
Comments

• The connectcode.php has database name and
  password. It sets link used later.
• NOTE trick of php that variable names inside
  double quotes get evaluated.
• NOTE the presence of the single quotes around
  strings. These are required by MySQL (and other
  DBMS)
• Code needs to be improved What if name or
  description had quotes or slashes? Try it and
  then add
• desc addslashes(desc)

26

• if (mysql_query(query))
• print ("movie added successfully")
• Else
• print ("failure to add movie ")
• mysql_close(.) //this depends on db link
• ?gt

27
showmovies.php

• lthtmlgt ltheadgtlttitlegtShow player scoreslt/titlegt
  lt/headgtltbodygt
• lt?php
• require("connectcode.php")
• query"SELECT FROM movies"
• rsmysql_query( query)
• while (rowmysql_fetch_array(rs))
• print(row'mname' . " (" .row'myear' . ")
  ltbr/gt ")
• print(row'mdesc')
• print("lthr/gt")
• ?gt
• lt/bodygt lt/htmlgt

Standard way to put db connect code in one place
28
Comments

• Go over this again next class
• Put the db connection code in separate file, to
  be included in all other files
• The fields are extracted by name.
• Chose not to extract and display mid
• The rs produced by the SELECT query is called a
  recordset. It is like a table. Its rows can be
  extracted one at a time.

29
More SQL

• ALTER this is for changing or adding a field
  definition
• DROP this is dropping a table. This is good to
  do before creating a table, just in case you
  already did it OR don't drop it, and check for
  errors.

30
Caution

• My songs with features database contains nonsense
  that I put in to quickly test the applications.
• Also, no provision for duplicates or near
  duplicates

31
Songs with features examples

• http//newmedia.purchase.edu/Jeanine/db/example/a
  ddsong.html
• actionaddsong.php
• http//newmedia.purchase.edu/Jeanine/db/example/a
  ddfeature.html
• actionaddfeature.php
• http//newmedia.purchase.edu/Jeanine/db/example/a
  ddfeaturestosong1.php
• actionaddfeaturestosong2.php
• http//newmedia.purchase.edu/Jeanine/db/example/f
  indothers1.php
• actionfindothers2.php
• Note last two need to go to database to present
  a form for user.

32
Remove application

• Need to remove song PLUS all the feature/song
  records
• http//newmedia.purchase.edu/Jeanine/db/example/r
  emovesong1.php

33
from removesong1.php

• Select a song to be deleted with its features
• lthr/gt ltform action"removesong2.php"gt
• lttablegt
• lt?php
• require("opendbe.php")
• query"Select from songs"
• resultmysql_db_query(DBname, query, link)
• while (rowmysql_fetch_array(result))
• print("lttrgt")
• print("lttdgt ltinput type'radio' name'song'
  value'".row'sid'."'/gt")
• print (row'sname' ."lt/tdgtlttdgt".row'sdesc'."
  lt/tdgtlttdgt".row'syear'."lt/tdgtlt/trgt")
• ?gt
• lt/tablegt ltinput type"submit" value"PICK SONG TO
  BE DELETED"/gt lt/formgt

34
from removesong2.php

• lt?php
• require("opendbe.php")
• sid_GET"song"
• query "Delete from songs where sid'sid'
  limit 1"
• print (query)
• result mysql_db_query(DBname,query,link)
• if (result)
• print("The song sid was deleted.ltbrgt\n")
  
• else
• print ("The song sid was not deleted.
  ltbrgt\n")
• query "Delete from featuresinsongs where
  sid'sid'"
• result mysql_db_query(DBname,query,link)
• if (result)
• print("The features for song sid were
  deleted.ltbrgt\n")
• else print ("The features for song sid
  were NOT successfully added. ltbrgt\n")
• ?gt

35
Notes

• The bad results let me know that I had the name
  of the tables wrong!
• song for songs
• featuresinsong for featuresinsongs

36
Who are the agents?

• In existing find similar songs applications, the
  assigning of features may not be an end user
  function..

37
Homework

• Confirm that you can access or create a database
• Write php scripts to create table
• Write html and php to INSERT records
• Write php to display whole table
• Use songs, movies model and posted on-line
  sources!
• Post comments
• Improve appearance
• Extra credit insert default values. Do
  validation.
• Look up syntax and write php with ALTER and DROP
  queries.
• USE SOURCES!!!