Matthew M.

Matthew M.

SQL: Joins

Slide Duration:

Table of Contents

Section 1: Advanced PHP with MySQL
Course Introduction

13m 36s

Intro
0:00
Advanced PHP w/ MySQL
0:13
Course Introduction
0:14
Course Content
0:59
Web Application: Educator Store
1:45
Web Application: Educator Store
1:46
Object-Oriented Programming
6:09
Object-Oriented Programming Overview
6:10
MySQL
6:50
MySQL Overview
6:51
Example: Command Prompt & MySQL
8:34
What You Will Learn
9:34
What You Will Learn
9:35
Course Prerequisites
11:52
Course Prerequisites
11:53
Advanced Course Development Environment

18m 46s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Development Environment Setup
1:36
Firefox Web Browser, XAMPP, and PSPad Text Editor
1:37
Course Directory Structure
4:36
Course Directory Structure
4:37
phpDocumentor
7:23
phpDocumentor Overview
7:24
Parsing Source Code
7:44
docs Subdirectory
9:06
Asvanced PHP Course Documentation Example
10:21
Previous Educator PHP Students
14:14
Lib Directory Changes
14:38
Images Directory Changes
14:57
VERSION Constant
15:40
Comments Noting a Specific Version Number
17:04
Homework Challenge
17:30
Homework Challenge
17:31
Introduction to File I/O

35m 37s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
File I/O
1:34
File I/O Overview
1:35
Performing file I/O: 3 Steps
1:52
File Permissions
2:28
Read Permission & Write Permission
2:29
fopen()
4:47
fopen()
4:48
Two Required Parameters
5:01
Successful fopen(): File Handle and Resource
5:21
FALSE and E_WARNING Error
6:00
Example
6:39
File Paths
7:10
File Paths
7:11
File Access Modes
9:18
File Access Modes
9:19
Coding Example: fopen()
10:31
Using Relative and Absolute Path & Non-Existent File
10:32
File Pointers
15:55
File Pointers
15:56
Example File
16:11
Opening a File with fopen() Using 'r' as the Access Mode
16:22
fgets()
17:55
fgets() Overview
17:56
Coding Example: fgets() - Reading a Line at a Time
18:26
feof()
20:10
feof() Overview
20:09
Coding Example: feof()
20:50
fclose()
23:43
fclose() Overview
23:44
Coding Example: fclose()
25:02
Coding Example: Current Stock
26:10
Coding Example: Current Stock
26:11
trim ()
31:39
trim () Overview and Example
31:40
Homework Challenge
33:35
Homework Challenge
33:36
Web Application Development

36m 36s

Intro
0:00
Lesson Overview
0:16
Lesson Overview
0:17
Version 1.0 Changelog
0:31
catalog.php
0:32
Function Library: fileLIB.php
5:00
Version 1.1 Changelog
27:12
createItemDataArray() and createDeptDataArray()
27:13
outputSimpleItemLink() and outputSimpleDeptLink()
32:06
HTTP & the POST Method

34m 36s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
HTTP Model
1:28
HTTP Model
1:29
Client-Server Model 2 Steps Process
2:00
HTTP Messages
2:23
HTTP Messages
2:24
HTTP Requests
3:09
HTTP Requests
3:10
Example: HTTP Request Header
3:17
Coding Example: HTTP Requests
4:29
HTTP Responses
7:15
HTTP Responses
7:16
Example: HTTP Response (Header & Body)
8:00
header()
11:47
header() Overview
11:48
Coding Example: header
14:02
Coding Example: header() Error
14:52
GET Method
18:55
GET Method
18:56
Coding Example: GET Method
20:13
POST Method
21:09
POST Method
21:10
Coding Example: Sample Form Using the POST Method
23:50
Accessing POST Data in PHP
26:38
Accessing POST Data in PHP
26:39
Coding Example: Accessing POST Data via $_POST
28:15
GET vs. POST
29:54
GET vs. POST
29:55
Example: Accessing POST Data via $_POST
31:20
Example: Accessing POST Data via $_POST
31:21
Homework Challenge
33:00
Homework Challenge
33:01
Web Application Development

14m 11s

Intro
0:00
Version 2.0 Changelog
0:09
Updating the Site's Current Form to Use the POST Method
0:10
Creating Admin Website
2:55
Dynamically Generated Department List
12:40
Writing to Files

17m 38s

Intro
0:00
Lesson Overview
0:09
Lesson Overview
0:10
Review of File I/O
0:40
Review of File I/O
0:41
fwrite()
1:38
fwrite()
1:39
fputs()
2:20
'r+'
2:31
Coding Example: fwrite()
2:59
fopen() Access Modes
8:41
'a' and 'a+'
8:42
'w' and 'w+'
11:34
Homework Challenge
14:47
Homework Challenge
14:48
Web Application Development

28m 14s

Intro
0:00
Lesson Overview
0:08
Lesson Overview
0:09
Updated Open File Functions
0:19
openItemsDataFile ( ), openDeptsDataFile ( ), and openLastAddedFile ( )
0:20
insertItem()
6:25
insertItem() Overview
6:26
Functions: createItemDataString ( ) and updateLastItemAdded ( )
7:07
addItemToDepartment ()
16:03
addItemToDepartment () Overview
16:04
Functions: createDeptDataString ( ) and updateDepartment ( )
17:55
HTTP POST: File Uploads

22m 51s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
POST Data Encoding
1:13
POST Data Encoding
1:14
When Data is Included in the Body of the HTTP POST Request and is of the Content Type
1:31
When Uploading Files Using HTTP POST
3:17
Uploading Files
6:22
Uploading Files
6:23
If Encoding Type is Not Set to Multipart/Formdata
9:27
$_FILES Superglobal
10:59
$_FILES Superglobal
11:00
Structure of $_FILES
12:15
Structure of $_FILES: Name, Type, tmp_name, Error, and Size
12:16
Coding Example: $_FILES Superglobal
13:34
Moving an Uploaded File
15:39
Introduction to Moving an Uploaded File
15:40
move_uploaded_file ( ): Definition and Example
16:40
Homework Challenge
21:19
Homework Challenge
21:20
Web Application Development

15m 30s

Intro
0:00
Lesson Overview
0:09
Lesson Overview
0:10
Uploading Item Image File
1:22
Uploading Item Image File
1:23
Uploading Item Image File (cont.)
2:43
Altering insertItem ( )
2:44
Helpfer Functions: getFileExtension ( ) and moveUploadedFile ( )
4:30
isValidItem ( )
13:03
Introduction to Object-Oriented Programming

32m 44s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Object-Oriented Programming
0:56
Introduction to Object-Oriented Programming
0:57
Associative Arrays
5:27
Associative Arrays
5:28
Classes
7:27
Classes Overview
7:28
Defining Classes
8:24
Defining Classes
8:25
Declaring Class Properties
9:25
Coding Example: Classes
10:11
Objects
12:18
Objects Overview
12:19
Classes vs. Objects
12:49
Classes vs. Objects
12:50
Instantiating Classes
14:58
Instantiating Classes
14:59
Coding Example: Instantiate an Item Object
16:30
Object Properties
19:21
Access and Set an Object's Property
19:22
Coding Example: Set & Access the Properties of an Item Object
24:23
Homework Challenge
30:22
Homework Challenge
30:23
Web Application Development

17m 47s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Version 5.0 Overview
0:50
Version 5.0 Overview and Examples
0:51
Outputting Object Properties
12:38
Outputting Object Properties
12:39
Array Object Properties
14:18
Access and Set Array Value
14:19
Object Methods

39m 57s

