[php[tek] 2014] Introduction To Databases (Part 1: MySQL & PDO)

Getting Started with Data
Databases

View Slide

Proprietary and Confidential
•Community Engineer at
Engine Yard
•Author of Zend PHP 5
Certification Study Guide,
Sitepoints PHP Anthology:
101 Essential Tips, Tricks &
Hacks & PHP Master: Write
Cutting Edge Code
•A contributor to Zend
Framework 1 & 2, phpdoc,
and PHP internals
•@dshafik
Davey Shafik

View Slide

What is a Database?

View Slide

A database is an organized collection of data.
The data is typically organized to model
relevant aspects of reality, in a way that
supports processes requiring this information.
“
”
Source: Wikipedia!
(Emphasis Mine)

View Slide

Types of Database

View Slide

SQL (Relational)

View Slide

• Highly Structured Data!
• Using Tables, Columns and Rows!
• One or more relationships exist between datas!
• Constraints!
– Primary Keys (a unique row identifier)!
– Unique Keys (one or more columns that must have unique values,
either individually, or as a group)!
– Foreign Keys (a column value that must be derived from a column
value in another table)!
• Indexes!
SQL (Relational)

View Slide

NoSQL
(Document/Key-Value/Graph)

View Slide

• Sometimes called “Not Only SQL” because some NoSQL DBs have a
SQL-like query language!
• Not always non-relational!
• Always unstructured!
• Intended to provide higher scalability and higher availability!
• Looser consistency models
NoSQL (Document/Key-Value/Graph)

View Slide

Agenda
Document Stores / Key-Value Stores
Non-Relational

View Slide

• NoSQL is non-relational!
• Document Stores!
• Centers around the concept of a document, and it’s related meta-
data!
• Collections of documents!
• Hierarchies of documents!
• Examples: Couchbase Server, CouchDB, MongoDB, Amazon
SimpleDB, Oracle NoSQL DB!
• Key-Value Stores!
• Data stored and accessible directly by a unique key!
• Examples: Memcache, MongoDB, Couchbase Server, Cassandra,
Riak, Amazon DynamoDB, Redis, Oracle NoSQL DB

View Slide

Agenda
Graph Databases
Relational

View Slide

• NoSQL is relational (say what?!)!
• Graph Databases!
• All data is related to N other data!
• Relationships are in the data, not indexes!
• Examples: OQGraph for MySQL!
• Example Implementation: Facebook’s Graph API

View Slide

Relational Concepts

View Slide

• Schema!
• Tables!
• Indexes!
• Relationships!
• Stored Procedures!
• Triggers
Relational Concepts

View Slide

A Note about MySQL

View Slide

• MySQL supports multiple drivers (called engines) for it’s tables.!
• These engine provide different features.!
• The two most common are InnoDB (default since MySQL 5.5) and
MyISAM (previously the default).!
• InnoDB has far more features, and is recommended for almost all
situations!
• We will assume InnoDB for all MySQL examples
A Note on MySQL

View Slide

Data Types

View Slide

Name What Notes
int exact whole numbers Signed or unsigned
decimal exact decimal numbers (fixed length) Signed or unsigned
float
approximate decimal number (variable
length)
Signed or unsigned
char strings (fixed length) Max size: 255 bytes
varchar strings (variable length) Max size: 255 bytes
text strings (variable length) Max size: 255 bytes - 4GB
blob binary strings (variable length) Max size: 255 Bytes - 4GB
date dates (no time) Any date is valid
datetime dates (with time) Any date/time is valid
timestamp timestamp
UNIX timestamp, must be >
1/1/1970
NULL Null values
Does not equal anything, even
NULL

View Slide

Exercise One
Create a Users Table

View Slide

• Unique Identifier!
• Username!
• Password!
• Email Address!
• Name or First Name/Last Name!
!
!
!
• Column Names!
• Column Types!
• Column Lengths
Exercise One: Users Table
Consider:

View Slide

Users
id int
username varchar(20)
password varchar(60)
email varchar(150)
ﬁrst_name varchar(45)
last_name varchar(55)

View Slide

Users
id int
username varchar(20)
password varchar(60)
email varchar(150)
ﬁrst_name varchar(45)
last_name varchar(55)
CREATE TABLE
(
,
,
,
,
,
);

