ICS 325
Internet Application Development
Class Notes – Chapter 7, 8, 9 & 10 – Web Databases using MySQL
Chapter 7, “Designing Your Web Database,” reviews
basic database concepts
Chapter 8, “Creating Your Web Database,” reviews SQL
Syntax to create databases and tables
Chapter 9, “Working with Your MySQL Database,”
reviews SQL commands to use the database
Chapter 10, “Accessing MySQL with PHP,” reviews
specific functionality for MySQL database connectivity
Database
Management Systems
A database management system (DBMS) is
a software application that implements a database.
A relational database management
system (RDBMS) is a collection of related tables. (Relations)
A RDBMS can provide faster and access
to data than flat text files
A RDBMS can quickly access subsets of
data based on user input
A RDBMS can be accessed by multiple
users concurrently
A RDBMS has built in mechanisms for
maintaining data integrity
A RDBMS can provide random access to
your data
Basic
Database Concepts
Table A relation; a data structure which looks similar to a spreadsheet)
Column A field or an attribute
Row A record or tuple
Values The actual data
Key An
attribute (or set of attributes) which uniquely identifies a tuple
RDBMS Design Issues
Schemas A method of representing
your database via a model, or blueprint; a complete set of table designs
A schema includes
all tables, their columns, data types of the columns, indicates the primary
key,
indicates
any foreign keys
Normalization A method of optimizing tables in a
database
Relationships Relationships between two or more tables are
normally done through foreign keys
Avoid
redundancy Avoid having multiple
copies of data located in multiple data – wastes space, leads to update
anomalies
Primary
Key A unique identifier for
a table
Foreign
Key A identifier that links
back to another table’s primary key.
Specifically, an attribute in one relation is
called a foreign
key if it appears as a primary key in another relation.
Empty
Attribute A data field that has
Null Data
Real
world objects A similarity between the
data you are storing and a natural object.
Questions What to you want to ask the
database?
MySQL
MySQL
is a RDBMS
Logging
into MySQL
Ø
mysql –h hostname –u username –p
Ø
Enter Password:
Creating Databases
Ø
create database databaseName;
Users and Privileges
Privilege Right to perform a
particular action on a particular object; associated with a particular user
Grant
Command Grants privileges to
specified users
Revoke
Command Revokes privileges to specified
users
Usage
Command Used in conjunction with grant
and revoke to assign usability rights to users
All
Command Used in conjunction with grant and
revoke to assign all rights to users
Other
Privileges select, insert, update, delete, index, alter, create, and drop
Selecting the Database
Ø
use databaseName;
Creating Database Tables
Syntax CREATE TABLE tableName (columns)
Key
words NOT NULL Data in the column cannot not be empty
AUTO_INCREMENT Data will increment by one, from the
highest value, if the field is left blank
PRIMARY
KEY The unique
identifier for a column of data
UNSIGNED Integer values can
have only zero or positive values
Data
types See Tables 8.5 – 8.11
Special Database Syntax
SHOW
DATABASES; This will show all
databases in MySQL
SHOW
TABLES; This will show
all tables in the current database
DESCRIBE
tableName; This will show the
properties of the table
SQL Structured Query Language
SQL is the most standard language for
access data in a RDBMS.
SQL has a set of key words that can be
used to create complex queries.
SQL implements both DDL and DML.
DDL – Data Definition Language used to
create databases and tables
DML – Data Manipulation Languages used
to query the database
Queries can obtain subsets of data
from a database table.
Results from a query are called a
record set, or result set.
Retrieving Data from a Table
Syntax SELECT columnName(s)
FROM tableName
WHERE condition
GROUP BY columnName(s)
HAVING where definition(s)
ORDER BY columnName(s)
The SELECT
statement is used to select data from a database table
The * is used to
select all fields (columns) in the database table.
The FROM
statement is used to select the database tables that will be accessed.
The WHERE statement
is used to select a subset (rows) of the data from the table.
Possible
conditions are <,>,<=,>=,=,<>, IS NULL, IS NOT NULL, BETWEEN,
IN , NOT IN, LIKE, AND NOT LIKE
SELECT * FROM
table1 WHERE field1 LIKE ‘%omp*’;
SELECT * FROM
table1 WHERE field1 BETWEEN (1 AND 10);
The GROUP BY
statement is used to group like data sets together.
The HAVING
statement is another way to select subsets of the data
The ORDER BY
statement is used to put the record set into some kind of order.
Aggregate Functions
See Table 9.3 for Aggregate Functions
available in MySQL
Retrieving Data from Multiple Tables
Joins
are a way to select a subset of data from multiple tables
Syntax
SELECT * FROM tableName1, tableName2 WHERE tableName1.columnName = tableName2.columnName;
Inserting Data into a Table
Syntax INSERT INTO tableName (columnName1, columnName2, …) VALUES (value1, value2, …)
The INSERT INTO
statement is used to insert records into a database table.
The VALUES
statement is used to specify the data that will be inserted into corresponding
fields.
columnName1 would
get value1
Updating a Record in a Table
Syntax Update tableName SET columnName=value WHERE condition
The UPDATE
statement is used to update records in the database table
Deleting Records from a Table
Syntax DELETE FROM tableName WHERE condition
The DELETE
statement is used to remove records from the database table
Altering a Table after Creation
Syntax ALTER TABLE tableName changes
The ALTER statement is used to alter
the table properties after the table is created. If you alter a table that has data stored
inside it, the result could be a loss of wanted data.
See Table 9.4 for possible changes
with the alter command.
Dropping Tables and Databases
Dropping
table and databases should be avoided in this class.
Syntax Drop tableName
Drop databaseName
Once
the database or table is gone it is gone!
Web
Database Architecture
A six step process
Web User Web Server
Database
Web User
Web Server 
Web Users makes a request to the Web
Server through Web Browser
The web server sends PHP requests to
PHP engine
PHP engine sends database request to
database driver
Database results sent back to PHP
engine
PHP formats results and sends HTML to
Web Server
Web Server sends HTML to Web User’s
Browser
Setting
the Database Connection
In order to connect to any database, a
connection string must be established
PHP has built in functions for
connecting to MySQL
int mysql_connect(address,
username, password);
address = the ip address
of the server that has the MySQL service running, localhost if MySQL is on Web Server.
username
= the user that you want connected to the database
password
= the password for the above username
returns
a connection id as an integer
Use
the @ to suppress errors
Example:
$dbConNum = @mysql_connect("localhost", "su04325??", "yourpassword");if (!$dbConNum) { echo( "<p>Unable to connect to the database server at this time.</p>" ); exit();}
Using the
Correct Database
After connecting to the database server, a database
selection needs to be made
PHP has built in functions for
selecting the database in MySQL
mysql_select_db(dbName
[, connectionID]);
dbName = the database in MySQL you
wish to connect to
connectionID = an optional
variable that specifies which connection to use; if blank, the last connection
id is used.
Use
the @ to suppress errors
Example:
if (! @ mysql_select_db("su04325??", $dbConNum)) { echo( "<p>Unable to locate the su04325?? database at this time.</p>" ); exit();}
Querying
the Database
Once the database has been connected
and selected, you will probably want to ask it questions.
Questions to a database are done
through queries.
PHP has built in functions querying
the database in MySQL
mysql_query(SQLquery
[, connectionID]);
SQLquery
= An SQL statement in the form of a string
connectionID
= an optional variable that specifies which connection to use; if blank, the
last connection id is used.
Returns a
Boolean value or an array
Use
the @ to suppress errors
Example:
$sqlQuery = "SELECT
* FROM myDatabase";
if(! @mysql_query($sqlQuery, $dbConNum)){
echo( "<p>Unable to locate the query database at this time.</p>" ); exit(); }Getting
Data from the Database Queries
For most queries the mysql_query() function
will return true or false, successful or failed.
The SELECT statement is different
because it returns records in the form of an array.
PHP has built in functions capturing records
returned from a query on the database in MySQL
mysql_fetch_Array(queryResult);
queryResult
= the result of running a select query using mysql_query
Example:
$sqlQuery = "SELECT
* FROM myDatabase";
$result = @mysql_query($sqlQuery, $dbConNum)
if(!$result){
echo( "<p>Unable to locate the query database at this time.</p>" ); exit();}
while ( $row = mysql_fetch_array($result) ) { echo("<p>" . $row["dbCol1"] . "</p>"); } Closing the
database Connection
To free memory, all database
connections should be closed when not being used.
PHP has built in functions closing
database connections in MySQL
mysql_close(connectionID);
connectionID = a variable that
specifies the connection to close
Other
Useful MySQL Database Functions
There are many MySQL function
available in PHP. See php.net for more…
mysql_affected_rows() Get
number of affected rows in previous MySQL operation (update, insert)
mysql_error() Returns
the text of the error message from previous MySQL operation
mysql_fetch_array() Fetch a result row as an associative
array
mysql_fetch_row() Fetch
a result row as an enumerated array
mysql_info() Get information about the most recent query
mysql_num_rows() Get
number of rows in result (select)
Manipulating the Database from a Text File
A series of SQL commands can be
executed repeatedly by placing them in a file.
Using pico, type in SQL commands.
To execute the SQL commands, type this
statement at the Linux prompt
mysql
–u su04325?? –D su04325?? –p < yourtextfile.sql