Intro
0:00
Lesson Overview
0:12
Lesson Overview
0:13
Object Methods
0:52
Definition of Object Methods
0:53
Calling Object Methods
3:25
Calling Object Methods
3:26
Coding Example: Simple Object Methods
4:44
Object Methods and Return Values
7:02
$this Variable
10:06
$this Variable: Definition and Example
10:07
$this Variable (cont.)
15:51
$this Variable (cont.)
15:52
Getters & Setters
21:21
Getters & Setters Methods
21:22
Object Methods In Strings
25:46
Object Methods In Strings
25:47
Coding Example: Outputting Method Return Values in Strings
27:41
Using $this to Call Other Methods
28:49
Using $this to Call Other Methods
28:50
Homework Challenge
34:18
Homework Challenge: 1-3
34:19
Homework Challenge (cont.)
36:20
Homework Challenge: 4-6
36:21
Homework Challenge (cont.)
37:52
Homework Challenge: 7-10
37:53
Web Application Development

17m 30s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Version 6.0 Overview
0:40
Version 6.0 Changes & Examples
0:41
Item Methods
0:53
Item Class Definition: getImageFilename()
0:54
Coding Example: getImageFilename()
1:58
Department Methods
7:33
addItem(), removeItem(), and isItemInDept() Method
7:34
addItemtToDepartment() & buildDeptObject() Functions
11:46
A Default Value Needed to Be Set for $items
16:26
Object Constructors

22m 20s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Object Constructors
0:32
Introduction to Object Constructors
0:33
Coding Example: Object Constructors
1:43
Defining Constructors
3:26
Defining Constructors
3:27
Coding Example: Constructors with No Arguments
4:24
Constructor Arguments
8:09
Constructor Arguments
8:10
Coding Example: Constructor Arguments
9:49
Important Notes
16:13
Important Notes
16:14
Homework Challenge
17:35
Homework Challenge: 1 - 4
17:36
Homework Challenge (cont.)
19:13
Homework Challenge: 5 - 9
19:14
Homework Challenge (cont.)
21:40
Homework Challenge: 10 and 11
21:41
Web Application Development

16m 19s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
Version 7.0 Overview
0:39
Version 7.0 Overview
0:40
Item Constructor
2:19
Item Constructor
2:20
Department Constructor
7:47
Department Constructor
7:48
Customer Constructor
12:23
Customer Constructor
12:24
More Magic Methods

53m 37s

Intro
0:00
Lesson Overview
0:13
Lesson Overview
0:14
Magic Methods
1:12
Magic Methods
1:13
Destructors
2:45
Destructors Overview
2:46
Coding Example: Calling Destructors
4:30
Coding Example: Object Destructor
9:19
_to String ()
16:12
_to String () Overview
16:13
Coding Example: _to String () Magic Method
18:10
Access Modifiers
21:23
Introduction to Access Modifiers
21:24
Access Modifiers: Public
21:39
Access Modifiers: Private
22:18
Access Modifiers: Protected
22:41
Object Properties and Methods
23:06
Coding Example: Public Access Modifiers
26:48
Coding Example: Private Access Modifiers
28:30
_get()
31:37
_get() Overview
31:38
Coding Example: _get () Magic Method
33:30
_set ()
36:23
_set () & the Magic Method
36:24
Using Getters & Setters
44:37
Coding Example: Using Getters & Setters
44:38
Homework Challenge
50:33
Homework Challenge: 1 - 6
50:34
Homework Challenge (cont.)
51:41
Homework Challenge: 7 - 12
51:42
Web Application Development

31m 12s

Intro
0:00
Version 8.0 Review
0:12
Version 8.0 Review
0:13
private Object Properties
1:15
private Object Properties
1:16
Coding Example
2:54
_toString() Methods
6:51
_toString() Methods
6:52
Coding Example
8:09
DataFile Class
13:27
DataFile Class & I/O Operations
13:28
Using DataFile Class: Instantiate a DataFile, DataFile open () Method, and DataFile close () Method
18:09
Homework Challenge
29:35
Homework Challenge
29:36
Classes vs. Objects

37m 49s

Intro
0:00
Lesson Overview
0:09
Lesson Overview
0:10
Class vs. Object Review
0:58
Class vs. Object Review
0:59
Static Properties
2:42
Static Properties
2:43
Scope Resolution Operator (::)
5:26
Scope Resolution Operator (::) & Static Property
5:27
Coding Example: Accessing a Static Property Using the Scope Resolution Operator
7:57
Coding Example: Accessing a Static Property in a Class Definition
10:33
Coding Example: Using a Static Property as an Instance Counter
14:18
Static Methods
18:51
Static Methods Overview
18:52
Coding Example: Static Methods
21:39
Classes vs. Objects
26:28
Classes vs. Objects: Diagrams
26:29
Class Constants
30:24
Class Constants Definition
30:25
Coding Example: Using Class Constants
31:55
Homework Challenge
34:01
Homework Challenge: 1 - 5
34:02
Homework Challenge (cont.)
35:25
Homework Challenge: 6 - 10
35:26
Web Application Development

21m 7s

Intro
0:00
Version 9.0 Overview
0:12
Version 9.0 Changes & Examples
0:13
Output Class
1:08
Output Class & Static Methods
1:09
outputHtmlSelector ( ) Method
2:43
outputItemSelector ( ) Method
4:53
outputDeptSelector ( ) Method
6:42
buildObject() Methods
11:54
buildObject() Methods & Examples
11:55
Exceptions

22m 43s

Intro
0:00
Lesson Overview
0:12
Lesson Overview
0:13
try Blocks
0:44
try Blocks Definition
0:45
catch Blocks
2:03
catch Blocks: Definition and Syntax
2:04
Coding Example: try/catch Blocks
3:48
When an Exception Object is Caught & Error Handling
5:59
Exception Class
7:02
Exception Class
7:03
Coding Example: Using try/catch to Catch an Exception Thrown from a Method
8:04
Re-Throwing Exceptions
16:39
Re-Throwing Exceptions
16:40
Coding Example: Re-throwing an Exception
17:21
Homework Challenge
20:40
Homework Challenge: 1 - 5
20:41
Homework Challenge (cont.)
22:17
Homework Challenge: 6
22:18
Web Application Development

22m

Intro
0:00
Version 10.0 Overview
0:11
Updating addItem.php, addItemToDept.php, and error.php
0:12
Updating DataFile Class: open ( ) Method & try/catch blocks
7:45
Version 10.1 Overview
13:37
Version 10.1 Changes & Examples
13:38
Updating DataFile Class: close ( ) Method & logWarning ( )
20:17
Cookies

39m 47s

Intro
0:00
Lesson Overview
0:09
Lesson Overview
0:10
What is a Cookie?
1:15
Definition of Cookie
1:16
HTTP - A Stateless Protocol
2:17
HTTP - A Stateless Protocol
2:18
Purpose of Cookies
4:10
Set - Cookie Header
6:21
Introduction to Set - Cookie Header
6:22
Coding Example: Set - Cookie Header
7:38
Cookie Header
9:45
Introduction to Cookie Header
9:46
Example: Cookie Header
10:27
setcookie()
12:33
Introduction to setcookie()
12:34
Example: Using setcookie
13:54
Cookies Attributes
15:48
Cookies Attributes Overview
15:49
Common Cookies Attributes: Domain
16:24
Common Cookies Attributes: Path
17:04
Common Cookies Attributes: Expires
19:34
Coding Example: Setting Cookie Attributes
20:58
$_COOKIE Superglobal
27:03
$_COOKIE Superglobal
27:04
Coding Example: Accessing Cookies from PHP Using $_COOKIE
29:45
Security Implications
34:44
Security Implications
34:45
Homework Challenge
36:10
Homework Challenge: 1 - 4
36:11
Homework Challenge (cont.)
37:47
Homework Challenge: 5 - 7
37:48
Web Application Development