View Slide

Exercise One: Users Table (Schema)
CREATE TABLE users (
id INT,
username VARCHAR(20),
password VARCHAR(60),
email VARCHAR(150),
first_name VARCHAR(45),
last_name VARCHAR(55)
);

View Slide

SQL
Structured Query Language

View Slide

• INSERT — Create Data!
• UPDATE — Update Existing Data!
• SELECT — Fetch Data!
• DELETE — Delete Data
SQL
Four Main Queries

View Slide

CRUD
Also Known As:

View Slide

CRUD
C reate INSERT
R etrieve SELECT
U pdate UPDATE
D elete DELETE

View Slide

Conditions

View Slide

• Used with:!
• SELECT
• UPDATE
• DELETE
• JOINs!
• Preceded by the WHERE, ON, HAVING, or USING keyword
Conditions

View Slide

Operators
Operator
= Equality
<>, != Inequality
< Less Than
<= Less Than or Equal To
> Greater Than
>= Greater Than or Equal To
IS NULL NULL Equality
IS NOT NULL NULL Inequality
AND Boolean AND
OR Boolean OR
BETWEEN Range Equality

View Slide

INSERT

View Slide

INSERT
INSERT INTO table name (
list,
of,
columns
) VALUES (
"list",
"of",
"values"
);

View Slide

INSERT
INSERT INTO users (
id,
username,
password,
email,
first_name,
last_name
) VALUES (
1,
"dshafik",
"$2y$10$Ol/KS4/Bhs5ENUh7OpIDL.Gs1SIWDG.rPaBkPAjjQ2UTITI60YDmG",
"[email protected]",
"Davey",
"Shafik"
);

View Slide

UPDATE

View Slide

UPDATE
UPDATE
table name
SET
column = "some",
name = "value"

View Slide

UPDATE
UPDATE
table name
SET
column = "some",
name = "value"
WHERE
some condition;

View Slide

Don’t forget your conditions!!
Otherwise you update every row in the table!
WARNING:

View Slide

UPDATE
UPDATE
users
SET
username = "davey",
email = "[email protected]"
WHERE
id = 1;

View Slide

SELECT

View Slide

SELECT
SELECT
list,
of,
columns
FROM
table

View Slide

SELECT
SELECT
list,
of,
columns
FROM
table
WHERE
column = "some"
AND name = "value"
OR other_column = "other value"

View Slide

SELECT
SELECT
list,
of,
columns
FROM
table
WHERE
column = "some"
AND name = "value"
ORDER BY some ASC, columns DESC

View Slide

SELECT
SELECT
list,
of,
columns
FROM
table
WHERE
column = "some"
AND name = "value"
LIMIT start, end;

View Slide

SELECT
SELECT!
!*!
FROM!
!users!
WHERE!
!username!=!"davey"!
!AND!password!=!"$2y$10$Ol..."!
LIMIT!1;

View Slide

SELECT
SELECT
first_name, last_name, email
FROM
users
ORDER BY first_name, last_name
LIMIT 0, 10;

View Slide

SELECT
SELECT
first_name, last_name, email
FROM
users
ORDER BY first_name, last_name
LIMIT 10, 20;

View Slide

DELETE

View Slide

DELETE
DELETE
FROM
table

View Slide

DELETE
DELETE
FROM
table
WHERE
column = "some"
AND name = "value"

View Slide

DELETE
DELETE
FROM
table
WHERE
column = "some"
AND name = "value"
LIMIT number;

View Slide

DELETE
DELETE FROM users;

View Slide

View Slide

DELETE
DELETE FROM
users
WHERE
id = 1;

View Slide

Constraints

View Slide

• IDs should be unique!
• Usernames should be unique!
• Passwords should not be unique!
• Email Address should be unique!
• First Name should not be unique!
• Last Name should not be unique!
!
• All column should not be NULL
Constraints: Users Table

View Slide

Constraints: Users Table
Users Constraints
id int unique, not null
username varchar(20) unique, not null
password varchar(60) not null
email varchar(150) unique, not null
ﬁrst_name varchar(45) not null
last_name varchar(55) not null

View Slide

