creating databases for web applications database datatypes. creating database homework: create...
TRANSCRIPT
![Page 1: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/1.jpg)
Creating databases for web applications
Database datatypes. Creating databaseHomework: Create tables in database. Add
records to database.
![Page 2: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/2.jpg)
Database design
• Not easy!• Formal methods exist.• 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– MySQL does support variable length strings.
![Page 3: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/3.jpg)
Critical requirement
• Want information present only one place in database– Don’t need to change your address multiple
times.
• Reference integrity– Reference in one record point to existing record
• E.g., Orders point to actual customer
• Order detail point to actual orders
![Page 4: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/4.jpg)
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 DBMS products
![Page 5: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/5.jpg)
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
![Page 6: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/6.jpg)
MySQL datatypes, strings
• CHAR(specified length)• VARCHAR(maximum length)
– trade off time to handle variable length versus space to not always store max. length
• TINYBLOB short, variable length string, up to 255 characters
• BLOB, TEXT variable length string• MEDIUMBLOB, MEDIUMTEXT,
LONGBLOB, LONGTEXT
![Page 7: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/7.jpg)
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
![Page 8: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/8.jpg)
MySQL datatypes: date/time
• DATE
• TIME
• DATETIME
• YEAR
• TIMESTAMP
![Page 9: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/9.jpg)
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. MORE ON THIS LATER.
![Page 10: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/10.jpg)
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
![Page 11: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/11.jpg)
phpMyAdmin• Can use this to create tables in your one database.• The next slides show SQL Create statements to do this.• https://socialsoftware.purchase.edu/phpmyadmin • NOTE THE https!!!!!• Next sign in with your equivalent of
[email protected] and email passwordTHEN use the special MySQL user name and password.This was in the Readme file. You can change the
password.Click on password name (jmeyer_db) on the left and then
you can create a new table.EVERYONE get to this point!
![Page 12: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/12.jpg)
Create table example
• CREATE TABLE movies (mid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,mname CHAR(30),
mdesc TEXT,
myear YEAR
)
![Page 13: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/13.jpg)
Create table example
CREATE TABLE players ( pid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,pname CHAR(30),
score INT NOT NULL,
lastplayed DATE
)
![Page 14: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/14.jpg)
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.
![Page 15: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/15.jpg)
Foreign keys• Some versions of MySQL (and other DBMS) have
ways to specify that the pid value is a foreign key pid INT REFERENCE players• The DBMS then will check to make sure it is a
valid value.• Since my JavaScript & php coding should
guarantee this in various places, I omit specifying this from my examples.– Extra credit opportunity for posting on this.– It may be that letting MySQL catch errors and trapping
the errors in the php is a better approach
![Page 16: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/16.jpg)
Class Exercises
Write the CREATE TABLE statement for a table• MySQL generated id, course 'number'
(MAT3530.45), name, cap, credits, teacher, time slot, building
• Published book: the ISBN number can serve as primary key, title, year of publication
• Your own idea
![Page 17: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/17.jpg)
Inherent challenges for implementation
… in developing database applications
• database persists. That is, data in database lasts (that is the whole point) so my/your/our testing needs to be aware of that.
• I forgot the passwords for larry, curly and moe.
![Page 18: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/18.jpg)
2 table example
Bookmarks application: finders and sites• http://socialsoftware.purchase.edu/jeanine.meye
r/research/register.html
• http://socialsoftware.purchase.edu/jeanine.meyer/research/addsite.html
• http://socialsoftware.purchase.edu/jeanine.meyer/research/showsites.php
• http://socialsoftware.purchase.edu/jeanine.meyer/research/showsitesbycategory1.php
![Page 19: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/19.jpg)
Create the tables<?phpfunction createtable($tname,$fields) {global $DBname, $link;$query = "DROP TABLE $tname";mysqli_query($link,$query);$query="CREATE TABLE ".$tname."(".$fields.")";if (mysqli_query($link,$query)) { print ("The table, $tname, was created successfully.<br>\n");
}else { print ("The table, $tname, was not created. <br>\n"); }}?>
![Page 20: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/20.jpg)
<html><head><title>Creating bookmark tables </title> </head>
<body><?phprequire("opendbo.php");$tname = "sitesfinders";$fields="sid INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY, stitle char(50), sdate DATE, surl char(100), sdescription TEXT, scategory char(30),
finderid INT ";createtable($tname, $fields);
![Page 21: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/21.jpg)
$tname = "finders";$fields = "finderid INT UNSIGNED NOT
NULL AUTO_INCREMENT PRIMARY KEY, username char(50), epw char(64)";
createtable($tname,$fields);mysql_close($link);?></body></html>
![Page 22: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/22.jpg)
adding a site
addsite.html
Use two scripts (files)
Make input fields new types.This should/will provide some checking, aka
form validation.
addsite.php
![Page 23: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/23.jpg)
Simplified addsite.html
<!DOCTYPE html><html> <head> <title>Add site</title> </head><body><form action="addsite.php">Your name for site: <input name="stitle"/><br/>Date: <input name="sdate" type="date" /> <br/>Site description:<input name="sdesc" width="300"/><br/>Web address: <input name="surl" type="url"/><br/>Category: <input name="scat" type="text"/><br/><input type="submit" value="Submit Site"/></form></body></html>
![Page 24: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/24.jpg)
Simplified addsite.php<html> <head> <title>Add song to database</title> </head> <body>
<?phprequire("opendbo.php");$tname = "sites";$stitle=addslashes($_GET["stitle"]);$sdate=addslashes($_GET["sdate"]);$sdesc=addslashes($_GET["sdesc"]);$surl=addslashes($_GET["surl"]);$scat = addslashes($_GET["scat"]);$query = "INSERT INTO $tname values
('0','$stitle','$sdate','$surl','$sdesc','$scat')";$result = mysqli_query($link,$query);if ($result) {
print("The site was successfully added.<br>\n"); }else { print ("The site was NOT successfully added. <br>\n"); }?></body> </html>
![Page 25: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/25.jpg)
Actual addsite application
• Uses localStorage for id and pw
• Encodes password using clientside coding– SHA256 algorithm
• Checks id and password using finders table
• Only then adds to sitesfinders table
![Page 26: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/26.jpg)
NOTE
• Should do checking on Date and URL fields.– This can be enhancement for the group
project.
• The addslashes 'escapes' any special characters. We see that again in next example.– May not be necessary all the time.
![Page 27: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/27.jpg)
Show bookmark/sites
• all the sites (and all the fields for each record)• select one of the categories
– use php to get list of DISTINCT category entries– use php to select exactly those
• In both cases, the query returns an array of associative (by name) arrays. The names are the field names of the table.
• My php code creates a table, one row for each record.
• I chose to display the URLs as the href of an a tag AND as the contents of the a element.
![Page 28: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/28.jpg)
showsites.php<html> <head> <title>List contents of sites table</title> </head> <body>
<?phprequire("opendbo.php");$query="SELECT * FROM sites ORDER BY sdate DESC";$result=mysqli_query($link, $query);print("<table border='1'>");print("<tr><th>Title</th><th>URL</th><th>Date </th><th>Description
</th><th>Category </th></tr>");while ($row=mysqli_fetch_array($result)) {print("<tr>"); print("<td> ".$row['stitle']."</td>"); print ("<td><a href='".$row['surl'] ."' target='_new'>".$row['surl']."</a></td>"); print ("<td>".$row['sdate']."</td>"); print ("<td>".$row['sdescription']."</td>"); print ("<td>".$row['scategory']."</td>"); print ("</tr>"); }mysqli_close($link);?>
</table></body> </html>
![Page 29: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/29.jpg)
show by category
• Two php files, one calling the other as the action of a form
• Form has select
showsitesbycategory1.php showsitesbycategory2.php
![Page 30: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/30.jpg)
showsitesbycategory1.php<html><head><title>List sites in category</title></head><body>
Pick the category you want:<br/><form action="showsitesbycategory2.php" method="get">Choices: <select name="pickedcategory">
<?phprequire("opendbo.php");
$query="SELECT DISTINCT scategory FROM sites";$categories = mysqli_query($link, $query);while ($row=mysqli_fetch_array($categories)){ $cat=$row['scategory']; print ("<option value='$cat'>$cat</option><br>\n"); }mysqli_close($link);print ("</select>");print ("<input type=submit name=submit value=\"Choose!\">
<br>\n");print ("</form>");mysqli_close($link);?></table> </body> </html>
![Page 31: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/31.jpg)
from showsitesbycategory2.php<?php$scat = $_GET['pickedcategory'];print "Sites in $scat category <br/>";require("opendbo.php");$query="SELECT * FROM sites WHERE scategory ='$scat' ORDER
BY sdate DESC";$result=mysqli_query($link, $query);
![Page 32: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/32.jpg)
$NoR=mysql_num_rows($result);if ($NoR==0) {
print ("No sites in that category"); } //should not happenelse {print("<table border='1'>");print("<tr><th>Title</th><th>URL</th><th>Date
</th><th>Description </th></tr>");while ($row=mysqli_fetch_array($result)) {print("<tr>"); print("<td> ".$row['stitle']."</td>"); print ("<td><a href='".$row['surl'] ."' target='_new'>".
$row['surl']."</a></td>"); print ("<td>".$row['sdate']."</td>"); print ("<td>".$row['sdescription']."</td>"); print ("</tr>"); } print ("</table>"); }mysqli_close($link);?>
![Page 33: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/33.jpg)
new example: stories• A story is a sequence of
– scenes, each holding text and html image tags, with title and author.
• There may be choices at certain points.
• http://socialsoftware.purchase.edu/jeanine.meyer/newstories/tellStory.php
• Room for improvement– This is one of the projects to enhance.
![Page 34: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/34.jpg)
3 tables
• stories2: holds title, author, scene, permflag plus a primary key auto-increment field generated by MySQL
• parentchild: holds one record for each (forward) link: parent to child (scene to a successor) plus a primary key …
• starts: holds one record for each scene designated as start of a story: first (pointer into stories2 table) plus a primary key …
![Page 35: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/35.jpg)
Entity Relationship Diagram
stories2 sid title scene author permflag
parentchild pcid parent child
starts
stid
first
each parent, child, first is exactly 1 record in stories2
0
0
0
A record in stories2 may not be represented as a parent or child or first
![Page 36: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/36.jpg)
Comment
• Could this be done other ways? Yes.– My first attempt used one table with prev and
next fields
![Page 37: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/37.jpg)
Example of SQL SELECTThe php variable $next holds the current scene. Code gathers
information on all child (successor) scenes to present choice to viewer.
• in SQL (as much as possible with $next holding a value)SELECT s.sid, s.title, s.author FROM parentchild as p JOIN stories2 as s
WHERE p.parent= $next AND s.sid= p.child
• php: $query=
"SELECT s.sid, s.title, s.author FROM parentchild as p JOIN stories2 as s ";
$query.=" WHERE p.parent=$next AND s.sid=p.child";
![Page 38: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/38.jpg)
Explanation
• Look at the parentchild table and pick up all the records with the parent field being a specified value. Call this group A.
• Look at stories2 table and pick up all records with sid being equal to a child value in group A.
• Think of this as a new table, consisting of portions of the old tabled joined together.
• for this new table, extract the sid, title and author values.– this will be used to create the form (radio buttons and
text next to the radio buttons)
![Page 39: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/39.jpg)
phpmyadmin
• Go to https://socialsoftware.purchase.edu/phpmyadmin– First logon: use your email id and password– Second logon: use what was sent to you in
the email
• NOTE: for our applications, we will be using php scripts, not phpmyadmin. BUT phpmyadmin may be useful for debugging.
![Page 40: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/40.jpg)
opendbo.php file<?php
global $DBname, $link;
$host = '127.0.0.1’;
$user=" ";
$password=" ";
$DBname=" ”;
$link = new mysqli($host,$user,$password);
if ($link->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
mysqli_select_db($link,$DBname);
?>
![Page 41: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/41.jpg)
Homework
• [Confirm that you can access phpmyadmin]• Use phpmyadmin to create table • Use code to create a table
– need to drop table to create a (new) table with the same name
• Write html and php to INSERT records• Write php to display whole table• Use code in charts and posted on-line sources!
– Post comments– Improve appearance– Extra credit: insert default values. Do validation.
• USE SOURCES!!!
![Page 42: Creating databases for web applications Database datatypes. Creating database Homework: Create tables in database. Add records to database](https://reader035.vdocuments.us/reader035/viewer/2022062308/56649ec05503460f94bcac7d/html5/thumbnails/42.jpg)
Preview• Will divide into teams, each teams taking one of the
examples and– study it & enhance it– present to the class– Bookmark project, with userid/password system:
http://faculty.purchase.edu/jeanine.meyer/db/bookmarkfiles.zip– Tell a story:
http://faculty.purchase.edu/jeanine.meyer/db/newstory.zip– Others:
http://faculty.purchase.edu/jeanine.meyer/db/examples.html
• THEN do an original project (can copy parts of sample projects)