39m 11s

Intro
0:00
Lesson Overview
0:17
Lesson Overview
0:18
instanceof Operator
1:34
instanceof Operator
1:35
Objects with Object Properties
2:53
Objects with Object Properties
2:54
Version 11.0 Overview
6:41
Version 11.0 Changes: Cart and CartItem Classes
6:42
Version 11.0 Coding Example
10:40
Version 11.1 Overview
31:03
Version 11.1 Changes & Examples
31:04
Introduction to Sessions

46m 8s

Intro
0:00
Lesson Overview
0:08
Lesson Overview
0:09
What is a Session?
0:53
What is a Session?
0:54
Client-Side Sessions
3:51
Client-Side Sessions
3:52
Server-Side Sessions
5:04
Server-Side Sessions
5:05
Session IDs
6:26
Session IDs
6:27
The SID is Used by the Server to:
7:08
A Client Passes an SID to the Server with Each HTTP Request Via:
8:15
Sessions in PHP
12:19
Sessions in PHP
12:20
session_start()
14:40
session_start() Overview
14:41
Coding Example: Using session_start () to Continue a Session
18:03
$_SESSION Superglobal
22:06
$_SESSION Superglobal Overview
22:07
Coding Example: Setting a Session Variable via $_SESSION
25:22
Accessing Session Data
28:08
Accessing Session Data
28:09
Deleting Session Data
31:43
Deleting Session Data
31:44
Coding Example: Deleting Session Data
32:16
Configuring PHP Sessions
36:14
session.cookie_lifetime, session.cookie_domain, and session.cookie_path
36:15
Coding Example: Configuring PHP Sessions
37:28
Configuring PHP Sessions (cont.)
40:56
session.use_cookies, session.use_trans_sid, and session.use_only_cookies
40:57
Coding Example: Configuring PHP Sessions
42:25
Homework Challenge
42:55
Homework Challenge: 1 - 5
42:56
Homework Challenge (cont.)
44:10
Homework Challenge: 6 - 10
44:11
Homework Challenge (cont.)
45:27
Homework Challenge: 11 - 13
45:28
Web Application Development

11m

Intro
0:00
Version 12.0 Overview
0:12
Version 12.0 Changes Part I: Updating 'viewCart.php' & 'checkout.php'
0:13
Version 12.0 Changes Part II
5:03
Destroying Sessions

29m 59s

Intro
0:00
Lesson Overview
0:12
Lesson Overview
0:13
Destroying Sessions
1:02
Destroying Sessions
1:03
session_destroy()
2:10
session_destroy() Overview
2:11
Coding Example: Setting a Session Variable and Destroying a Session
3:18
Deleting Session Cookies
8:38
Deleting Session Cookies
8:39
Coding example: Deleting Session Cookies
9:17
Review of Steps
21:07
Review of Steps
21:08
Garbage Collection
21:50
Garbage Collection Overview
21:51
Coding Example: Garbage Collection
24:30
Homework Challenge
26:28
Homework Challenge: 1 - 4
26:29
Homework Challenge (cont.)
28:16
Homework Challenge: 5 - 9
28:17
Web Application Development

19m 14s

Intro
0:00
Lesson Overview
0:13
Lesson Overview
0:14
_autoload()
0:46
Introduction to _autoload ()
0:47
Version 13.0 Overview
4:06
_autoload () and SessionManager Class
4:07
Updating 'viewCart.php' & 'checkout.php'
11:16
Introduction to Databases

21m 24s

Intro
0:00
Lesson Overview
0:16
Lesson Overview
0:17
Flat Files
1:09
Flat Files: Definition and Example
1:10
Problems Associated with Using Flat Files as a Database
2:15
Relational Databases
3:29
Relational Databases
3:30
Relational Database Management System (RDBMS)
3:50
Tables
7:43
Tables
7:44
Columns
9:24
Columns
9:25
What is SQL?
10:45
Introduction to Structured Query Language
10:46
Standard Data Types of SQL
12:23
Primary Keys
13:19
Primary Keys
13:20
Primary Key Examples
16:36
Primary Key Examples
16:37
Introduction to MySQL

28m 11s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
What is MySQL?
1:33
MySQL
1:34
MySQL Monitor
5:16
MySQL Monitor Overview
5:17
XAMPP & MySQL
5:58
In the MySQL Command: hostname, username and -p
7:05
Connecting to a MySQl Server
9:52
Connecting to a MySQl Server
9:53
Using MySQl Monitor
13:48
Using MySQl Monitor
13:49
GRANT Command
15:10
GRANT SQL Command
15:11
Privileges & Objects
18:18
Username, Hostname, and Password
19:37
Specifying Objects
20:05
Creating a PHP User Account
21:41
Creating a PHP User Account Overview
21:42
Meaning of all & *.*
23:35
Example: Creating a PHP User Account
23:48
Required Homework
26:27
Required Homework: 1 - 5
26:28
Required Homework (cont.)
27:28
Required Homework: 6
27:29
Creating Databases & Tables

23m 1s

Intro
0:00
Lesson Overview
0:08
Lesson Overview
0:09
Creating a Database
1:40
Introduction to Creating a Database
1:41
Example: Creating a Database
3:33
CREATE TABLE Command
6:43
CREATE TABLE Command
6:44
Data Types
7:39
Numeric Data Types: INT, INT UNSIGNED, and DECIMAL
7:40
String Data Types: CHAR (M), VARCHAR (M), and TEXT
9:23
Colum Attributes
11:00
PRIMARY KEY Attribute
11:01
AUTO INCREMENT Attribute
11:35
Items Table
13:07
Items Table
13:08
Useful Commands
17:04
SHOW TABLES Command & DESCRIBE Utility Statement
17:05
Example: Creating an Items Table
17:58
Required Homework
20:51
Required Homework: 1 - 6
20:52
Required Homework (cont.)
21:55
Required Homework: 7 - 9
21:56
SQL Command: INSERT

27m 11s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
INSERT Command
1:20
SQL INSERT Command
1:21
Specifying Values
3:16
Columns with Numeric Data Types
3:17
Columns with String Data Types
3:34
Columns with AUTO INCREMENT
4:07
Inserting Items
5:21
Inserting Items
5:22
Example: Inserting Items
7:03
SQL Text Files
14:04
SQL Monitor Client
14:05
MySQL Monitor & the < Operator
15:15
Example
17:35
Required Homework
22:32
Required Homework: 1 - 6
22:33
Required Homework (cont.)
24:19
Required Homework: 7 - 10
24:20
SQL Command: SELECT

24m 57s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
SELECT Command
0:42
SELECT Command: Definition and Syntax
0:43
Results of a SELECT Query
1:53
To Specify the Retrieval of All Columns from a Table
3:06
Example: SELECT Command
4:18
WHERE Clause
7:11
WHERE Clause
7:12
A SELECT Query with WHERE Clause has the Following Syntax
8:10
A Where Condition can Contain both Logical and Comparison Operators
9:44
Literal Values
13:24
Literal Values
13:25
Example: Literal Values
14:18
ORDER BY Clause
15:31
ORDER BY Clause & SELECT Query
15:32
Ascending and Descending Order
17:30
An ORDER BY Clause can Specify Multiple Columns to Order the Rows by
18:01
Example: SELECT Query with WHERE and ORDER BY Clause
20:32
Homework Challenge
21:59
Homework Challenge
22:00
Homework Challenge (cont.)
23:54
Homework Challenge
23:55
Using PHP from MySQL