Constraints: Users Table Schema
id INT NOT NULL,
username VARCHAR(20) NOT NULL,
password VARCHAR(60) NOT NULL,
email VARCHAR(150) NOT NULL,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(55) NOT NULL,
UNIQUE INDEX id_UNIQUE (id),
UNIQUE INDEX username_UNIQUE (username),
UNIQUE INDEX email_UNIQUE (email)
);

View Slide

Features

View Slide

Name What Notes
Auto Increment
(auto_increment)
Automatically inserts
the (last row)+1 value
when inserting
• Column must be set
as PRIMARY KEY!
• One Per Table
Signed/Unsigned
Sets Numeric columns
to signed (may be
positive or negative) or
unsigned (must be
positive)
Unsigned numbers start
at 0 and allow for much
larger numbers.
Zero Fill (zerofill)
Left Pads numeric
values to the column
size
Only applied on retrieval
(i.e. it’s not stored this
way)

View Slide

• ID should be auto increment!
• ID should be the Primary Key!
• ID should be unsigned
Features: Users Table

View Slide

Features: Users Table Schema
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(60) NOT NULL,
email VARCHAR(150) NOT NULL,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(55) NOT NULL,
UNIQUE INDEX username_UNIQUE (username),
UNIQUE INDEX email_UNIQUE (email),
PRIMARY KEY (id)
);

View Slide

Indexes

View Slide

Indexes
Name Constraints Notes
Index None May have NULL values
Unique Unique May have NULL values
Primary Key Unique
Must NOT have NULL values. May
auto_increment. There can only be one.
Foreign Key
Must match data in
linked table
May have NULL values

View Slide

INDEX, UNIQUE, & PRIMARY KEY

View Slide

• Can be added during table creation:
• CREATE TABLE foo ( 
column_name TYPE, 
INDEX name (column, list), 
UNIQUE name (column, list), 
PRIMARY KEY (column) 
);
• Index/Unique can have multiple columns
• Can be added after table creation:
• CREATE INDEX name ON table_name (column, list); 
ALTER TABLE table name ADD INDEX (column, list); 
CREATE UNIQUE INDEX name ON table (column, list); 
ALTER TABLE table name ADD UNIQUE (column, list); 
ALTER TABLE table name ADD PRIMARY KEY (column);
• Must be added with caution!
INDEX, UNIQUE, & PRIMARY KEY

View Slide

Foreign Keys

View Slide

• Used to create inter-table relationships!
• Value must be in the foreign table or NULL!
• Can update when the foreign table updates!
• Can delete when the foreign table deletes!
• Can be set to NULL when the foreign table deletes
Foreign Keys

View Slide

Exercise Two
Profiles & Foreign Keys

View Slide

• Unique Identifier!
• Short Introductory Summary!
• Full Biography!
• Location
Exercise Two: Profiles Table
Consider:
• Must link to the Users table!
• One Profile per User

View Slide

Exercise Two: Profiles Table
Proﬁles Constraints
id int UNSIGNED primary key, auto_increment, not null
users_id int UNSIGNED unique, foreign key -> users.id, not null
summary varchar(200) none
bio TEXT none
location varchar(100) none

View Slide

Exercise Two: Profiles Table Schema
CREATE TABLE profiles (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
users_id INT UNSIGNED NOT NULL,
summary VARCHAR(200) NOT NULL,
bio TEXT NULL,
location VARCHAR(150) NULL,
PRIMARY KEY (id),
UNIQUE INDEX users_id_UNIQUE (users_id),
CONSTRAINT
FOREIGN KEY (users_id) REFERENCES users (id)
ON DELETE CASCADE
);

View Slide

INSERT
INSERT INTO profiles (
users_id,
summary,
bio,
location
) VALUES (
1,
"Community Engineer at Engine Yard",
NULL,
"Florida, USA"
);

View Slide

Indexing Your Data

View Slide

View Slide

• Indexes make writes slower, and reads (much faster). The more indexes,
the slower your writes.!
• The creation of indexes should be determined by the SELECT queries
being run upon the data. Nothing else.!
• For example, if you run a query that SELECTs using two WHERE
criteria with an AND condition, that is probably a good combination
index.!
• MySQL can only use one index [per table] at a time and will (generally)
pick the best option based on the query!
• Indexes cannot be used with LIKE if starting with a wildcard (e.g. %foo%)
Indexing Your Data

