SYST 28043
Web Technologies
Databases & MySQL
Database Overview
Database:
A collection of related Tables
Table:
A collection of records that describe
items in an entity
Some sytems call this a “file”
E.g. Employee Table contains
Employee Records
1/15/2012 Wendi Jollymore, ACES 2
Database Overview
Record:
Describes a single item
Collection of Fields or Columns
Also called a Row
Field:
A single data element
Describes a piece of an item
1/15/2012 Wendi Jollymore, ACES 3
Database Overview
1/15/2012 Wendi Jollymore, ACES 4
Database Overview
1/15/2012 Wendi Jollymore, ACES 5
Database Overview
Primary Key
A special field that acts as a unique
identifier for a record
Must be unique for each record
Each table must have one
Examples:
Student ID, Social Insurance #, Product ID
1/15/2012 Wendi Jollymore, ACES 6
Database Overview - Exercises
1. Besides Students, what other
tables do you think would exist in
a database for Sheridan College?
2. Define a structure for a table
called Mp3s that you would use to
keep track of all your MP3 files.
3. Make up three examples of
records with field values for the
Mp3s table in #2.
1/15/2012 Wendi Jollymore, ACES 7
Using MySQL
In order to create database-driven
pages, you need a database server
We will use MySQL
You will need to go in and create
databases and tables that your
pages can use
We’ll use the PHPMyAdmin tool!
1/15/2012 Wendi Jollymore, ACES 8
Using MySQL
Go to http://localhost
Log in using the user name and
password you set up when you
installed XAMPP
In the left-hand menu, select
phpMyAdmin
Log in using the root password you
set up for your Sql server when you
installed XAMPP
1/15/2012 Wendi Jollymore, ACES 9
Using MySQL
On the main page, find “Create
Database”
Name: Media
Collation: latin1_general_ci
Click Create
Create the Table:
Name: Cds
Number of fields: 4
Click Go
1/15/2012 Wendi Jollymore, ACES 10
Using MySQL
Fill in the information for all four
fields
See notes online for field details
Add a description
Set Collation to latin1_general_ci
Click Save
1/15/2012 Wendi Jollymore, ACES 11
Using MySQL
We can’t use the root account for web
pages
Too powerful for a regular site visitor
Add a Guest user:
On main admin page, click Privleges
Click Add new user
Fill in the user name and password
Select Host: Local
For Global Privleges, check SELECT, INSERT,
UPDATE, DELETE
Click Go
1/15/2012 Wendi Jollymore, ACES 12
Accessing the DB
Create the form in the notes
Use this to get user data to store
PHP file will be used to retrieve the
data and save it to the database table
Open a new PHP file
Add variables for user name, host,
password
Use the guest name and password you
created
1/15/2012 Wendi Jollymore, ACES 13
Accessing the DB
To perform any task with your table
data:
Connect to the database server
Select the database you want to work
with
Perform the commands you want
1/15/2012 Wendi Jollymore, ACES 14
Accessing the DB
Connecting to the server:
mysql_connect(host, user, passwd)
Connects to a database server with a
specific user name and password
Returns a reference to the database
connection object
$dblink = mysql_connect($hostname, $user, $passwd)
or die ("Error: No connection to MySQL
server\n");
1/15/2012 Wendi Jollymore, ACES 15
Accessing the DB
Selecting the database:
mysql_select_db(db, conn)
Selects a specific database using a
connection that has already been
created
mysql_select_db($dbname, $dblink)
or die ("Error: MySQL database not selected\n");
1/15/2012 Wendi Jollymore, ACES 16
Accessing the DB
Executing an SQL statement:
mysql_query(cmd, conn)
Executes a specific command or query
using a database connection
If cmd is a SELECT statement, function
returns a set of records
If cmd is INSERT, DELETE, UPDATE,
$result true if successful, false if not
$result = mysql_query($sql, $dblink)
or die ("SQL query failed: $sql".mysql_error());
1/15/2012 Wendi Jollymore, ACES 17
Accessing the DB
In your PHP file:
At the top, add variables for host,
user, password
Normally these are stored somewhere
else
In the tag:
Connect to the database
Select the Media table
1/15/2012 Wendi Jollymore, ACES 18
Retrieving Form Data
You sent your form data using
method=“post”
To access in PHP file:
$_POST[“fieldname”]
Fieldname is the value in the input
element’s name=“” attribute
isset($_POST[“fieldname”]) function
Returns true if the field has a value
$cdTitle = (isset($_POST["title"])) ?
$_POST["title"] : "";
1/15/2012 Wendi Jollymore, ACES 19
Inserting Records
The SQL INSERT statement allows
you to add records:
INSERT INTO tableName (f1, f2, f3,
...) VALUES (v1, v2, v3…);
Inserts the values v1 into field f1,
the value v2 into field f2, etc…
You can build an SQL statement
using the form data
1/15/2012 Wendi Jollymore, ACES 20
Inserting Records
Add the code to
build the SQL INSERT query
Execute the query
If the result returned is greater than 0,
display a confirmation message
If the result is 0 or less, display an
error message
Check the notes!
1/15/2012 Wendi Jollymore, ACES 21
Inserting Records
Use phpMyAdmin to check your
table and see if the record was
added!
Check the notes online for complete
code solution
1/15/2012 Wendi Jollymore, ACES 22