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/db/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.phpNote: 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/videoproject/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