Pro Yearly is on sale from $80 to $50! »

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

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

This talk will cover the basics necessary to help you decide what data to store, where, and how.

We will cover PDO — PHP’s Data Object extension, which allows you to talk to a variety of databases, including MySQL.

You will learn how to CRUD — Create, Retrieve, Update and Delete data, database schema, and when to use indexes.

This talk assumes zero knowledge of databases and SQL, and will take you from zero to JOINs and Foreign Key constraints in no time with MySQL.

Fee39f0c0ffb29d9ac21607ed188be6b?s=128

Davey Shafik

May 20, 2014
Tweet

Transcript

  1. Getting Started with Data Databases

  2. 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
  3. What is a Database?

  4. 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)
  5. Types of Database

  6. SQL (Relational)

  7. 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)
  8. NoSQL (Document/Key-Value/Graph)

  9. 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)
  10. Agenda Document Stores / Key-Value Stores Non-Relational

  11. 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)
  12. Agenda Graph Databases Relational

  13. 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)
  14. Relational Concepts

  15. Proprietary and Confidential • Schema! • Tables! • Indexes! •

    Relationships! • Stored Procedures! • Triggers Relational Concepts
  16. A Note about MySQL

  17. 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
  18. Data Types

  19. 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
  20. Exercise One Create a Users Table

  21. 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:
  22. Proprietary and Confidential Exercise One: Users Table Users id int

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

    username varchar(20) password varchar(60) email varchar(150) first_name varchar(45) last_name varchar(55) CREATE TABLE ( , , , , , );
  24. 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) );
  25. SQL Structured Query Language

  26. Proprietary and Confidential • INSERT — Create Data! • UPDATE

    — Update Existing Data! • SELECT — Fetch Data! • DELETE — Delete Data SQL Four Main Queries
  27. CRUD Also Known As:

  28. Proprietary and Confidential CRUD C reate INSERT R etrieve SELECT

    U pdate UPDATE D elete DELETE
  29. Conditions

  30. Proprietary and Confidential • Used with:! • SELECT • UPDATE

    • DELETE • JOINs! • Preceded by the WHERE, ON, HAVING, or USING keyword Conditions
  31. 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
  32. INSERT

  33. Proprietary and Confidential INSERT INSERT INTO table name ( list,

    of, columns ) VALUES ( "list", "of", "values" );
  34. 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", "davey@engineyard.com", "Davey", "Shafik" );
  35. UPDATE

  36. Proprietary and Confidential UPDATE UPDATE table name SET column =

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

    "some", name = "value" WHERE some condition;
  38. Proprietary and Confidential Don’t forget your conditions!! Otherwise you update

    every row in the table! WARNING:
  39. Proprietary and Confidential UPDATE UPDATE users SET username = "davey",

    email = "davey@engineyard.com" WHERE id = 1;
  40. SELECT

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

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

    WHERE column = "some" AND name = "value" OR other_column = "other value"
  43. 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
  44. 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;
  45. Proprietary and Confidential SELECT SELECT! !*! FROM! !users! WHERE! !username!=!"davey"!

    !AND!password!=!"$2y$10$Ol..."! LIMIT!1;
  46. Proprietary and Confidential SELECT SELECT first_name, last_name, email FROM users

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

    ORDER BY first_name, last_name LIMIT 10, 20;
  48. DELETE

  49. Proprietary and Confidential DELETE DELETE FROM table

  50. Proprietary and Confidential DELETE DELETE FROM table WHERE column =

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

    "some" AND name = "value" OR other_column = "other value" ORDER BY some ASC, columns DESC
  52. 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;
  53. Proprietary and Confidential DELETE DELETE FROM users;

  54. None
  55. Proprietary and Confidential DELETE DELETE FROM users WHERE id =

    1;
  56. Constraints

  57. 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
  58. 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 first_name varchar(45) not null last_name varchar(55) not null
  59. 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) );
  60. Features

  61. 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)
  62. Proprietary and Confidential • ID should be auto increment! •

    ID should be the Primary Key! • ID should be unsigned Features: Users Table
  63. 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) );
  64. Indexes

  65. 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
  66. INDEX, UNIQUE, & PRIMARY KEY

  67. 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
  68. Foreign Keys

  69. 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
  70. Exercise Two Profiles & Foreign Keys

  71. 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
  72. Proprietary and Confidential Exercise Two: Profiles Table Profiles 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
  73. 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 );
  74. Proprietary and Confidential INSERT INSERT INTO profiles ( users_id, summary,

    bio, location ) VALUES ( 1, "Community Engineer at Engine Yard", NULL, "Florida, USA" );
  75. Indexing Your Data

  76. None
  77. 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
  78. Exercise Three Indexes

  79. 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
  80. 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...";
  81. 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..."; SELECT!*!FROM!users!! WHERE!email!=!"davey@engineyard.com";
  82. 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..."; SELECT!*!FROM!users!! WHERE!email!=!"davey@engineyard.com"; SELECT!*!FROM!users!
 !!WHERE!first_name!LIKE!"%Dave%";
  83. Proprietary and Confidential Exercise Three: Indexes Users Key Type id

    PRIMARY KEY username UNIQUE username, password UNIQUE email UNIQUE
  84. JOINs Connecting Tables

  85. Proprietary and Confidential • Used to JOIN multiple tables! •

    INNER JOIN! • LEFT or RIGHT OUTER JOIN JOINs
  86. Proprietary and Confidential Get the intersection of two tables INNER

    JOIN Users Profiles Users with Profiles
  87. OUTER JOIN For when one side or the other doesn’t

    match
  88. Proprietary and Confidential LEFT OUTER JOIN For when one side

    or the other doesn’t match Users Users with Profiles Profiles
  89. Proprietary and Confidential RIGHT OUTER JOIN Users Profiles with Users

    Profiles
  90. 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;
  91. Proprietary and Confidential SELECT... LEFT OUTER JOIN SELECT * FROM

    users LEFT OUTER JOIN profiles ON ( profiles.user_id = users.id ) WHERE users.id = 1;
  92. 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%';
  93. Databases and PHP

  94. Connecting to Databases Using PDO

  95. Proprietary and Confidential • PDO! – MySQL! – PostgreSQL! –

    MSSQL! – Oracle! – SQLite! – ODBC and DB2! – Firebird! • DSN — Data Source Name! – Driver Name! – Hostname & Port! Connecting to Databases
  96. Proprietary and Confidential Connecting to MySQL <?php $pdo = new

    PDO( "mysql:dbname=db;host=localhost", "user", "pass" ); ?>
  97. Querying Data

  98. Proprietary and Confidential Executing Queries: Simplest <?php $pdo = new

    PDO(...); $affected = $pdo->exec( "DELETE FROM user" ); ?>
  99. Proprietary and Confidential Executing Queries: Simpler <?php $pdo = new

    PDO(...); $result = $pdo->query( "SELECT * FROM user" ); foreach ($result as $user) { echo '<a href="/user/edit/', $user['id'], '">'; echo $user['first_name'], ' ', $user[‘last_name’]; echo '</a>'; } ?>
  100. Proprietary and Confidential Executing Queries: Prepared Statements <?php $pdo =

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

    new PDO(...); $query = $pdo->prepare( "SELECT * FROM user WHERE id = :id" ); $conditions = array( ':id' => 1 ); $result = $query->execute($conditions); ?>
  102. Handling Results

  103. Proprietary and Confidential Handling Results <?php $result = $query->execute($conditions); if

    ($result) {
 !!echo!"Results!Found:!"!.$query9>rowCount(); while ($row = $query->fetch()) { echo "<a href='/edit/" .$row['id']. "'>" .$row['first_name']. ' ' . $row['last_name'] .'</a>'; } } ?>
  104. Proprietary and Confidential Handling Results as Objects <?php $result =

    $query->execute($conditions); if ($result) { echo!"Results!Found:!"!.$query9>rowCount(); while ($row = $query->fetchObject()) { echo "<a href='/edit/" .$row->id. "'>" .$row->first_name. ' ' . $row->last_name .'</a>'; } } ?>
  105. 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 "<a href='/edit/" .$row->id. "'>" .$row->getName(). "</a>"; } }
  106. 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 "<a href='/edit/" .$row->id. "'>" .$row->getName(). "</a>"; } }
  107. Proprietary and Confidential Fetch Column foreach ($query->fetchColumn('first_name') as $row) {

    echo "<a href='/edit/" .$row->id. "'>" .$row(). "</a>"; } }
  108. Object-Data Mapper — ODM

  109. Proprietary and Confidential Object Data Mapper — ODM namespace App;

    class Users { protected $db; public function __construct(\PDO $db) { $this->db = $db; }
  110. 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; }
  111. 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; }
  112. 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; } }
  113. 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; } }
  114. 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); }
  115. 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]); }
  116. Agenda mysqlnd_ms Read/Write Splitting

  117. Proprietary and Confidential $ pecl install mysqlnd_ms
 
 
 mysqlnd_ms.enable=1


    mysqlnd_ms.config_file=
 /path/to/mysqlnd_ms.json Read/Write Splitting
  118. 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
  119. 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
  120. Proprietary and Confidential Feedback & Questions: ! Feedback: https://joind.in/
 Twitter:

    @dshafik Email: davey@engineyard.com Slides: http://daveyshafik.com/slides 10667