32m 43s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
PHP/MySQL Architecture
0:56
PHP/MySQL Architecture
0:57
MySQLi Extension
3:05
MySQLi Extension
3:06
Steps to Using MySQL from PHP
5:53
Four Steps to Using MySQL from PHP
5:54
Step # 3
7:18
Connecting to a Database
8:12
To Connect to a MySQL Database
8:13
To Test for Connection Errors
10:20
Running an SQL Statement
11:54
Running an SQL Statement
11:55
Example
13:30
Processing the Results
15:58
For non-SELECT Queries
15:59
MySQLi Object Property: affected_rows
16:19
For INSERT Statements on Tables with an AUTO_INCREMENT Column
18:07
Closing the Connection
19:45
Closing the Connection
19:46
Coding Example
21:24
Coding Example: Using MySQL from PHP
21:25
Homework Challenge
29:00
Homework Challenge: 1 - 4
29:01
Homework Challenge (cont.)
31:10
Homework Challenge: 5 - 9
31:11
Web Application Development

30m 30s

Intro
0:00
Lesson Overview
0:14
Lesson Overview
0:15
sprintf()
1:28
sprintf() Overview
1:29
Example
1:56
INSERT Statements with sprintf()
5:15
INSERT Statements with sprintf()
5:16
Version 14.0 Overview
10:00
Version 14.0 Changes & Examples Part 1
10:01
Version 14.0 Changes & Examples Part 2
21:58
SELECT QUERIES from PHP

24m 22s

Intro
0:00
Lesson Overview
0:14
Lesson Overview
0:15
SELECT Queries
1:21
SELECT Queries
1:22
MySQLi_Result Class
3:17
MySQLi_Result Class: num_rows
3:18
MySQLi_Result Class: fetch_assoc ( ) and fetch_object ( )
3:59
fetch_assoc()
5:17
fetch_assoc() Overview
5:18
Coding Example: Using fetch_assoc()
9:00
stdClass Objects
12:46
stdClass Objects
12:47
fetch_object()
14:19
fetch_object() Overview
14:20
Coding Example: Using fetch_object()
16:45
Freeing Result Set Memory
18:42
Freeing Result Set Memory
18:43
Coding Example: Using free ( )
20:01
Homework Challenge
22:07
Homework Challenge: 1 - 5
22:08
Web Application Development

17m 6s

Intro
0:00
Version 15.0 Overview
0:13
Version 15.0 Changes & Examples Part 1
0:14
Version 15.0 Changes & Examples Part 2
8:43
Linking Tables

24m 32s

Intro
0:00
Lesson Overview
0:16
Lesson Overview
0:17
A Departments Table
1:08
Departments Table
1:09
How Could We Model This in a Table: Option 1
1:26
How Could We Model This in a Table: Option 2
3:12
Database Normalization
4:37
Database Normalization
4:38
Foreign Keys
8:47
Foreign Keys
8:48
Linking Table
11:26
Linking Table
11:27
Multiple Column Primary Keys
15:30
Multiple Column Primary Keys
15:31
Example
18:51
Required Homework
22:34
Required Homework: 1 - 3
22:35
Required Homework (cont.)
24:10
Required Homework: 4
24:11
Web Application Development

24m

Intro
0:00
Version 16.0 Overview
0:11
Version 16.0 Changes
0:12
Version 16.0 Coding Examples
4:12
Version 16.0 (cont.)
18:30
Version 16.0 Changes & Examples Part 2
18:31
SQL Command: DELETE

10m 33s

Intro
0:00
Lesson Overview
0:12
Lesson Overview
0:13
DELETE Statement
0:30
DELETE Statement & Its WHERE Clause
0:31
Delete All Rows From a Table
2:57
Using DELETE from PHP
4:04
Using DELETE from PHP Overview
4:05
Coding Example: Using DELETE from PHP
6:37
Homework Challenge
8:53
Homework Challenge: 1 - 4
8:54
Web Application Development

16m 2s

Intro
0:00
Version 17.0 Overview
0:11
Version 17.0 Changes
0:12
Version 17.0 Coding Example
2:51
Transactions
12:50
Database Transaction
12:51
Steps in Using Transactions
14:00
SQL Command: UPDATE

32m 14s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
UPDATE Statement
0:54
UPDATE Statement & Its WHERE Clause
0:55
Example: UPDATE Statement
3:32
Using UPDATE from PHP
5:21
Using UPDATE from PHP Overview
5:22
Coding Example: Using UPDATE from PHP
6:53
htmlspecialchars()
9:04
htmlspecialchars()
9:05
Encoding Special Characters
9:23
Coding Example: htmlspecialchars()
13:41
addslashes()
20:40
addslashes()
20:41
Coding Example: addslashes()
22:52
Homework Challenge
27:55
Homework Challenge: 1 - 2
27:56
Homework Challenge (cont.)
30:14
Homework Challenge: 3 - 5
30:15
Homework Challenge (cont.)
30:41
Homework Challenge: 6 - 9
30:42
Web Application Development

12m 33s

Intro
0:00
Version 18.0 Overview
0:12
Version 18.0 Changes
0:13
Version 18.0 Coding Example
1:24
SQL: Joins

42m 16s

Intro
0:00
Lesson Overview
0:11
Lesson Overview
0:12
DATETIME Data Type
1:52
DATETIME Data Type
1:53
Modeling Orders
5:12
Modeling Orders
5:13
Customers Table
10:46
Customers Table
10:47
Example
16:18
Example: Order Table in MySQL
16:19
Joins
22:26
Joins Overview
22:27
Inner Join
23:09
Example: Joins
24:11
Join Conditions
28:14
Join Conditions
28:15
Example
28:32
Join Queries
30:00
Example: Join Conditions in MySQL
30:36
Implicit Joins
34:42
Implicit Joins
34:43
Example: Implicit Joins
35:57
Aliases
37:28
Introduction to Aliases
37:29
Example: Aliases
38:55
Required Homework
40:32
Problem 1
40:33
Problem 2
41:01
Problem 3
41:17
Web Application Development

29m 34s

Intro
0:00
Lesson Overview
0:12
Lesson Overview
0:13
Inserting Orders
0:44
Inserting Orders
0:45
Version 19.0 Overview
3:45
Version 19.0 Changes & Example Part 1
3:46
Version 19.0 Changes & Example Part 2
13:18
Version 19.1 Overview
21:10
Version 19.1 Changes
21:11
Version 19.1 Coding Example
22:18
User Authentication

26m 13s

Intro
0:00
Lesson Overview
0:10
Lesson Overview
0:11
User Authentication
1:31
User Authentication
1:32
Authentication with Sessions
2:34
Authentication with Sessions
2:35
Four Steps in Authentication via Sessions
2:50
Using Sessions to Restrict Access
3:58
Using Sessions to Restrict Access
3:59
Coding Example: Restricted Access Area
4:47
Authentication Methods
5:54
Authentication Methods Overview
5:55
Coding Example: Authentication Methods
7:31
Logging Out
9:57
Logging Out
9:58
Coding Example: Log Out
10:47
Users Table
13:50
Users Table
13:51
Example: Creating a Users Table
15:08
Password Hashing
17:30
Password Hashing
17:31
PHP and MySQL Built-in Hash Functions: sha1 ( ) and md5 ( )
18:43
Coding Example: Password Hashing
19:27
Required Homework
24:41
Required Homework: 1 - 4
24:42
Web Application Development

14m 36s

