Transcript PowerPoint for this Session

SYST 28043
Web Technologies
Databases & MySQL
Database Overview
Database:
A collection of related Tables
A database server could house many
databases
Table:
A collection of records that describe
items in an entity
Some sytems call this a “file”
E.g. Employee Table contains
Employee Records
4/8/2016
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
Also called a column
Describes a piece of an item
4/8/2016
Wendi Jollymore, ACES
3
Database Overview
4/8/2016
Wendi Jollymore, ACES
4
Database Overview
4/8/2016
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
4/8/2016
Wendi Jollymore, ACES
6
Database Overview - Exercises
1.
2.
3.
Besides Students, what other
tables do you think would exist in
a database for Sheridan College?
Define a structure for a table
called Mp3s that you would use to
keep track of all your MP3 files.
Make up three examples of
records with field values for the
Mp3s table in #2.
4/8/2016
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!
4/8/2016
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
4/8/2016
Wendi Jollymore, ACES
9
Using MySQL
Click on the Databases tab and find
“Create Database”
Name: Media
Collation: latin1_general_ci (optional)
Click Create
Create the Table:
Name: Cds
Number of fields: 4
Click Go
4/8/2016
Wendi Jollymore, ACES
10
Using MySQL
Fill in the information for all four
fields
See notes online for field details
Add a description
Click Save
4/8/2016
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, Users tab
Click Add new user
Fill in the user name and password
Select Host: Local
For Global Privileges, check SELECT, INSERT,
UPDATE, DELETE
Click Add User button bottom-right
4/8/2016
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
4/8/2016
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
4/8/2016
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");
4/8/2016
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");
4/8/2016
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<br
/>".mysql_error());
4/8/2016
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 <body> tag:
Connect to the database
Select the Media table
4/8/2016
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"] : "";
4/8/2016
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
4/8/2016
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!
4/8/2016
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
4/8/2016
Wendi Jollymore, ACES
22
Displaying Records
A SELECT query will select a specific set
of records
SELECT fields FROM table WHERE condition
Fields = comma delimited list of fields
If you want all fields, use * instead
WHERE clause is optional
SELECT id, lastName, firstName FROM Students
SELECT * FROM Wine WHERE price > 50.0
SELECT * FROM Wine WHERE estate LIKE
‘%Jackson%’
4/8/2016
Wendi Jollymore, ACES
23
Displaying Records
Once you run a query, you’ll want
to get the results
Determine how many records are in
the result set
$rows = mysql_num_rows($result);
Access a row in the result set
$myRecord = mysql_fetch_array($result);
Access fields in a fetched row
echo “Name: “.$myRecord[“lastName”].”,
“.$myRecord[“firstName”];
4/8/2016
Wendi Jollymore, ACES
24
Displaying Records
Try the demos in the notes:
Display all records in a table
Display only records between a userspecified min and max price
Do the search exercise
4/8/2016
Wendi Jollymore, ACES
25