Chapter 7 - 8

Working with Databases and SQL

Prepared by:

Ryan B. Azur
PHP – Working with Database and SQL
Key Skills and Concepts
Learn database concepts and Structured Query
Language
Add, edit, delete, and view records using MySQL and
MySQLi databases
Retrieve database records with PHP
Validate and save user input to a database with PHP
Write portable database-driven programs
Database Concepts
Introduction
PHP supports more than fifteen different database
engines, including Microsoft SQL Server, IBM DB2,
PostgreSQL, MySQL, and Oracle. Until PHP 5, this
support was offered through native database
extensions, each with its own functions and features;
however, this made it hard for developers to change
from one database engine to another. PHP 5 rectified
this situation by introducing a common API for
database access: the PHP Data Objects (PDO)
extension, which provides a unified interface to
working with databases and helps developers
manipulate different databases in a consistent manner.
Database Concepts
Brief History
Electronic databases were first used in the 1960s, a
little later than the first application of computers in
businesses.
In 1970, Dr. Edgar F. Codd published an academic paper
on relational databases. His article inspired the
creation of a language to handle database transactions
called Sequel or Structured English Query Language
The word “ English” was later dropped and the
language was standardized to SQL or Structured Query
Language.
SQL, is a way of giving the computer instructions on
how to handle large volumes of data.
Database Concepts
SQL
SQL is an abbreviation for Structured Query Language.
It is generally pronounced “Sequel”
SQL is a unified language for... defining, querying,
modifying, and controlling the data in a Relational
Database.
SQL is a standard, not a software product
Commercial institutions now lead the standard by
extending SQL to meet the needs of business.
The main commercial database management systems
(DBMS) in the industry today are: Oracle (MySQL),
Sybase, Microsoft SQL Server
Database Concepts
Relational Database
What is a Relational Database Management System
(RDBMS)?
All data is stored in Tables (i.e. Relations)
(grid-like format, similar to a spreadsheet)
The logical representation of data is separate from its
physical storage
One high-level language is provided …
for structuring, querying, and changing information.
This, of course, is SQL
Supports the concept of NULL values
Provides Mechanisms for Integrity, Recovery,
Authorization, and Transactions
Database Concepts
Introducing Databases and SQL
Database is also a collection of related facts and figures
regarding one subject matter.
Table is part of database that contains data about one
aspect of a subject in the database.
Record is an instance or a row of a table
Field is an attribute of the record or column of a table.
Database Concepts
Tables
They have Rows and Columns
(like a spreadsheets with rows & columns)
Database Concepts
Database
A Set of Related Tables is called a Database
Tables are separate, but equal in that...
They have no Hierarchical Ranking
They have inherent relationship to each other
You can create relationships
Database Concepts
Entity
An entity is a person, place, or thing for which you
wish to hold information
A table is a collection of separate occurrences of an
Entity
E.g. the “Employee” table contains information about
individual employees
Separate Characteristics are stored for each
Occurrence of an Entity (i.e a row)
E.g. An individual employee has a name, address,
phone number, etc.
Database Concepts
Sample Table

In the above table "Last Name" and "City" are the

columns
Each different person and their represent a row of
data
Database Concepts
Primary Key
Each Row is uniquely identified using the Primary
Key.
The Primary Key is defined as any Column (or
combination of columns) that can be used to
uniquely identify a particular row.
The Foreign Key is a field (or collection of fields) in
one table that uniquely identifies a row of another
table. In simpler words, the foreign key is defined in
a second table, but it refers to the primary key in
the first table.
Database Concepts
Data Manipulation Statements
The SELECT statement displays information you
want to see from the database
The INSERT statement allow you to add rows to the
database
The UPDATE statement allows you to change
existing column information
The DELETE statement deletes rows of data
Database Concepts
Example Primary Key

In the above example the Last Name column acts

as the PRIMARY key. (Note: names are not usually
a good choice, but this is a simple example)
Database Concepts
Values
A Value can be determined by the intersection of
the Column Name for the row identified by the
Primary Key.

