[YEGPHP] Introduction to Databases

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.

Transcript

1. Getting Started with Data Introduction to 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, & PHP internals • Original creator of PHAR/ PHP_Archive • Lead for PHPWomen US •@dshafik Davey Shafik
3. Let’s start a conversation about mental health in tech prompt.engineyard.com
4. About These Slides
5. Proprietary and Confidential Two slides per “slide” Title Slide (for when I’m talking) Details slide (for later) Nobody likes it when you can read the slide just as well as the speaker can I like slides that are useful About These Slides
6. What is a Database?
7. Proprietary and Confidential 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)
8. Types of Database
9. SQL (Relational)
10. 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 –A lookup for one, or multiple columns aggregate data SQL (Relational)
11. NoSQL (Document/Key-Value/Graph)
12. 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)
13. Non-Relational Document Stores / Key-Value Stores
14. 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 NoSQL (Document/Key-Value/Graph)
15. Relational Graph Databases
16. 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: Neo4J, OQGraph for MySQL » Example Implementation: Facebook’s Graph API NoSQL (Document/Key-Value/Graph)
17. A Note about MySQL
18. 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
19. Relational Concepts
20. Proprietary and Confidential • Schema – Tables – Indexes – Relationships • Stored Procedures • Triggers Relational Concepts
21. Data Types
22. Proprietary and Confidential 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
23. Create a Users Table
24. Proprietary and Confidential • Unique Identifier • Username • Password • Email Address • Name or First Name/Last Name • Column Names • Column Types • Column Lengths Users Table Consider:
25. Proprietary and Confidential Users Table Users id int username varchar(20) password varchar(60) email varchar(150) first_name varchar(45) last_name varchar(55)
26. Proprietary and Confidential Users Table Users id int username varchar(20) password varchar(60) email varchar(150) first_name varchar(45) last_name varchar(55) CREATE TABLE ( , , , , , );
27. Proprietary and Confidential CREATE TABLE users ( id INT, username VARCHAR(20), password VARCHAR(60), email VARCHAR(150), first_name VARCHAR(45), last_name VARCHAR(55) ); Users Table (Schema)
28. SQL Structured Query Language
29. Proprietary and Confidential • INSERT — Create Data • UPDATE — Update Existing Data • SELECT — Fetch Data • DELETE — Delete Data SQL: Four Main Queries
30. CRUD Also Known As:
31. Proprietary and Confidential CRUD Create INSERT Retrieve SELECT Update UPDATE Delete DELETE
32. Conditions
33. Proprietary and Confidential • Used with: –SELECT –UDPATE –DELETE –JOINs • Preceded by the WHERE, ON, USING, or HAVING keyword Conditions
34. 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
35. INSERT
36. Proprietary and Confidential INSERT INTO table name ( list, of, columns ) VALUES ( "list", "of", "values" ); INSERT
37. Proprietary and Confidential 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" ); INSERT
38. UPDATE
39. Proprietary and Confidential UPDATE table name SET column = "some", name = "value" UPDATE
40. Proprietary and Confidential UPDATE table name SET column = "some", name = "value" UPDATE WHERE some condition;
41. Proprietary and Confidential WARNING: Don’t forget your conditions! Otherwise you update every row in the table!
42. Proprietary and Confidential UPDATE users SET username = "davey", email = "davey@engineyard.com" WHERE id = 1; UPDATE
43. SELECT
44. Proprietary and Confidential SELECT SELECT list, of, columns FROM table
45. Proprietary and Confidential SELECT SELECT list, of, columns FROM table WHERE column = "some" AND name = "value" OR other_column = "other value"
46. 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
47. 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, offset;
48. Proprietary and Confidential SELECT * FROM users WHERE username = "davey" AND password = "$2y$10$Ol..." LIMIT 1; SELECT
49. Proprietary and Confidential SELECT first_name, last_name, email FROM users ORDER BY first_name, last_name LIMIT 0, 10; SELECT
50. Proprietary and Confidential SELECT first_name, last_name, email FROM users ORDER BY first_name, last_name LIMIT 10, 10; SELECT
51. DELETE
52. Proprietary and Confidential DELETE FROM table DELETE DELETE FROM table
53. Proprietary and Confidential DELETE FROM table DELETE DELETE FROM table WHERE column = "some" AND name = "value" OR other_column = "other value"
54. Proprietary and Confidential DELETE FROM table DELETE DELETE FROM table WHERE column = "some" AND name = "value" OR other_column = "other value" ORDER BY some ASC, columns DESC
55. Proprietary and Confidential DELETE FROM table DELETE DELETE FROM table WHERE column = "some" AND name = "value" OR other_column = "other value" ORDER BY some ASC, columns DESC LIMIT number;
56. Proprietary and Confidential DELETE FROM users; DELETE
58. Proprietary and Confidential DELETE FROM users WHERE id = 1; DELETE
59. Constraints
60. 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
61. 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
62. Proprietary and Confidential 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) ); Constraints: Users Table Schema
63. Features
64. Proprietary and Confidential Features 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)
65. Proprietary and Confidential • ID should be auto increment • ID should be the Primary Key • ID should be unsigned Features: Users Table
66. Proprietary and Confidential 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) ); Features: Users Table Schema
67. Indexes
68. 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
69. INDEX, UNIQUE, & PRIMARY KEY
70. Proprietary and Confidential • Can be added during table creation:
 CREATE TABLE foo (
 column_name TYPE,
 INDEX name (column, list),
 UNIQUE INDEX name (column, list),
 PRIMARY KEY (column)
 ); • Index/Unique Index 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
