Simple Example of a Database We

1
Simple Example of a Database Were going to
build a simple example of a database, which will
allow us to register users and allow them to log
into do this, were going to1- design a
database that fits our needs2- create templates
that allow us to view and enter information3-
enter records into the database4- write scripts
that allow us to a- view the information b-
enter the information
2
ProjectTo create our database and the tables for
this project, we need to understand what our
project will be.Project DescriptionCreate a
system that allows 1- new users to
register 2- existing users to login 3-
existing users to change their passwords 4-
existing users to view all users
3
DESIGNING THE SITE(from a programming point of
view)
4
Site MapA Site Map is an overall look at the
pages links within a site from a users point of
view. It indicates how users can get from point A
to point B.
5
What the site will look like image here
6
Site FlowThe Site Flow is an overall look at the
pages links within a site from a programming
point of view. It indicates how the program flows
from point A to point B.
7
(No Transcript)
8
Site IAThe Information Architecture
describes 1- the elements that make up a page
and 2- how they function
9
Site IA
10
Site IA
11
Site IAREGISTER has 2 statesFormConfirm
12
Site IA
13
Site IA
14
Site IALOGIN has 2 statesFormConfirm
15
Site IA
16
Site IA
17
Site IACHANGE PASSWORD has 2
statesFormConfirm
18
Site IA
19
Site IA
20
Site IAVIEW USERS has only one state
21
Site IA
22
Directory StructureThe directory structure
outlines where files will be stored.
23
CREATING THE TEMPLATE
24
Creating the template
25
Creating the templatelt?php index.php//HEADER
// Set the page title and include the HTML
header.page_title 'Welcome!'include
('./templates/header.php')/// MAIN
BODYprint("lttable width'90' border'0'
cellspacing'4' cellpadding'4' align'center'gt
lttrgt lttd width'70' valign'top'gt
ltpgtltbgtContent....lt/bgtlt/pgt lt/tdgt
lt/trgtlt/tablegt")//FOOTERinclude
('./templates/footer.php') // Include the HTML
footer.?gt
26
Creating the templatePHP has 4 functions for
using external filesinclude()include_once()req
uire()require_onceTo use them, your script
needs a line likeinclude (filename.php')What
s the difference?If an include file is not found
it will display an error, but other scripts will
runif a require file is not found, it will
display and error and the other scripts will NOT
runinclude_once() and require_once will be
called only once, no matter how many times your
scripts refer to them
27
Creating the header filelt!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http//www.w3.org/TR/2000/REC-xhtml1-20000126/DTD
/xhtml1-transitional.dtd"gtlthtml
xmlns"http//www.w3.org/1999/xhtml"
xmllang"en" lang"en"gtltheadgt ltmeta
http-equiv"content-type" content"text/html
charsetiso-8859-1" /gt lttitlegtlt?php echo
page_title ?gtlt/titlegtlt/headgtltbody
bgcolor"FFFFFF"gtlttable width"100" border"0"
cellspacing"0" cellpadding"4"gt lttrgt lttd
width"100" bgcolor"666666"gtltfont
color"CCCCCC"gtltbiggtltbgtWelcome to my
site!lt/bgtlt/biggtlt/fontgtlt/tdgt lt/trgt lttrgt
lttd bgcolor"CCCCCC"gt lttable width"100"
border"0" cellspacing"1" cellpadding"2"gt
lttrgt lttd align"center"gtlta
href"index.php"gtHomelt/agtlt/tdgt lttd
align"center"gtlta href"register.php"gtRegisterlt/agt
lt/tdgt lt/trgt lt/tablegtlt/tdgt
lt/trgtlt/tablegtltbr /gtlt!-- Script 3.2 header.inc
--gtlt!-- PAGE SPECIFIC CONTENT STARTS HERE. --gt
28
Creating the footer filelt!-- Script 3.3
footer.inc --gtltbr /gt lttable width"100"
border"0" cellspacing"1" cellpadding"2"gt
lttrgt lttd align"center"gtlta
href"index.php"gtHomelt/agtlt/tdgt lttd
align"center"gtlta href"register.php"gtRegisterlt/agt
lt/tdgt lt/trgt lt/tablegt lttable
width"100" border"0" cellspacing"0"
cellpadding"2" bgcolor"CCCCCC"gt lttrgt
lttdgt ltdiv align"center"gtcopy 2003 Larry E.
Ullman and DMC Insights, Inc.lt/divgtlt/tdgt
lt/trgtlt/tablegtlt/bodygtlt/htmlgt
29
WRITING THE SCRIPTS
30
Writing the scriptsPreciously we used 2 scripts
to handle our HTML forms. One to display the
formOne to process itUsing some of the logic
we learned last week, were going to combine both
of these processes into one form.lt?If(/form
has been submitted/) // handle
itelse //process it ?gt
31
Writing the scripts
32
Writing the scripts
Display the header
Check if form has been submitted
If YES, do error checking
Does information pass error checking?
If YES, register user and print confirm message
If NO, display error message and form
If NO, display form
Display the footer
User defined functions
33
Writing the scriptslt?php Script 3.11 -
register.php// Set the page title and include
the HTML header.page_title 'Register!'includ
e ('./templates/header.php')// initialize
error message to nothingerror_message""if
(isset(_POST'submit')) // Handle the
form. // ERROR CHECKING // Check for a
name. if (strlen(_POST'user_name') gt 0)
else error_message. "ltpgtYou forgot to enter
your username!lt/pgt" // Do I PASS ERROR
CHECKING? if (error_message"" ) // If
everything's okay. // Register the user. //
DATABSE FUNCTIONALITY WILL GO HERE //print
verification/confirm message else //
There's an error. //print error message
print_registration_form() else //
Display the form. print_registration_form()
// End of the main SUBMIT conditional.include
('./templates/footer.php') // Include the HTML
footer.function print_registration_form()?
gt
Display the header
Check if form has been submitted
If YES, do error checking
Does information pass error checking?
If YES, register user and print confirm message
If NO, display error message and form
If NO, display form
Display the footer
User defined functions
34
DESIGNING THE DATABASE TABLE
35
What the table will look like This table is
relatively simple.1- We have one main type of
thing to worry about users, so were going to
create one table2- From the IA we know there
are 7 things we need to have for each user
First Name Last Name E-mail User
Name Password User ID (gives us a way to
uniquely id each user) Registration Date
36
What the table will look like To fully
construct our table we need to define the data
type required by each column.My SQL has a
number of data types, but they break down into a
few basic categoriesBOOLEAN NUMBERS Integer,
Float, Decimal etcDATE Datetime, Timestamp,
etcTEXT Char, Varchar, Text, Blob
37
Data Types have to be correct otherwise you
will be trying to put a square peg in a round
hole. Once in a while it will work, but often it
will be impossible.
38
These are further broken down, usually by the how
much information each variable can storeBOOLEAN
- no sub categories NUMBERSTINYINT - A very
small integer. The signed range is -128 to 127.
The unsigned range is 0 to 255SMALLINT - A
small integer. The signed range is -32768 to
32767. The unsigned range is 0 to 65535.
MEDIUMINT - A medium-size integer. The signed
range is -8388608 to 8388607. The unsigned range
is 0 to 16777215.INT- A normal-size integer.
The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295. BIGINT -
A large integer. The signed range is
-9223372036854775808 to 9223372036854775807. The
unsigned range is 0 to 18446744073709551615.
39
What the table will look like NUMBERS
(contd)FLOAT- A small (single-precision)
floating-point number. Allowable values are
-3.402823466E38 to -1.175494351E-38, 0, and
1.175494351E-38 to 3.402823466E38. DOUBLE - A
normal-size (double-precision) floating-point
number. Allowable values are -1.7976931348623157E
308 to -2.2250738585072014E-308, 0, and
2.2250738585072014E-308 to 1.7976931348623157E308
. DECIMAL- An unpacked floating-point number.
Behaves like a CHAR column unpacked'' means
the number is stored as a string, using one
character for each digit of the value.
40
What the table will look like TEXT VARCHAR - A
variable-length string. The range is 0 to 255
characters (1 to 255 prior to MySQL Version
4.0.2). TINYBLOB TINYTEXT - A BLOB or TEXT
column with a maximum length of 255 characters.
BLOB TEXT A BLOB or TEXT column with a
maximum length of 65535 characters. MEDIUMBLOB
MEDIUMTEXT A BLOB or TEXT column with a maximum
length of 16777215 (224 - 1) characters.
LONGBLOB LONGTEXT A BLOB or TEXT column with
a maximum length of 4294967295 or 4G (232 - 1)
characters.
41
What Data Type to use for each column? This is
always a tricky question. For our example lets
go back to the project IA and some common
sense.First Name - Since the IA says that this
has a max number of 30 characters and its a text
string, Im going to make it a VARCHAR with a
max of 30 characters Last Name- Since the IA
says that this has a max number of 30 characters
and its a text string, Im going to make it a
VARCHAR with a max of 30 charactersE-mail Name-
Since the IA says that this has a max number of
255 characters and its a text string, Im going
to make it a VARCHAR with a max of 255
characters User Name- Since the IA says that
this has a max number of 10 characters and its a
text string, Im going to make it a VARCHAR with
a max of 10 characters
42
What Data Type to use for each column? Password
- Since the IA says that this has a max number of
10 characters and its a text string, Im going
to make it a VARCHAR with a max of 10 characters
User ID This is going to be a unique number
for each user. I cant imagine Im going to have
more than 16777215 users, so Im going to make
it a MEDIUM INT unsignedRegistration Date I
want to get both the date and time a user
registered, so Im going to make it a DATETIME
type
43
What Data Type to use for each column? This is
the DATASPEC to design our table
Also PRIMARY KEY
44
CREATING THE TABLE
45
Creating a Database Were going to use
phpMyAdmin to create our database.
46
Installing phpMyAdmin1- download
phpMyAdmin http//www.phpmyadmin.net/home_page/
2- unzip the file3- rename the folder
phpMyAdmin4- changes to config.inc.php
47
Installing phpMyAdminconfig.inc.php
The directory for phpMyAdmin on your site For
example http//denning.net/phpMyAdmin
The user name password from Kunal
48
Installing phpMyAdmin5- upload the folder
phpMyAdmin6- go to the full url you used for
example http//denning.net/phpMyAdmin
49
Using phpMyAdmin creating a database
50
Using phpMyAdmincreating a table

Name is arbitrary, but it should make some
sense Number of fields we know this from our
dataspec
51
Using phpMyAdmin naming columns and assigning
datatypes

Field Name should come from our dataspec Type
we know this from our dataspec Length/Values
we know this from our dataspec Extra we
know this from our dataspec Key we know this
from our dataspec
52
Using phpMyAdmin naming columns and assigning
datatypes

53
Using phpMyAdmin

54
Using phpMyAdmin entering records
Click this to enter a record
Leave this blank were using Auto Increment

55
Using phpMyAdminviewing records
Click this to view records