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

Slide 1

Slide 1 text

Getting Started with Data Databases

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

What is a Database?

Slide 4

Slide 4 text

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)

Slide 5

Slide 5 text

Types of Database

Slide 6

Slide 6 text

SQL (Relational)

Slide 7

Slide 7 text

Proprietary and Confidential • 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)

Slide 8

Slide 8 text

NoSQL (Document/Key-Value/Graph)

Slide 9

Slide 9 text

Proprietary and Confidential • 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)

Slide 10

Slide 10 text

Agenda Document Stores / Key-Value Stores Non-Relational

Slide 11

Slide 11 text

Proprietary and Confidential • 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 NoSQL (Document/Key-Value/Graph)

Slide 12

Slide 12 text

Agenda Graph Databases Relational

Slide 13

Slide 13 text

Proprietary and Confidential • 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 NoSQL (Document/Key-Value/Graph)

Slide 14

Slide 14 text

Relational Concepts

Slide 15

Slide 15 text

Proprietary and Confidential • Schema! • Tables! • Indexes! • Relationships! • Stored Procedures! • Triggers Relational Concepts

Slide 16

Slide 16 text

A Note about MySQL

Slide 17

Slide 17 text

Proprietary and Confidential • 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

Slide 18

Slide 18 text

Data Types

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

Exercise One Create a Users Table

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

Proprietary and Confidential Exercise One: Users Table Users id int username varchar(20) password varchar(60) email varchar(150) ﬁrst_name varchar(45) last_name varchar(55)

Slide 23

Slide 23 text

Proprietary and Confidential Exercise One: Users Table Users id int username varchar(20) password varchar(60) email varchar(150) ﬁrst_name varchar(45) last_name varchar(55) CREATE TABLE ( , , , , , );

Slide 24

Slide 24 text

Proprietary and Confidential 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) );

Slide 25

Slide 25 text

SQL Structured Query Language

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

CRUD Also Known As:

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

Conditions

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

Proprietary and Confidential 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

Slide 32

Slide 32 text

INSERT

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

Proprietary and Confidential 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" );

Slide 35

Slide 35 text

UPDATE

Slide 36

Slide 36 text

Proprietary and Confidential UPDATE UPDATE table name SET column = "some", name = "value"

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

SELECT

Slide 41

Slide 41 text

Proprietary and Confidential SELECT SELECT list, of, columns FROM table

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

Proprietary and Confidential SELECT SELECT list, of, columns FROM table WHERE column = "some" AND name = "value" OR other_column = "other value" ORDER BY some ASC, columns DESC

Slide 44

Slide 44 text

Proprietary and Confidential SELECT SELECT list, of, columns FROM table WHERE column = "some" AND name = "value" OR other_column = "other value" ORDER BY some ASC, columns DESC LIMIT start, end;

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

DELETE

Slide 49

Slide 49 text

Proprietary and Confidential DELETE DELETE FROM table

Slide 50

Slide 50 text

Proprietary and Confidential DELETE DELETE FROM table WHERE column = "some" AND name = "value" OR other_column = "other value"

Slide 51

Slide 51 text

Proprietary and Confidential DELETE DELETE FROM table WHERE column = "some" AND name = "value" OR other_column = "other value" ORDER BY some ASC, columns DESC

Slide 52

Slide 52 text

Proprietary and Confidential DELETE DELETE FROM table WHERE column = "some" AND name = "value" OR other_column = "other value" ORDER BY some ASC, columns DESC LIMIT number;

Slide 53

Slide 53 text

Proprietary and Confidential DELETE DELETE FROM users;

Slide 54

Slide 54 text

No content

Slide 55

Slide 55 text

Proprietary and Confidential DELETE DELETE FROM users WHERE id = 1;

Slide 56

Slide 56 text

Constraints

Slide 57

Slide 57 text

Proprietary and Confidential • 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

Slide 58

Slide 58 text

Proprietary and Confidential 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

Slide 59

Slide 59 text