71. Foreign Keys
72. 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
73. Profiles & Foreign Keys
74. Proprietary and Confidential • Unique Identifier • Short Introductory Summary • Full Biography • Location
 
 
 
 • Must link to the Users table • One Profile per User Profiles Table Consider:
75. Proprietary and Confidential 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
76. Proprietary and Confidential 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 ); Profiles Table Schema
77. Proprietary and Confidential INSERT INTO profiles ( users_id, summary, bio, location ) VALUES ( 1, "Community Engineer at Engine Yard", NULL, "Florida, USA" ); INSERT
78. Indexing Your Data
79. Proprietary and Confidential
80. 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 automatically • Indexes cannot be used with LIKE if starting with a wildcard (e.g. %foo%) Indexing Your Data
81. Indexes
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: Indexes
83. 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: Indexes SELECT * FROM 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%";
84. Proprietary and Confidential Indexes Users Key Type id PRIMARY KEY username UNIQUE username, password UNIQUE email UNIQUE
85. JOINs Connecting Tables
86. Proprietary and Confidential • Used to JOIN multiple tables – INNER JOIN – LEFT or RIGHT OUTER JOIN JOINs
87. Proprietary and Confidential INNER JOIN Users Profiles Users with Profiles
88. OUTER JOIN For when one side or the other doesn’t match
89. Proprietary and Confidential LEFT OUTER JOIN Users Users with Profiles Profiles
90. Proprietary and Confidential RIGHT OUTER JOIN Users Posts with Users (Authors) Posts Posts by Deleted Users
91. Proprietary and Confidential SELECT * FROM users INNER JOIN profiles ON ( profiles.user_id = users.id ) WHERE 
 profiles.location LIKE '%Edmonton%' ORDER BY 
 users.first_name, users.last_name; SELECT... INNER JOIN
92. Proprietary and Confidential SELECT * FROM users LEFT OUTER JOIN profiles ON ( profiles.user_id = users.id ) WHERE users.id = 1; SELECT... LEFT OUTER JOIN
93. 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%';
94. Databases and PHP
95. Connecting to Databases Using PDO
96. Proprietary and Confidential • PDO – MySQL – PostgreSQL – MSSQL – Oracle – SQLite – ODBC and DB2 – Firebird • DSN — Data Source Name – Driver Name – Hostname & Port Connecting to Databases
97. Proprietary and Confidential <?php try { $pdo = new \PDO( "mysql:dbname=db;host=localhost;port=3306", "user", "pass" ); } catch (\PDOException $ex) { error_log($ex->getMessage()); } ?> Connecting to MySQL
98. Querying Data
99. Proprietary and Confidential try { $pdo = new \PDO(…); $query = $pdo -> prepare( "SELECT * FROM user WHERE id = :id" ); $conditions = array( ':id' => 1 ); $result = $query->execute($conditions); } catch (\PDOException $ex) { error_log($ex->getMessage()); } Executing Queries
100. Handling Results
101. Proprietary and Confidential <?php $result = $query->execute($conditions); if ($result) {
 echo "Results Found: " .$query->rowCount(); while ($row = $query->fetch()) { echo "<a href='/edit/" .$row['id']. "'>" .$row['first_name']. ' ' . $row['last_name'] .'</a>'; } } ?> Handling Results
102. Proprietary and Confidential <?php $result = $query->execute($conditions); if ($result) { echo "Results Found: " .$query->rowCount(); while ($row = $query->fetchObject()) { echo "<a href='/edit/" .$row->id. "'>" .$row->first_name. ' ' . $row->last_name .'</a>'; } } ?> Handling Results as Objects
103. Proprietary and Confidential class User { function getName() { return $this->first_name . ' ' . $this->last_name; } } if ($result) { echo "Results Found: " .$query->rowCount(); while ($row = $query->fetchObject("User")) { echo "<a href='/edit/" .$row->id. "'>" .$row->getName(). '</a>'; } } Handling Results as Custom Objects
104. Proprietary and Confidential Feedback & Questions: 
 Twitter: @dshafik Email: davey@engineyard.com Slides: http://daveyshafik.com/slides