Upgrade to Pro — share decks privately, control downloads, hide ads and more …

[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.

Davey Shafik

May 20, 2014
Tweet

More Decks by Davey Shafik

Other Decks in Programming

Transcript

  1. Getting Started with Data
    Databases

    View Slide

  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

    View Slide

  3. What is a Database?

    View Slide

  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)

    View Slide

  5. Types of Database

    View Slide

  6. SQL (Relational)

    View Slide

  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)

    View Slide

  8. NoSQL
    (Document/Key-Value/Graph)

    View Slide

  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)

    View Slide

  10. Agenda
    Document Stores / Key-Value Stores
    Non-Relational

    View Slide

  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)

    View Slide

  12. Agenda
    Graph Databases
    Relational

    View Slide

  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)

    View Slide

  14. Relational Concepts

    View Slide

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

    View Slide

  16. A Note about MySQL

    View Slide

  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

    View Slide

  18. Data Types

    View Slide

  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

    View Slide

  20. Exercise One
    Create a Users Table

    View Slide

  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:

    View Slide

  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)

    View Slide

  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
    (
    ,
    ,
    ,
    ,
    ,
    );

    View Slide

  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)
    );

    View Slide

  25. SQL
    Structured Query Language

    View Slide

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

    View Slide

  27. CRUD
    Also Known As:

    View Slide

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

    View Slide

  29. Conditions

    View Slide

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

    View Slide

  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

    View Slide

  32. INSERT

    View Slide

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

    View Slide

  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",
    "[email protected]",
    "Davey",
    "Shafik"
    );

    View Slide

  35. UPDATE

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  40. SELECT

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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;

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  48. DELETE

    View Slide

  49. Proprietary and Confidential
    DELETE
    DELETE
    FROM
    table

    View Slide

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

    View Slide

  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

    View Slide

  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;

    View Slide

  53. Proprietary and Confidential
    DELETE
    DELETE FROM users;

    View Slide

  54. View Slide

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

    View Slide

  56. Constraints

    View Slide

  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

    View Slide

  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

    View Slide

  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)
    );

    View Slide

  60. Features

    View Slide

  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)

    View Slide

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

    View Slide

  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)
    );

    View Slide

  64. Indexes

    View Slide

  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

    View Slide

  66. INDEX, UNIQUE, & PRIMARY KEY

    View Slide

  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

    View Slide

  68. Foreign Keys

    View Slide

  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

    View Slide

  70. Exercise Two
    Profiles & Foreign Keys

    View Slide

  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

    View Slide

  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

    View Slide

  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
    );

    View Slide

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

    View Slide

  75. Indexing Your Data

    View Slide

  76. View Slide

  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

    View Slide

  78. Exercise Three
    Indexes

    View Slide

  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

    View Slide

  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...";

    View Slide

  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!=!"[email protected]";

    View Slide

  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!=!"[email protected]";
    SELECT!*!FROM!users!

    !!WHERE!first_name!LIKE!"%Dave%";

    View Slide

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

    View Slide

  84. JOINs
    Connecting Tables

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  89. Proprietary and Confidential
    RIGHT OUTER JOIN
    Users
    Profiles with Users
    Profiles

    View Slide

  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;

    View Slide

  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;

    View Slide

  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%';

    View Slide

  93. Databases and PHP

    View Slide

  94. Connecting to Databases
    Using PDO

    View Slide

  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

    View Slide

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

    View Slide

  97. Querying Data

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  102. Handling Results

    View Slide

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

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

    View Slide

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

    View Slide

  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 ""
    .$row->getName().
    "";
    }
    }

    View Slide

  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 ""
    .$row->getName().
    "";
    }
    }

    View Slide

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

    View Slide

  108. Object-Data Mapper — ODM

    View Slide

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

    View Slide

  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;
    }

    View Slide

  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;
    }

    View Slide

  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;
    }
    }

    View Slide

  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;
    }
    }

    View Slide

  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);
    }

    View Slide

  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]);
    }

    View Slide

  116. Agenda
    mysqlnd_ms
    Read/Write Splitting

    View Slide

  117. Proprietary and Confidential
    $ pecl install mysqlnd_ms



    mysqlnd_ms.enable=1

    mysqlnd_ms.config_file=

    /path/to/mysqlnd_ms.json
    Read/Write Splitting

    View Slide

  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

    View Slide

  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

    View Slide

  120. Proprietary and Confidential
    Feedback & Questions: !
    Feedback: https://joind.in/

    Twitter: @dshafik
    Email: [email protected]
    Slides: http://daveyshafik.com/slides
    10667

    View Slide