Proprietary and Confidential Constraints: Users Table Schema CREATE TABLE users ( 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) );

Slide 60

Slide 60 text

Features

Slide 61

Slide 61 text

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)

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

Proprietary and Confidential Features: Users Table Schema CREATE TABLE users ( 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) );

Slide 64

Slide 64 text

Indexes

Slide 65

Slide 65 text

Proprietary and Confidential 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

Slide 66

Slide 66 text

INDEX, UNIQUE, & PRIMARY KEY

Slide 67

Slide 67 text

Proprietary and Confidential • 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

Slide 68

Slide 68 text

Foreign Keys

Slide 69

Slide 69 text

Proprietary and Confidential • 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

Slide 70

Slide 70 text

Exercise Two Profiles & Foreign Keys

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

Proprietary and Confidential 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

Slide 73

Slide 73 text

Proprietary and Confidential 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 );

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

Indexing Your Data

Slide 76

Slide 76 text

No content

Slide 77

Slide 77 text

Proprietary and Confidential • 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

Slide 78

Slide 78 text

Exercise Three Indexes

Slide 79

Slide 79 text

Proprietary and Confidential • 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

Slide 80

Slide 80 text

Proprietary and Confidential • 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 SELECT2*2FROM! !users! WHERE! !username!=!"davey"  !AND!password!=!"$2y$10$Ol...";

Slide 81

Slide 81 text

Slide 82

Slide 82 text

Slide 83

Slide 83 text

Proprietary and Confidential Exercise Three: Indexes Users Key Type id PRIMARY KEY username UNIQUE username, password UNIQUE email UNIQUE

Slide 84

Slide 84 text

JOINs Connecting Tables

Slide 85

Slide 85 text

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

Slide 86

Slide 86 text

Proprietary and Confidential Get the intersection of two tables INNER JOIN Users Profiles Users with Profiles

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

Proprietary and Confidential RIGHT OUTER JOIN Users Profiles with Users Profiles

Slide 90

Slide 90 text

Proprietary and Confidential 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;

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

Databases and PHP

Slide 94

Slide 94 text

Connecting to Databases Using PDO

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

Proprietary and Confidential Connecting to MySQL

Slide 97

Slide 97 text

Querying Data

Slide 98

Slide 98 text

Proprietary and Confidential Executing Queries: Simplest exec( "DELETE FROM user" ); ?>

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

Handling Results

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

Proprietary and Confidential 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(). ""; } }

Slide 106

Slide 106 text

Proprietary and Confidential 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(). ""; } }

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

Object-Data Mapper — ODM

Slide 109

Slide 109 text

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

Slide 110

Slide 110 text

Proprietary and Confidential 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; }

Slide 111

Slide 111 text

Proprietary and Confidential Object Data Mapper — ODM 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; }

Slide 112

Slide 112 text

Proprietary and Confidential Object Data Mapper — ODM 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; } }

Slide 113

Slide 113 text

Proprietary and Confidential Object Data Mapper — ODM class Users_Record { protected $db; public function __construct(\PDO $db, $data = []) { $this->db = $db; foreach ($data as $key => $value) { $this->{$key} = $value; } }

Slide 114

Slide 114 text

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

Slide 115

Slide 115 text

Proprietary and Confidential Object Data Mapper — ODM public function delete() { $sql = "DELETE FROM users WHERE id = :id"; $query = $this->db->prepare($sql); return $query->execute([':id' => $id]); }

Slide 116

Slide 116 text

Agenda mysqlnd_ms Read/Write Splitting

Slide 117

Slide 117 text

Proprietary and Confidential $ pecl install mysqlnd_ms      mysqlnd_ms.enable=1  mysqlnd_ms.config_file=  /path/to/mysqlnd_ms.json Read/Write Splitting

Slide 118

Slide 118 text

Proprietary and Confidential { "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "192.168.2.27", "port": "3306" } } } Read/Write Splitting

Slide 119

Slide 119 text

Proprietary and Confidential •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 Read/Write Splitting

Slide 120

Slide 120 text

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