Example: If I wanted to know where “Sison” lives I

need only tell SQL about the Primary Key value
“Sison” and the City column.
Database Concepts
Understanding SQL Statements
SQL statements can logically be broken in to three
high-level sets...
Data Definition Language (DDL) DDL consists of
statements that define the structure and relationships
of a database and its tables. Typically, these statements
are used to create, delete, and modify databases and
tables; specify field names and types; and set indexes.
Database Concepts
Understanding SQL Statements
Data Manipulation Language (DML) DML statements
are related to altering and extracting data from a
database. These statements are used to add records to,
and delete records from, a database; perform queries;
retrieve table records matching one or more user-
specified criteria; and join tables together using their
common fields.
Data Control Language (DCL) DCL statements are used
to define access levels and security privileges for a
database. You would use these statements to grant or
deny user privileges; assign roles; change passwords;
view permissions; and create rulesets to protect access
to data.
Database Concepts
Data Definition Statements

Common SQL Statements

Database Concepts
SQL Statements - modifiers
The NOT NULL modifier ensures that the field cannot accept a
NULL value after each field definition.
The PRIMARY KEY modifier marks the corresponding field as the
table’s primary key.
The AUTO_INCREMENT modifier, which is only available for
numeric fields, tells MySQL to automatically generate a value for
this field every time a new record is inserted into the table, by
incrementing the previous value by 1.
Database Concepts
MySQL Data Types
Database Concepts
SQL Statements – Creating and Populating Database
Note: Throughout the following exercise, boldface
type indicates commands that you should enter at
the MySQL command-line prompt.
Begin by starting up the MySQL command-line
client and connecting to the MySQL database with
your username and password:
If all went well, you’ll see a welcome message and
an interactive SQL prompt, like this:
mysql>
Database Concepts
Video- SQL Statements
Database Concepts
SQL Statements – Relational Operators
Relational Operators – are binary operators
primarily because these indicate the
relationship between operands.
Operators Description
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
= Equal to
<> Not equal to
Database Concepts
SQL Statements – Wildcard Operators
Wildcard Operators – In matching characters, wildcard
operators are provided by Microsoft Access. The word “Like” is
automatically added in the grid so the user need not type it.
Operators Description Example

* Matches Character in any me* finds meat, meet, and

number meeting
? Matches any letter m?ll finds mall and mill

# Matches any single-digit number 7#2 finds 712, 732, 772

[] Matches any of the characters t[io]e finds tie and toe but not
within the brackets true
! Matches any character not in t[!io] finds true but not to and
the list inside the brackets tie
- Matches one character in a d[a-d]d finds dad, dbd, dcd and
range of characters ddd
24
Database Concepts
SQL Statements – Logical Operators

Logical Operators - follow the rules of formal logic.

Operator Description

AND All specified criteria must be met

OR Either criterion must be met

NOT Indicated criteria should not be met

BETWEEN X AND Y The value must be from X and Y

IN The value must be in the list

Database Concepts
SQL Statements

SELECT [ DISTINCT | ALL {*|[column [as alias]] }

FROM {tablename1[table_alias][,….]}
[ WHERE where_expression]
[GROUP BY expression1, expression2, ….]
[HAVING having_expression ]
[ORDER BY order_column_expr1,
order_column_expr2…]

26
Database Concepts
SQL Statements
RESERVED WORDS DESCRIPTION

SELECT Indicates the field/s to be shown

AS Used for giving aliases or alternative names to columns

FROM Indicates the table/s that will be used in the query

WHERE Filters the records based on the specified query

GROUP BY Groups records with the same field value

HAVING Works like WHERE but applies only to groups

ORDER BY Indicates how the fields will be sorted

Reserved Words - A word in a programming language that has a fixed meaning and
cannot be redefined by the programmer.
Database Concepts
SQL Statements – Sample Database

Sample Database – Book Database