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 <?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; } ?>

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. <?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.<br>\n"); } else { print ("The table, $tname, was not created. <br>\n"); } } ?>

21. <html><head><title>Creating movie table </title> </head> <body> <?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); ?> </body> </html>

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 <html><head><title>Input movies </title></head> <body> <form action="inputmovies.php"> Movie Name <input type="text" name="mn"/> Description <textarea name="desc" rows="10" cols="50">…</textarea> <input type="input" name="my" /> <input type="submit" value="STORE"/> </form> </body> </html>

24. php handler to do INSERT <?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 ?>

27. showmovies.php Standard way to put db connect code in one place <html> <head><title>Show player scores</title> </head><body> <?php require("connectcode.php"); $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/>"); } ?> </body> </html>

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/addsong.html • action=addsong.php • http://newmedia.purchase.edu/~Jeanine/db/example/addfeature.html • action=addfeature.php • http://newmedia.purchase.edu/~Jeanine/db/example/addfeaturestosong1.php • action=addfeaturestosong2.php • http://newmedia.purchase.edu/~Jeanine/db/example/findothers1.php • action=findothers2.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/removesong1.php

33. from removesong1.php Select a song to be deleted with its features <hr/> <form action="removesong2.php"> <table> <?php require("opendbe.php"); $query="Select * from songs"; $result=mysql_db_query($DBname, $query, $link); while ($row=mysql_fetch_array($result)) { print("<tr>"); print("<td> <input type='radio' name='song' value='".$row['sid']."'/>"); print ($row['sname'] ."</td><td>".$row['sdesc']."</td><td>".$row['syear']."</td></tr>"); } ?> </table> <input type="submit" value="PICK SONG TO BE DELETED"/> </form>

34. from removesong2.php <?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.<br>\n"); } else { print ("The song $sid was not deleted. <br>\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.<br>\n"); } else { print ("The features for song $sid were NOT successfully added. <br>\n"); } ?>

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