View Slide

Exercise Three
Indexes

View Slide

• Add Indexes to the users table!
• Remember we already have a PRIMARY KEY and UNIQUE indexes!
• Example queries we will perform against it:
Exercise Three: Indexes

View Slide

SELECT2*2FROM!
!users!
WHERE!
!username!=!"davey" 
!AND!password!=!"$2y$10$Ol...";

View Slide

SELECT2*2FROM!
!users!
WHERE!
SELECT!*!FROM!users!!
WHERE!email!=!"[email protected]";

View Slide

SELECT2*2FROM!
!users!
WHERE!
SELECT!*!FROM!users!!
WHERE!email!=!"[email protected]";
SELECT!*!FROM!users! 
!!WHERE!first_name!LIKE!"%Dave%";

View Slide

Users
Key Type
id PRIMARY KEY
username UNIQUE
username, password UNIQUE
email UNIQUE

View Slide

JOINs
Connecting Tables

View Slide

• Used to JOIN multiple tables!
• INNER JOIN!
• LEFT or RIGHT OUTER JOIN
JOINs

View Slide

Get the intersection of two tables
INNER JOIN
Users Profiles
Users with Profiles

View Slide

OUTER JOIN
For when one side or the other doesn’t match

View Slide

LEFT OUTER JOIN
For when one side or the other doesn’t match
Users
Users with Profiles
Profiles

View Slide

RIGHT OUTER JOIN
Users
Profiles with Users
Profiles

View Slide

SELECT... INNER JOIN
SELECT * FROM
users
INNER JOIN profiles
ON (
profiles.user_id = users.id
)
WHERE  
profiles.location LIKE '%Chicago%'
ORDER BY  
users.first_name,
users.last_name;

View Slide

SELECT... LEFT OUTER JOIN
SELECT * FROM
users
LEFT OUTER JOIN profiles
ON (
)
WHERE
users.id = 1;

View Slide

SELECT... RIGHT OUTER JOIN
SELECT * FROM
users 
LEFT OUTER JOIN profiles
ON (
)
RIGHT OUTER JOIN posts
ON (
posts.user_id = users.id
)
WHERE
posts.content LIKE '%PHP%';

View Slide

Databases and PHP

View Slide

Connecting to Databases
Using PDO

View Slide

• PDO!
– MySQL!
– PostgreSQL!
– MSSQL!
– Oracle!
– SQLite!
– ODBC and DB2!
– Firebird!
• DSN — Data Source Name!
– Driver Name!
– Hostname & Port!
Connecting to Databases

View Slide

Connecting to MySQL
$pdo = new PDO(
"mysql:dbname=db;host=localhost",
"user",
"pass"
);
?>

View Slide

Querying Data

View Slide

Executing Queries: Simplest
$pdo = new PDO(...);
$affected = $pdo->exec(
"DELETE FROM user"
);
?>

View Slide

Executing Queries: Simpler
$result = $pdo->query(
"SELECT * FROM user"
);
foreach ($result as $user) {
echo '';
echo $user['first_name'], ' ', $user[‘last_name’];
echo '';
}
?>

View Slide

Executing Queries: Prepared Statements
$query = $pdo->prepare(
"SELECT * FROM user WHERE id = ?"
);
$conditions = array(1);
$result = $query->execute($conditions);
?>

View Slide

Executing Queries: Prepared Statements
$query = $pdo->prepare(
"SELECT * FROM user WHERE id = :id"
);
$conditions = array(
':id' => 1
);
?>

View Slide

Handling Results

View Slide

Handling Results
if ($result) { 
!!echo!"Results!Found:!"!.$query9>rowCount();
while ($row = $query->fetch()) {
echo ""
.$row['first_name']. ' '
. $row['last_name'] .'';
}
}
?>

View Slide

Handling Results as Objects
if ($result) {
echo!"Results!Found:!"!.$query9>rowCount();
while ($row = $query->fetchObject()) {
echo ""
.$row->first_name. ' '
. $row->last_name .'';
}
}
?>

View Slide

Handling Results as Custom Objects
class User {
function getName() {
return $this->first_name
. ' ' . $this->last_name;
}
}
if ($result) {
echo!"Results!Found:!"!.$query9>rowCount();
while ($row = $query->fetchObject("User")) {
echo ""
.$row->getName().
"";
}
}