Intro
0:00
Version 20.0 Overview
0:13
Version 20.0 Changes & Examples Part 1
0:14
Version 20.0 Changes & Examples Part 2
5:05
Version 20.0 Changes & Examples Part 3
7:29
Version 20.0 (cont.)
8:31
Version 20.0 Changes & Examples Part 4
8:32
Loading...
This is a quick preview of the lesson. For full access, please Log In or Sign up.
For more information, please see full course syllabus of Advanced PHP
Bookmark & Share Embed

Share this knowledge with your friends!

Copy & Paste this embed code into your website’s HTML

Please ensure that your website editor is in text mode when you paste the code.
(In Wordpress, the mode button is on the top right corner.)
  ×
  • - Allow users to view the embedded video in full-size.
Since this lesson is not free, only the preview will appear on your website.
  • Discussion

  • Study Guides

  • Download Lecture Slides

  • Table of Contents

  • Transcription

  • Related Services

Start Learning Now

Our free lessons will get you started (Adobe Flash® required).
Get immediate access to our entire library.

Sign up for Educator.com

Membership Overview

  • Unlimited access to our entire library of courses.
  • Search and jump to exactly what you want to learn.
  • *Ask questions and get answers from the community and our teachers!
  • Practice questions with step-by-step solutions.
  • Download lesson files for programming and software training practice.
  • Track your course viewing progress.
  • Download lecture slides for taking notes.
  • Learn at your own pace... anytime, anywhere!

SQL: Joins

  • MySQL provides the DATETIME data type for storing a timestamp.
  • The PHP date() function can be used to generate properly formatted DATETIME strings: ‘YYYY-MM-DD HH:MM:SS’.
  • It is advantageous to store timestamps as DATETIME data rather than as simple strings, like VARCHAR, because MySQL can perform date calculations on DATETIME columns to restrict the result sets of queries.
  • A join enables us to extract data from more than one table in a single SELECT query.
  • The basic type of join is called an inner join and is specified by using the INNER JOIN SQL keywords:
    SELECT * FROM orders INNER JOIN customers;
  • An inner join forms the Cartesian product of all of the rows from all tables specified, meaning that the result set will contain a row for each combination of the rows from one table with all of the rows from the other tables.
  • A join condition can be provided using the ON keyword in order to restrict the rows present in an inner join result set based on specified criteria. For example:
    SELECT * FROM orders INNER JOIN customers ON orders.custID=customers.custID;
  • Join queries involve multiple tables so to specify a column from one of the tables in a join, you use the syntax:
    tableName.columnName
  • The join condition specified after the ON keyword functions the same way that a where condition does.
  • An INNER JOIN can also be implicitly defined by using a WHERE clause in the following manner:
    SELECT * FROM orders, customers WHERE orders.custID=customers.custID;
  • In queries using joins, it is often useful to define aliases, which is a shortened way to refer to a table in a query. In SQL, aliases are defined using the AS keyword:
    SELECT o.orderID, c.firstName, c.lastName
    FROM orders AS o
    INNER JOIN customers AS c
    ON o.custID=c.custID;
  • Additional Resources:

SQL: Joins

Lecture Slides are screen-captured images of important points in the lecture. Students can download and print out these lecture slide images to do practice problems as well as take notes while watching the lecture.

  • Intro 0:00
  • Lesson Overview 0:11
    • Lesson Overview
  • DATETIME Data Type 1:52
    • DATETIME Data Type
  • Modeling Orders 5:12
    • Modeling Orders
  • Customers Table 10:46
    • Customers Table
  • Example 16:18
    • Example: Order Table in MySQL
  • Joins 22:26
    • Joins Overview
    • Inner Join
    • Example: Joins
  • Join Conditions 28:14
    • Join Conditions
    • Example
    • Join Queries
    • Example: Join Conditions in MySQL
  • Implicit Joins 34:42
    • Implicit Joins
    • Example: Implicit Joins
  • Aliases 37:28
    • Introduction to Aliases
    • Example: Aliases
  • Required Homework 40:32
    • Problem 1
    • Problem 2
    • Problem 3

Transcription: SQL: Joins

Welcome back to Educator.com's Advanced PHP with MySQL course.0000

In today's lesson, we are going to be talking about a SQL concept known as JOINS,0004

which is going to allow us to query data from multiple tables at the same time.0007

Before we get into working with JOINS in particular, we are going to talk about a couple topics.0013

The first thing that we are going to do is introduce a new SQL type for columns in our database, called DATETIME,0019

which is going to allow us to store a timestamp, a date/time value.0027

And then, we are going to talk about how, currently, in our web application, when a user creates an order0032

or submits an order in the store, it emails the store administrator the information about the order.0038

Well, we are going to be changing that system, in our next version of the web application,0044

to where it is just going to store the order information in the database, as opposed to sending an email.0048

We are going to discuss, in today's lesson, about how we might model orders within our database.0053

It is going to involve an Orders table; it is going to involve a Customers table.0059

And then, that is going to bring us to our concept of JOINS, which is where we are going to be learning0063

how to write queries that get information from multiple tables.0069

And that is going to relate to how we pull information about orders from our database.0072

As far as JOINS goes, we are going to talk about something called the join conditions, which is like a WHERE condition.0077

It is going to allow us to specify what data we want to pull from multiple tables.0082

We are going to talk about two other related concepts: one known as an implicit joins and something known as aliases,0086

which are going to make your SQL statements cleaner-looking.0093

And then, also, we are going to go over required homework, which is going to have you set up the tables in your web store database0096

so that we will be able to run our next version of the web application, which is going to store orders within the MySQL database.0105

The first thing we are going to talk about, again, is a new data type called DATETIME.0114

It is a MySQL data type, and it allows you to store a timestamp.0119

For example, if we wanted to create a table called Orders, it might have a number of different columns.0123

And this is an abbreviated CREATE TABLE statement.0129

Let's say we have an order ID that is an unsigned integer, and I left it out...maybe it is a primary key that is on AUTO_INCREMENT.0131

But that is just one column.0137

And then, let's say we have a column called dateTimeOrdered, which is going to be the date and time that an order was processed.0139

Well, we can store that as a data type called DATETIME.0147

And what that does is stores a date and time stamp for that column.0152

For example, in MySQL, it stores them in this format here, where you have the four-digit year, two-digit month, and two-digit day,0158

and then you have two digits for hours, minutes, and seconds.0170

And what you can do is: we are going to be creating a timestamp when an order is processed.0174

Currently, we had been using the date function within our emailOrder function to generate a timestamp on our order.0180

For example, when we place an order in our current web store, it shows you a date.0189

It says your order was placed at so-and-so time.0194

Well, we are still going to use that date function, but we are going to be storing it in the database.0197

We are going to have to specify how we create the date string, because for MySQL, it needs to be in this format here.0201

And we are going to be using date...as we know, with the date function, you provide it some sort of formatting string.0211

that is going to allow you to describe how you want the date stored.0219

When we add an order to our database from PHP (which we are going to see in our next web application),0224

we are going to be using the date function to generate this date string that we are going to store in the database.0229

One thing to note is that you could also just store dates as regular strings in the database.0235

For example, this right here is just a regular string, in essence.0240

You could have...and just have that be a string, and then store that in your database, and be able to extract information from it.0244

Well, there is a distinct advantage to using the DATETIME data type, as opposed to maybe using a VARCHAR, or...0259

you could use even a CHAR for this type of data, because it is going to be of a known length.0267

in that, because MySQL will know that the column data represents a DATETIME and is not just a string, you can perform operations on it.0274

