Transcript Datatypes. Creating tables. Show bookmark sites application.

Creating databases for web
applications
Database datatypes. Creating database
Homework: Create tables in database. Add
records to database.
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.
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
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
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
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
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
MySQL datatypes: date/time
•
•
•
•
•
DATE
TIME
DATETIME
YEAR
TIMESTAMP
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.
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
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 password
THEN 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!
Create table example
• CREATE TABLE movies (
mid INT NOT NULL AUTO_INCREMENT
PRIMARY KEY,
mname CHAR(30),
mdesc TEXT,
myear YEAR
)
Create table example
CREATE TABLE players (
pid INT NOT NULL AUTO_INCREMENT
PRIMARY KEY,
pname CHAR(30),
score INT NOT NULL,
lastplayed DATE
)
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.
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
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
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.
2 table example
Bookmarks application: finders and sites
• http://socialsoftware.purchase.edu/jeanine.meye
r/research/register.html
• http://socialsoftware.purchase.edu/jeanine.meye
r/research/addsite.html
• http://socialsoftware.purchase.edu/jeanine.meye
r/research/showsites.php
• http://socialsoftware.purchase.edu/jeanine.meye
r/research/showsitesbycategory1.php
Create the tables
<?php
function 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");
}
}
?>
<html><head><title>Creating bookmark tables
</title> </head>
<body>
<?php
require("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);
$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>
adding a site
Use two scripts (files)
addsite.html
addsite.php
Make input fields new types.
This should/will provide some checking, aka
form validation.
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>
Simplified addsite.php
<html> <head> <title>Add song to database</title> </head> <body>
<?php
require("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>
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
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.
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.
showsites.php
<html> <head> <title>List contents of sites table</title> </head> <body>
<?php
require("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>
show by category
• Two php files, one calling the other as the
action of a form
• Form has select
showsitesbycategory1.php
showsitesbycategory2.php
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">
<?php
require("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>
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);
$NoR=mysql_num_rows($result);
if ($NoR==0) {
print ("No sites in that category"); } //should not happen
else {
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);
?>
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.
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 …
Entity Relationship Diagram
0
stories2
sid
title
scene
author
permflag
0
parentchild
pcid
parent
child
0
starts
stid
first
each parent, child, first is
exactly 1 record in stories2
A record in stories2 may
not be represented as a
parent or child or first
Comment
• Could this be done other ways? Yes.
– My first attempt used one table with prev and
next fields
Example of SQL SELECT
The 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";
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)
phpmyadmin
• Go to
https://socialsoftware.purchase.edu/phpmy
admin
– 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.
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);
?>
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!!!
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)