View Slide

Fetch All
class User {
function getName() {
return $this->first_name
. ' ' . $this->last_name;
}
}
if ($result) {
echo "Results Found: " .$query->rowCount();
$results = $query->fetchAll(PDO::FETCH_CLASS, 'User');
foreach ($results as $row) {
echo ""
.$row->getName().
"";
}
}

View Slide

Fetch Column
foreach ($query->fetchColumn('first_name')
as $row) {
echo ""
.$row().
"";
}
}

View Slide

Object-Data Mapper — ODM

View Slide

Object Data Mapper — ODM
namespace App;
class Users {
protected $db;
public function __construct(\PDO $db)
{
$this->db = $db;
}

View Slide

Object Data Mapper — ORM
public function findById($id)
{
$sql = "SELECT * FROM users WHERE id = :id";
$query = $this->db->prepare($sql);
$query->setFetchMode(\PDO::FETCH_CLASS, 'Users_Record', [$this->db]);
$result = $query->execute([':id' => $id]);
if (!$result || $query->rowCount() == 0) {
return false;
}
$row = $query->fetch();
return $row;
}

View Slide

public function find($where = []) {
$sql = 'SELECT * FROM users';
if ($where) {
$sql .= " WHERE ";
}
$conditions = [];
foreach ($where as $column => $value) {
$conditions[] = "$column = :$column";
$data[":$column"] = $value;
}
$sql .= implode(" AND ", $conditions);
$query = $this->prepare($sql);
$query->setFetchMode(\PDO::FETCH_CLASS, 'Users_Record', [$this->db]);
$result = $query->execute($data);
if (!$result || $query->rowCount() == 1) {
return false;
}
return $query;
}

View Slide

public function create($data = [])
{
return new Users_Record($this->db, $data);
}
public function delete($id)
{
$obj = static::findById($id);
if ($obj) {
return $obj->delete();
} else {
return false;
}
}

View Slide

class Users_Record {
protected $db;
public function __construct(\PDO $db, $data = [])
{
$this->db = $db;
foreach ($data as $key => $value) {
$this->{$key} = $value;
}
}

View Slide

public function save()
{
$data = [];
if (isset($this->id)) {
foreach ($this as $column => $value) {
$update[] = "$column = :$column";
}
$sql = "UPDATE users SET ";
$sql .= implode(", ", $update);
} else {
foreach ($this as $column => $value) {
}
$sql = "INSERT INTO users (";
$sql .= implode(",", array_keys((array) $this));
$sql .= ") VALUES (";
$sql .= implode(",", array_keys((array) $data));
$sql .= ")";
}
return $query->execute($data);
}

View Slide

public function delete()
{
$sql = "DELETE FROM users WHERE id = :id";
return $query->execute([':id' => $id]);
}

View Slide

Agenda
mysqlnd_ms
Read/Write Splitting

View Slide

$ pecl install mysqlnd_ms 
 
 
mysqlnd_ms.enable=1 
mysqlnd_ms.config_file= 
/path/to/mysqlnd_ms.json

View Slide

{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "192.168.2.27",
"port": "3306"
}
}
}

View Slide

•SELECT * FROM foo; -- Use Slave!
•/* MYSQLND_MS_SLAVE_SWITCH */SELECT * FROM foo; -- Use Slave!
•SELECT * FROM foo; INSERT INTO bar VALUES ('baz'); -- Use Slave!!
•/* MYSQLND_MS_MASTER_SWITCH */SELECT * FROM foo; -- Use
Master!
•/* MYSQLND_MS_LAST_USED_SWITCH */SELECT * FROM foo; -- Use
whatever !
• -- was last used

View Slide

Feedback & Questions: !
Feedback: https://joind.in/ 
Twitter: @dshafik
Email: [email protected]
Slides: http://daveyshafik.com/slides
10667

View Slide

[php[tek] 2014] Introduction To Databases (Part 1: MySQL & PDO)

[php[tek] 2014] Introduction To Databases (Part 1: MySQL & PDO)

Davey Shafik

More Decks by Davey Shafik

Other Decks in Programming

Featured

Transcript