For example, you will be able to do things like: let's say you want to query your database0284

and say, "Give me all the orders that happened in the last month."0291

Well, MySQL will be able to use the fact that a particular column is a date/time column to be able to develop a query with results based on criteria you specify.0294

If you had just specified it as a VARCHAR, for example, MySQL won't be able to do that.0306

How could we model orders in our database? Orders contain a bunch of different information.0314

We know that, when we create an order, it has items in the order; it has the quantity of those items.0320

And then also, the order involves a customer submitting their name and shipping information.0327

We also just found out that, as we had in our email, we have had a timestamp for our particular order.0333

So, there is a bunch of different information, and we are going to see that, as in our Departments, it doesn't necessarily fit all into one table in our database.0339

If we look at a couple of ways to modeling it...0348

Let's say we had a table called Orders in our database: maybe we store an order ID;0350

we would store the date and time of the order; and then, we could store information, like the customer that ordered it:0360

we would store their name, and let's just simplify--rather than putting all of the different columns we have,0370

like city, state, and address, let's just say address.0376

And then, maybe we would have item 1, and then you could list the item number, and then item 1 quantity, item 2, and so on.0381

And so, this could be the structure of our Orders table.0399

Now hopefully, you will remember from our lesson where we talked about linking tables and departments0408

that this is not normalized data, in that, if we have a column for every item that we want to add to a particular order,0413

then it limits the number of items we can have in a particular order to the number of columns in the table.0420

So, we constantly have to be updating the size of our Orders table to add columns for new items.0424

For example, maybe it would be order ID 1 (I'm just going to put 2011), blank, just some string...0430

maybe item 1 is 1001 and the quantity purchased was 2; maybe item 2 was 1003, and so forth.0438

Well, we know that that is a problem, and we can normalize that.0446

One thing that we are going to do with our order database is: we could change the structure a little bit.0450

So now, we would have two tables: we would have one called Orders, which would contain all of the information except for the items.0457

And then, what we can do is create another table called orders_items, which would be a linking table,0476

which we learned about in Departments, where we would link multiple items to a particular department.0482

And here, we could have two foreign keys that make up a primary key to the table.0486

We could have an orderID and an itemID.0493

For example, order 1--we could have an entry that says item 1001 was part of order 1.0497

For each additional item that is in order 1, you could add information.0504

Additionally, because we have a quantity associated with an item and order, we are going to have a quantity column.0508

So, we could say there were two 1001 items in order #1; there was 1 1002 item.0514

And so, now we have normalized our database, and we can add as many items to a particular order as we want,0523

without having to change the structure of our database.0529

Additionally, one other thing I will mention while we are here is: we are also going to add additional features to add a price column,0532

because as you know, at the store, sometimes the prices of items change, and so forth.0542

So, we would probably want to note in our database what the price of a particular item was when it was purchased.0547

So, maybe we would say that this one was priced at $50 at the time of the purchase; this one was priced at 45.0552

So, even if we had...maybe there was order #2 that had 1001; maybe they purchased 3 of them;0558

but there was a sale that day, so the price was down to $40.0565

Now, we can incorporate that information into our table.0569

So now, we have this order table that has information about the order, which is the customer information,0573

the date and time of the order, and its ID: and then we have another linking table that contains all the order information.0579

One other thing that we are going to add to our Orders table, just so you will be aware, is:0586

we are also going to add a tax rate column.0591

And the reason for that is: we are going to put the tax rate that was used when a particular order was made,0596

because the tax rate can change; the idea being that, when you look up an order in a database in the future,0600

you want to be able to figure out exactly what you charged for tax, how much each price was, and so forth.0605

Now, we could just create a column called total and store the total of the order;0612

but another thing with database design is that typically, you don't want to store anything that you can calculate,0618

because in a way, it is kind of redundant.0624

If we have all of the information about the prices of the items and the tax rate, we can calculate the total.0627

And therefore, we don't need to have that extra information in the database, because we can already generate it.0631

So, in that effort, we are going to include a tax rate column, as you will see in our Orders table.0640

Now, one thing that we can do is further normalize our database by moving customer data into its own table.0648

For example, we saw in the last version of the Orders table: each order had the orderID, the tax rate,0656

the date and time stamp, and then the name and address of each customer.0663

And so, if we flushed it all out and showed all the different columns for a customer, and so on,0667

we could see that we might have order 1 (just write down a fake date), 10% tax rate,0706

Joe Smith, who lives at 101 Main Street, and doesn't have an apartment number; and we could put a city and state.0713

Well, typically, hopefully, for our store, we want multiple customers to come back to the store.0722

So, we would like to have Joe be able to make more than one order.0728

So, let's say Joe makes another order, order #2 for Joe Smith.0731

He has the same address as before, so now we can see that we have redundant data in our tables again.0739

And as we learned in our lesson on linking tables, part of normalizing a database is to reduce redundant data,0745

because right now, we have all the information about Joe Smith and his customer address, listed in our table twice.0754

So, we have it taking up more space than we need to; we could separate it out and put it in one spot.0762

Additionally, if Joe changes his address, and we need to know that for the future--we want to update that on our orders--0768

we would have to go and update each individual row that Joe has.0775

Instead, what we can do is create a link to another table and extract that information out and put it into a Customers table.0779

So now, we can generate a primary key for our Customers table called custID0791

that will have all of that same information, except it will have this additional custID column.0808

So now, if Joe comes back to the store, we can update his address in one spot.0823

And what we can do is erase all of the customer data from this table.0828

And instead, we will include a foreign key column that is going to be a foreign key to this custID.0837

So, we are going to say custID=1.0842

So now, when we look up information about an order, we look at the Orders table.0853

We see that order 1 was ordered on this particular date, with this particular tax rate.0857

We can see that the customer that ordered it was a customer associated with ID number 1.0861

So then, we could look up the information in the Customers table and find out all of Joe's information.0867

And we could do that for every order that has custID #1.0872

Additionally, as we saw in the last page, all of the information about the items in a particular order is stored in our linking table,0875

where we have order #1, item 1001, quantity, price, and so forth.0883

We have all this information spread into three different tables: it seems more complicated, but it makes our database, in many ways, more efficient.0891

And the reason that we are bringing that up in this lesson is because now,0901

when we want to get information about an order (for example, we are going to be adding orders to our database),0905

for the purpose of our website administrator being able to go into the admin site and look up all the orders that have been placed;0909

well, in order to do that, we are pulling information from a number of different spots.0919

So, that is where the concept of a join comes in, which is where you can query data from multiple tables.0923

When we want to find information about order #1, not only do we want the information in the Orders table,0930

but we also want information about the customer, Joe Smith.0935

So, we are going to be creating a query that, in one result set, is going to pull information from the Orders table, as well as the Customers table.0938

Because there is only one customer associated with each order, we can do that in a single result set.0946

We can't do that with orders_items, so we are not going to be performing a join, as we will learn about, on the orders_items table,0952

because there are multiple items associated with each order, and there is not really a good way to do that with JOINS.0959

But for our customers, it is going to work out; and this is something that you will be commonly using,0967

as your databases get more complex, where you will be pulling data from multiple spots.0973

Now, I am just going to walk through the creation of the tables that we are going to have to represent orders in our database.0980

I have already logged into our MySQL Monitor.0987

So, let me create the table Orders; we are going to do a typical unsigned integer auto-increment for our primary key.0995

We are going to have a dateTimeOrdered column, which is going to be the date and time that an order was made.1018

That is going to be our new data type that we learned about, DATETIME.1023

We are going to have a tax rate column; it is going to be the tax rate that was used at the time the order was placed.1028

And for that, we are going to use a floating-point number that is going to be three digits long, all of which are after the decimal point.1036

So, we can have a three-digit percent amount (like .0975 would be 9.75%, and so forth).1047

And so, that is going to allow us to pull our tax rate.1064

And then, we are going to create a custID column, which is going to be a foreign key that is going to link to our Customers table.1068

And because we are going to be using an unsigned integer as the primary key for that table, we would list that as the same data type.1076

So, now we have created our table, which is going to look like this, with these four different columns.1084

We are going to create the Customers table to store the customer information, so that we can have multiple customers making multiple orders.1095

And then, we are just going to add all the different fields for our customer.1118

Now, we are just using variable-length strings for all of these different fields, because they are going to be able to vary--1152

except for, in particular, our state and our ZIP code: we know that the state is going to be two characters long,1164

and our ZIP code will be 5 characters long, so we are going to use CHAR data types for those.1169

And now, if we look at our Customers table, we can see that, if we remember our web application,1177

when a user submits information when completing an order, they put in their first name, their last name, and then their address.1187

And this is going to embody all of that information.1192

And then, finally, we just need to create that linking table that is going to link items to a particular order.1195

And we are going to have a double-column primary key, because an item can only be in an order one time, so that is a unique combination.1210

And we are going to give them the same data types that the primary keys have in their respective tables.1222

So, we have an orderID; itemID is going to correspond to the ID of an item in our store.1228

And then, as mentioned, we are going to have quantity, which will just be an integer,1239

and then price, which is the price of the item at the time of the purchase--we will have it be the same as the price column1244

in our Items table, which is a decimal of seven digits long, with two after the decimal point.1252

So now, we have our three tables set up that are going to be able to hold all the information about our orders.1265

And we can see here that we have...1272

Oh, and the one thing I forgot was: let me go ahead and get rid of...I forgot to assign a primary key.1275

I'm just going to redo this statement; and now, we can see that they are the primary keys.1295

So now, we have this set up to be able to add orders to our database.1317

What we are going to learn about now is how to query information from these multiple tables.1321

And what we are going to be doing, as part of the required homework for this lesson, is making the same setup1325

on your own home database, so that you will be able to use our new version of the web application.1331

That is going to give you additional practice with using CREATE TABLE statements.1335

And it is also going to give you a better understanding of how the tables are structured to work together.1339

A join, as implicitly mentioned, is a way to extract data from multiple tables in a single SELECT query.1348

For example, when we want to pull up an order, we want to pull information from a row in the Orders table;1359

and then, we also want to pull information about the customer associated with that order.1364

Now, we could do that in two separate queries: we could run a SELECT query on the Orders table,1369

and say, "Give me all the information about order #1"; then we could pull out of that data result set the ID1373

of the customer that is associated with that order, run a SELECT query on the Customers table, and then pull that information.1379

But JOINS allows us to do it all in one query, so we can have one result set that we can parse over.1384

The basic, most common type of join is called an inner join, and it uses the inner join SQL keywords.1390

And this is what a JOINS query looks like: it says, "Select all the columns from the Orders table that are joined with the Customers table."1399

Now, what this query means is: when you just have a query that is just like this, where you specify the name of one table,1410

and you specify that it is an inner join, that it is joined with the other table, the result set1419

is going to be a combination of all of the rows of the Orders table, combined with all of the rows from the Customers table.1425

For example, if the Orders table had five rows in it, and the Customers table had two rows in it,1431

our result set from this query here would be 10 rows, and it would be a combination1438

of each of the five orders, with each of the rows in our Customers table.1444

It is a little abstract, so let's look at a quick example.1450

If I create a table called Employees that is just going to have an ID for the person and a name,1455

and then we are going to link them to a particular department; we are going to use a foreign key from another table.1486

We are going to link them using a deptID, and let's go ahead and put someone in this table.1490

Move it up a little bit...1514

We have Joe Smith that is going to be in department #1, and then Jane Jones, who is going to be in department #2.1541

And just to look at what our Departments table looks like, if you don't remember: we have three departments1547

in our default setup: Apparel, Electronics, and Sporting Goods.1554

So now, let's say we are going to show you what an inner join would look like if we join these two tables (Departments and Employees).1558

We do SELECT, and we are going to select all of the columns from Employees, and we are going to join that table with the Departments table.1568

And as you can see, we have six rows in our resulting table, because we have three rows in our Departments table and two rows in our Employees table.1583

And what that inner join does is creates a giant table that matches each row in the Employees table with each row in the Departments table.1593

So, we can see that Joe Smith (actually, let's do this) is one entry in our Employees table.1604

But he is matched up with each different department, so there are three rows here:1625

one matching Joe Smith up with department 1; one matching Joe Smith with department 3, department 2,1629

and so forth; and the same thing for Jane Jones.1633

And so, that is what an inner join is: it creates a combination of all the rows in all the different tables.1636

This particular case is not very useful, but what we are going to learn in the next slide1643

is about something called the join condition, that is going to allow us to define information like...1646

typically, what we would want to find out would be information about Joe and the department he is in.1652

And so, what we can do is: out of this table of information, this Join table, we can restrict the rows that show up.1657

For example, we would only want the row where the deptID in the Employees table matches the deptID in the Departments table.1664

And that would give us, say, in our same query...if we ran that query, we would be able to get1674

the information about Joe Smith and the name of the department he is in.1678

We don't really care that he is matched up with the row that matches him up with department 3, number 2.1683

We want it where they are the same; so, we are going to learn about that in the next slide.1687

A join condition is pretty much like a WHERE condition; it specifies which rows you want from a result set.1696

We learned that the inner join has a giant table result set; it combines all of the rows from all of the different tables into a giant table.1703

And what we can do is: we had the same query as before, SELECT * FROM Orders, inner join, Customers.1712

We can add what is known as a join condition, that is specified by the keyword ON.1719

And what we are going to say for this particular join would be, "After you combine all of the rows of the Orders table1724

with all of the rows of the Customers table, only give me the rows1732

where the custID of the Orders table matches the custID of the Customers table."1736

And that comes up because, in our Orders table, this is the order ID, the tax rate, the date...and then, we have a customer ID, for example, custID 2.1744

So, if we did a join on both the Orders and Customers table, it would pull up a table that--1760

if there were five different customers, it would have information about each customer associated with this order.1766

So, it would say order 1, and it would match it up with customer 2, and customer 1, and so forth.1774

Well, we are interested in where the customer ID is matched up.1783

So, in this join condition, we get rid of this row from that join.1786

And it would say, "Only give me the rows where the customer ID's of those two tables match up,"1792

because that is what we use to link the information from those two tables together.1796

Because JOINS queries involve multiple tables, you need to be able to specify specific columns from specific tables.1801

And that is what we have done up here.1809

And the syntax for that is: you specify the table name, followed by the period, followed by the column name.1810

And so, that is what we used in our clause here, where we said the custID column of the Orders table is equal to the custID column of the Customers table.1816

And they work the same way that a WHERE clause does: you can have AND statements; you can have OR statements.1824

So, you can add multiple conditions that further define which rows you would like to receive.1831

For example, to perform a more practical, useful query, using a join on this Employees table and Departments table1836

that we just set up, we are going to say, "Select all of the columns from the Employees table and the Departments table;1845

join them together," so we have this giant table; and then, we are going to specify a condition.1851

And we are going to say, "Only give us the rows where the deptID column of the Employees table matches the deptID of the Departments table."1858

And when we run that, we are going to see that we get two rows.1875

We only have two employees; each one is only assigned to one department.1878

So, as you can see, what it has done is pulled the information from the two different tables.1883

You can see that, from the Employees table, it pulled the employee ID, the name, and the department ID that it is in.1889

And then, because we specified a star here, it pulls all of the columns from the Departments table, as well.1896

We have the department ID and the name from the Departments table.1902

And you can see that these two department ID's match up; the same thing for Jane Jones.1906

So, that is how we would pull information together.1909

And that is how, in our web application, we are going to pull information from the Orders table, and then also pull information from the Customers table.1912

Now, one thing you will notice here is: because we have used the star...1920

that star implies, "Give me all the columns from the first table (Employees), as well as all the columns from the second table";1924

because they have this matching column, deptID, it shows up twice, because they both have it.1930

And we are not necessarily interested in that; we are just interested in the other information--1938

for example, what might be related, or might just be that we only want to see it once.1941

So, what we can do is change our SELECT query.1945

And instead of specifying all of the columns; whereas before, we know that we could specify, for example, name,1951

and that would give us the name of the Employees table; well, because we are using multiple tables now,1956

we are going to specify the table name, followed by the column name that we want.1961

So, let's say we want the name of the employee from the Employees table, and the name of the department from the Departments table.1968

I'll try to get this...it doesn't look like it is fitting on one line.1986

What this is saying is, "Give me the name column of the Employees table and the name column from the Departments table..."1989

"from the join of the Employees and Departments table, where employees.deptID equals departments.deptID."1996

And so, now we have the name of Joe Smith, who is in the Apparel department, and Jane Jones in Electronics.2016

So, that is another way that we can use this table name/column name syntax within our query.2022

Not only can we use it in a join condition, but we can also use it to specify what columns we want to get.2027

And so, that query made use of both an ON condition, as well as specifying what columns we want.2035

Now, one other thing that we could do is: typically, when we are doing this,2042

we also want to only maybe pull up the particular user.2047

We want to find out what department Joe Smith is in.2050

So, if we know Joe Smith has employee ID number 1, we can also add an AND clause to our join condition,2052

that says, "Also, only give me the rows where, in the Employees table, the employee ID equals 1."2060

And that is going to pull up Joe.2071

And so, as you can see, these queries are getting more and more complex.2072

But it allows you to pull a lot of information together.2076

We learned the explicit method of specifying a join between tables.2083

There is also something known as an implicit join, which is just a simpler syntax for specifying2087

that you want to inner join two tables and pull data from both of them.2093

The way you do that is: you simply say SELECT; you list the columns you want; and then, you say FROM all the tables you want.2097

So, when you say FROM, and then you list two tables separated by a comma, that is going to perform an inner join on those tables.2105

So, that would be the same thing as if we had written FROM Orders inner join Customers.2111

This right here is the same thing as this; it is a little less syntax-y with advanced words--it is a little more intuitive.2137

And then, instead of an ON clause, we can specify a WHERE clause, which is the same as our join condition,2146

where we had ON orders.custID equal customers.custID; we now do that with fewer words.2151

For example, this same query that we just ran, where we pull the employee's name and department's name,2159

and we have Employees joined with Departments, and we get that join of both of those two tables...2173

Actually, let's add a join condition, so that it limits the results.2181

We have our two different rows here.2193

So, we could write this query right here that is an explicit join.2195

We have explicitly specified we want a join between Departments and Employees.2199

Just another format--another syntax for doing that--would be...2203

And by separating the table names we want to join by comma, we are specifying that we want those joined.2214

And then, we are just saying, "And then, from that join, give me all the rows where..."2220

And we can see that we are able to get the same information.2235

Implicit joins are just sometimes simpler, and it is more intuitive syntax for joining two different tables for a query.2239

One other thing we are going to learn before the end of the lesson, that is related to using joins, is something known as aliases.2250

As you can see, our queries are starting to get longer and longer and more complex.2257

One of the things that SQL allows you to do is specify aliases, which are, in a way, kind of like a variable name,2262

or a shortcut--a shorter name to refer to a specific table.2268

For example, we could say, if we wanted the order ID, the first name, and the last name of our customer,2273

from the Orders table that is joined with the Customers table, where the custID of the Orders table2281

equals the custID of the Customers table, what we can do is: when we list Orders, and we list the table,2286

we can use an AS clause afterwards, and say AS o.2292

So, what this is saying is, "Select these columns from the Orders table, which, in this particular query, has an alias that can be used for it, which is o."2296

So, this is the same thing, right here, as Orders.orderID.2306

And what it does is that the less typing that we have to do makes our queries a little simpler.2314

And we can do the same thing for the Customers table: we are going to say, "We are going to refer to the Customers table within this query as c."2318

So, this is going to say, "Get the firstName column from the Customers table and the lastName column from the Customers table."2323

And this is going to say that the join condition is going to be "where the custID of the Orders table equals the custID of the Customers table."2328

So again, looking back at our other query that, as you can see here, was rather long, we can go ahead and change it a little bit.2337

We are going to call our Employees table e; so I am going to give you the name from the Employees table.2347

We are going to call our Departments table d and give you the name from the Departments table.2351

Actually, let's do a regular integer...2363

So, we are going to use e as the alias for our Employees table, and we are going to be able to pull up the same data.2368

Now, you might say, "Well, that query is almost as long as the other one is."2391

But let's say that we have 10 different columns we are pulling from a particular table; that is significantly going to shorten the length of the query.2395

Also, we might have many more join conditions.2404

For example, if we add one more join condition where the employee ID equals 1, so we are just looking up Joe,2406

that is another spot where we can use it, as well.2422

And so, it is just going to significantly shorten the queries, in a way to make them a little bit easier to read and write.2424

And so, that is the purpose of aliases.2430

For the required homework, I am going to have you create the three tables that we are going to need2434

to use orders in our database, the ones that we learned about in this lesson.2443

You are going to create a table called Orders, just like we did here.2446

It is going to have four columns: orderID, dateTimeOrdered, taxRate, and custID (which is a foreign key that is going to point to a row in our Customers table).2448

So, we can have multiple customers linked to multiple orders.2458

You are going to create the Customers table, just as we had done.2462

It is going to have a primary key column, which is custID, which is what we are going to use to link an order to a customer.2465

It is going to have all of this different fields containing information about a particular customer.2471

And then finally, we are going to have our orders_items linking table, which is going to allow us to link multiple items to a single order.2478

And so, the primary key of the table is going to be a combination of the orderID column and itemID, which, in this particular table, are foreign keys.2485

The orderID is a foreign key, because it refers to a primary key of the Orders table.2493

The itemID is a foreign key, because it refers to a primary key of the Items table.2500

So that way, we can uniquely link a unique item row to a unique order row.2504

And then, we are also going to specify the quantity, which is going to specify the quantity of this particular item in this particular order,2510

and also the price of this particular item when this particular order was placed.2516

And so, when you go ahead and set that up and run these CREATE TABLE statements,2522

you will be able to have your database set up for the next web application version that we are going to be going through.2526

That ends today's lesson; thank you for watching Educator.com, and I look forward to seeing you next time.2531

Educator®

Please sign in to participate in this lecture discussion.

Resetting Your Password?
OR

Start Learning Now

Our free lessons will get you started (Adobe Flash® required).
Get immediate access to our entire library.

Membership Overview

  • Available 24/7. Unlimited Access to Our Entire Library.
  • Search and jump to exactly what you want to learn.
  • *Ask questions and get answers from the community and our teachers!
  • Practice questions with step-by-step solutions.
  • Download lecture slides for taking notes.
  • Track your course viewing progress.
  • Accessible anytime, anywhere with our Android and iOS apps.