Matthew M.

Matthew M.

Linking Tables

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!

Linking Tables

  • Database normalization is a process that involves structuring a relational database to help reduce common problems involving:
    • Difficulties in searching data
    • Having large numbers of empty fields
    • Needing to re-structure tables frequently
    • Redundant storage of the same data
  • A foreign key is a column in one table whose value represents a primary key of another table.
  • A linking table contains two or more foreign key values in each row that serve to link one row of one table with one row of another table.
  • Tables can use more than one column to comprise its primary key. Such a table is defined using the following syntax:
    CREATE TABLE depts_items (
    deptID INT UNSIGNED,
    itemID INT UNSIGNED,
    PRIMARY KEY (deptID, itemID)
    );

Linking Tables

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:16
    • Lesson Overview
  • A Departments Table 1:08
    • Departments Table
    • How Could We Model This in a Table: Option 1
    • How Could We Model This in a Table: Option 2
  • Database Normalization 4:37
    • Database Normalization
  • Foreign Keys 8:47
    • Foreign Keys
  • Linking Table 11:26
    • Linking Table
  • Multiple Column Primary Keys 15:30
    • Multiple Column Primary Keys
    • Example
  • Required Homework 22:34
    • Required Homework: 1 - 3
  • Required Homework (cont.) 24:10
    • Required Homework: 4

Transcription: Linking Tables

Hello again, and welcome back to Educator.com's Advanced PHP with MySQL course.0000

In today's lesson, we are going to be talking about what is known as a linking table, which is a type of table in a database0004

that is going to allow us to move our departments database from a file-based system over into our MySQL database.0010

We are going to talk about the concepts of how we could model our departments in the database table,0018

which is going to progress us forward into this discussion of linking tables, which is the goal of this lesson.0024

As part of that discussion, we are going to be talking about a concept called database normalization,0031

which is a way to make your database work and act more efficiently.0036

One of the topics related to that is something known as foreign keys.0041

Then, we are going to talk about what a linking table is, and then also (related to a linking table)0045

how you can have a table with a primary key that is comprised of multiple columns.0050

Then, we are going to go over required homework that you are going to need to do, to set up your advanced_php database,0056

so that you can run the future versions of our sample web application.0064

We know that a Department object from our web application has three properties.0070

It has a department ID and a name, and then it has an indexed array of item ID's, where each item ID represents an item in the department.0079

How could we model this in a table?0088

There a couple of different things that we could do.0091

For example, we could create a table that maps one column to each property of our object.0092

We could have a column called deptID, which would be the primary key of the table: it is a unique ID for each department.0099

We could have a column called name, which would be the name of the particular department.0105

And then, we could have a column called items, which would be used to hold the information0109

about all of the different items that are in our particular department.0114

In this case, as has been the case in our departments.txt file, we have been storing all of the item information in a comma-delimited list0117

that shows all of the items in the particular department.0127

We could simply move that comma-delimited string into this items column of our department table,0130

so that here would be a department with three items, and here would be a department with two items, for example.0138

The problem with that that we are going to talk about in the next slide with database normalization is: it makes it difficult to search the database.0144

One of the reasons we use relational databases is because of the way it can quickly search information.0151

Well, now in order to look up an item, to see if, for example, item 1004 was part of department number 1,0156

we would have to pull this column up, using the SELECT query from the database,0163

and then parse through that, as we had been doing in our departments.txt file, extracting each of the items' ID's in that,0169

using, for example, the explode function that works on separating items by the comma delimiter.0177

And it just adds another step.0183

So, it takes away our advantage of using the database to automatically search for things.0185

Now, we have to write some manual code to parse that item string.0188

Another option, you might think, would be: let's have a deptID column again and a name column again.0194

But then, this time, we just create a column to store the item ID of each item in the department.0201

So, we would have a different column for each item in the department.0210

So, we might have one called itemID1, itemID2, and itemID3.0212

In this particular case, the first item, if it was the same exact data as up here, would have three values: 1001, 1004, 1007.0218

And so, all of those columns would be filled up.0227

However, our Electronics department only has two items, so it has this empty column here.0230

Well, let's say...right now, this limits us to only having three items in our database.0236

So, if we want it to be able to have a fourth item in the department, we would have to add another column over here,0240

which is going to involve restructuring our table, which is a procedure you want to avoid, if possible.0245

Additionally, if we add a fourth item (for example, 1008) to our first department, and it still only has two items for department #2,0253

now we have two empty spaces in our database.0263

And so, this is stuff that takes up space in our database.0266

And one of the goals is that you want to avoid having a lot of empty space within your database.0272

That is going to bring us to the concept known as database normalization.0279

And that is a process that you go through to structure your relational database to help reduce a number of these problems we have been talking about.0283

We talked about the difficulty with searching data.0292

It is under the concept called atomic values; in our first table that we had, where we had three item ID's all in one column,0297

that is considered non-atomic; atomic means a single value.0308

So, we had three values contained in one column, which makes it difficult to search.0311

If we have only one item ID in each particular column, that is known as an atomic data value; it makes it easier to search.0315

Database normalization is going to help us change the structure of our table, so that searching is easier.0322

It is going to avoid having a large number of empty fields.0328

We saw, for example, in the second table that we had, where, let's say, we have a new column for each item,0330

so that we can have up to four items, that if one of those departments only has one item in it, it is going to have a lot of empty columns.0337

And that is something we want to avoid in our database.0343

Normalization is going to help with that.0345

It is also going to avoid the problem of having to restructure our tables frequently.0348

As we saw before, the second table originally started so we were only able to have three items in a particular department.0352

Well, ideally, we would like to have as many items as we want in a department,0358

which means every time we wanted to add an additional item to a department--let's say right now our database enabled a department0360

to hold 5 items; well, now maybe we want 8 items in there--we need to restructure our database; and we have to continually do that.0369

Normalization is going to help us avoid having to do that.0375

And then, something else that wasn't mentioned on those other slides that it is going to help us avoid is something called data redundancy.0378

Let's say, for example, we had a table called Orders that represents an order that a user places on our web store.0384

Well, maybe we have a primary key for the Orders table called orderID, which is a unique ID for that particular order.0397

We would store the amount of the order in a column.0403

We would store the customer's name, and then their city and state.0406

We would probably have more information here, like address, apartment number, and stuff like that.0410

But because of the size of this slide, we are just going to limit it to this; but I think you can see the point.0413

Let's say, for example...one of the things about having customers on our website, or people placing orders on our websites,0419

is that we might have the same customer place multiple orders.0425

And that is something we would want.0429

For example, let's say our first order was placed by Joe Smith, from Houston, TX, for $150.0431

Then Jane Jones comes along, and she places an order for $200, and she is from Boston, MA.0436

Joe Smith comes back to our web store; he says, "I like this store; I want to buy something else."0441

So, we add another row to our Orders table.0446

It is going to have the amount that is ordered, and then it is going to have Joe's name and his address again.0449

Well, this right here is redundant data; assuming it is the same customer (because there could be two Joe Smiths from Houston, TX),0455

we have that same data in our database twice.0463

For example, let's say we wanted to update Joe Smith's address information.0467

And let's say it was stored in this Orders table.0474

We would have to go through, and we would have to update this row here, and we would have to update this row here.0476

Maybe he becomes from Boston, MA.0480

So now, because we have the same data in multiple spots, we have to make multiple updates.0484

And also, what it does is: now, we have this information that is stored in our database that takes up space on a server.0489

This data is stored in memory somewhere, so we have the same data stored in two different spots,0496

when really we could have it stored in one spot, which we are going to see on the next slide.0505

And theoretically, maybe Joe could place 10 different orders; now, we have this same information in 10 different spots within our Orders table.0511

We have the information repeated multiple times, and then any time we want to go and update it, we have to update it in multiple spots, as opposed to one.0518

Well, there is a way we can improve on that, and it uses a concept known as a foreign key.0524

What a foreign key is: it is a column in one table that refers to the primary key column in another table.0532

For example, let's say we changed our Orders table so that it would just have the orderID; it would have the amount;0538

and then, it would have this other column called custID.0546

And you can see that it is in italics; typically, italics, in these database descriptions, represents a foreign key.0549

And what this customer ID is going to do is refer to the primary key of a row in another table.0555

For example, if we instead move all of the information about Joe Smith and Jane Jones into its own Customers table,0563

we can create a new table called Customers that has a unique ID for each customer, which would be the primary key of that table.0570

And then, it has columns for their first name, their last name, their city, and their state.0577

So now, what we can do is: instead of having to have Joe Smith from Houston, TX written out here and here in our Orders table,0582

we simply can use the primary key ID from the Customers table.0591

And we can insert it into this foreign key column of our orders database.0598

And what that is going to do is link this order to Joe.0603

So now, what we have is: we have Joe Smith's information, but instead of having it twice in the table,0609

by having his customer ID in two spots in this customer ID column of our Orders table,0613

they both can link to the same spot; we have it in one place.0620

If Joe changes his address, we only need to update it here.0623

Additionally, we don't have that in two different spots.0628

That is what a foreign key can be used for.0630

One thing to note is that, if you are going to use a foreign key (which, again, is a value that represents a primary key in another table,0633

so we know a primary key value indicates a unique row in a table), if this is a foreign key,0643

it is going to indicate a unique row in another table (in this case, a unique row in Customers).0648

You just have to make sure that the data types match up here.0654

For example, if the custID primary key column of our Customers table was an unsigned integer,0657

then you would want the foreign key column to also be an unsigned integer.0664

You wouldn't want it, for example, to be a VARCHAR.0673

You want the types of those to match up.0679

And so, that is how a foreign key can be used to link one row in a table to a row in another table.0680

We are going to expand on that a little more, to come up to the concept that this lesson is mainly about,0687

or the ultimate goal of this lesson, which is known as a linking table.0693

What a linking table is: it is a table where each row of the table contains two or more foreign keys, representing rows in another table.0696

That is probably best explained by an example.0707

Let's say we create a Departments table; and this is the goal of this lesson--0709

we are trying to efficiently model our Departments table in our database.0713

And as we saw earlier, the way we were doing it in the first slide, it wasn't really an efficient method.0718

And we saw, from the rules of database normalization, that there were a lot of problems with that.0723

This is a solution that is going to solve that problem.0727

Let's say we create a Departments table that, instead of having an items column, is just going to have two columns:0731

a primary key column for deptID, and then the name of the department.0740

Well, we know, also, that we have an Items table in our database that has a primary key itemID,0744

has a name, has a price...I have left off the rest here, just so this would fit on the slide.0751

But we know that we can identify an item in the Items table by its primary key.0755

Similarly, we can identify a department in this Departments table by its primary key.0760

Well, now what we can do is: in order to add items to a department, we can create a linking table.0766

And in this case, it is going to be called depts_items, and that is typically the case for linking tables:0774

the name of the table contains the names of the two tables you are trying to link together.0780

And what we can do is: for each item in department 1, we can add a row to this table.0785

We can say, "Department 1 contains item 1001."0790

Now, deptID 1 is a foreign key that is going to refer to the primary key of the departments table.0795

Item 1001 is a foreign key that is going to refer to item 1001 in the Items table.0804

Now, what we can do is continually add, for each item in the department...0812

We want to add item 1004 to department #1; we simply create a new row0815

that has the department ID of the item we want to add to, and then the item ID.0820

The same thing we can do for department 2; we can add two items to it, as well.0825

Now, what this allows us to do is: now, we can add as many items to a department as we want.0828

We could have department 1 have 4 different items in it, whereas department 2 only has two items in it.0834

That is going to keep us from having to restructure this department table by adding a new column every time we want0843

to increase the capacity of the number of items in a column.0849

Additionally, it reduces the empty space in a table that would be stored for item 2.0852

Whereas it only has 2 items, if we had a table that would allow 4 items to be in a department,0859

there would be, for department 2--the row for that department would have 2 empty spots in it.0863

So, we have eliminated that empty space that is not needed.0867

And then also, we have allowed our application to grow to where we can put as many items in a department as we want0871

without having to restructure our particular table.0877

And so, this is how we are going to model our departments in our database.0881

We are going to create this Departments table that is just going to have the ID and the name of the department.0884

And then, we are going to have this separate linking table that we are going to use to add items into a department.0889

And the way we do that is: we use the primary key of the deptID and the primary key of the itemID we want to add to it,0898

and put those in the same row in this depts_items table.0906

And again, you can see that they are both italicized here; these are both foreign keys,0909

because they refer to primary keys in another table.0914

This row right here, again, is going to uniquely link one row in our Departments table to one row in our Items table, and so forth.0917

As we know, with our tables, we want to have a primary key for our table.0933

And what we are going to explain now is that, in that depts_items table, our linking table, it is going to have a primary key that is composed of two columns.0938

We know that a primary key is used to uniquely identify a row in a table.0947

Well, if we know that, let's say, we have the rule, which would make sense, that each store item can only be in a department once.0951

Then, what that is going to say is that, in our depts_items table, each deptID can only be matched up with an itemID one time.0960

Each of those pairs is unique.0971

Now, because they are unique, and they are the row of the table, we can use those to uniquely identify a row in the table.0973

We can say, "I want the row from the table that has deptID number 1, and then also has itemID 1001 with it."0979

That way, we can uniquely locate that row in the table, so we have a primary key0987

where the key of the table involves two different columns.0992

So, for our linking table, both of those columns (deptID and itemID) are going to comprise the primary key for that table.0997

Now, the way you do that--the syntax for that--is: in our CREATE TABLE SQL statement,1006

we define our deptID and itemID columns as we normally would any other column.1013

Notice that we have the type INT UNSIGNED, and here, because this is a foreign key that matches a key in another table,1020

we want it to have the same data type as the other table.1027

Well, in our Departments table, you will see in a minute: we are going to create deptID as an unsigned integer.1029

And we know, from our itemID, that it is an unsigned integer, so we give both of these data types of unsigned integer.1035

And the way that we signify a multiple-column primary key is by adding, after all of our column definitions, this phrase 'primary key.'1040

And then, we include in parentheses a comma-delimited list of the different columns that we want to use1049

to represent the primary key for that table.1054

What this is going to do is: if, let's say, we have some data from our depts_items linking table;1056

when we define this as the primary key, this is going to let MySQL know that each of those pairs1072

of deptID and itemID has to be unique, and it will help us to enforce the fact that an item is in only one department.1078

So, if we try to, for example, run an INSERT statement (and I'm just going to abbreviate it),1083

with the values 1 and 1001, MySQL is going to complain and say it can't be; a row with that primary key already exists in the table.1094

And so, that is going to allow us to be able to enforce that.1105

Let's go ahead and run these CREATE TABLE scripts that are going to set up the ability to add departments to our MySQL database.1108

It is going to create the Departments table and this depts_items linking table.1119

And that is what you are going to be doing for your homework: mimicking what we are going to be doing here,1124

so that your MySQL database at home will work with our web application.1127

First of all, I have logged into the MySQL Monitor.1135

The database I am currently using is the advanced_php database, which is our database.1140

And if I show the tables that we have, we can see that there is one table currently, Items.1145

Well, let's go ahead and create our Departments table, which as we just saw, is just going to contain1149

a primary key column, which is deptID, and then a column for the name of the department.1154

We can write a CREATE TABLE statement and call our table Departments.1161

We are going to have the first column be deptID, which is going to be an unsigned integer.1170

It is going to be auto-incremented, just as our Items table has its itemID auto-incremented.1178

We are going to denote that that is the primary key of this table, so each deptID is going to uniquely identify a department.1184

And then, we are simply going to have a name column that is going to be a variable-length string.1190

In this case, I am going to set it to 25; so our department name can be up to 25 characters long.1195

And then, we close the definition and add the closing parentheses and the semicolon, and we run it.1202

It is going to let us know that we created the table; if we run SHOW TABLES again, we can see we have two tables in our database.1208

to verify the structure of this Departments table we just created, we can use the DESCRIBE command.1216

And we can see that our Departments table has two columns, deptID and name.1223

And we can see all of the information that we just input.1229

So now, let's create the table for our linking table.1232

And because it is linking Departments with Items, again, we are going to be calling it,1238

as is common with linking tables, the names of the two tables combined, so we are going to call it depts_items.1242

The first column we are going to have is going to be deptID, which is going to be a foreign key.1253

It is going to represent a primary key in the Departments table.1257

We are going to have it match the data type of the primary key column in Departments that it refers to.1260

We are going to create the itemID column in the same way, which is going to refer to a primary key in the Items table, which is an unsigned integer.1267

And then, we are going to add that new clause that we learned about to signify a multiple-column primary key.1277

And we are going to say that we want the primary key to be comprised of the columns deptID and itemID.1287

Close the table definition and run it; it is going to let us know that it was OK.1295

If we describe this new table, we can see that there is a column here that I haven't talked too much about,1298

when you run DESCRIBE, that shows which keys are the primary key.1307

Up here, we can see, in our Departments table, that the deptID column is denoted as the primary key.1311

Here, we can see that primary key is denoted as both of these columns.1319

In this particular table, both of the columns end up representing the primary key of the table.1325

That is how we have gone ahead and created our Departments table and depts_items table1329

that is going to allow us to add departments into our database and add items to those departments.1334

And we are going to be continuing in the next upcoming lessons, migrating our database from the departments.txt file into these tables.1341

For your homework, I just want you to go ahead and do what we just did.1355

Create the Departments table that is going to have a deptID and a name column.1358

The deptID is going to be the primary key; it should be an auto-incremented column, an unsigned integer.1364

The name should be a VARCHAR, a variable-length string column.1369

Create the linking table depts_items that has the columns deptID and itemID, which together make up the primary key of that table.1373

So, when you write that CREATE TABLE statement, it is going to give you some practice with signifying a primary key that contains multiple columns.1382

And then, go ahead and populate the tables, so that they mimic the...1391

which is something we didn't do here, but you know how to use INSERT statements now.1395

Go ahead and populate the tables, so that they mimic the three default departments setup that we have in our web store.1398

And that is that our Apparel department in our default setup contains item 1001 and 1004.1404

Our Electronics department has 1002 and 1005; our Sporting Goods department has 1003 and 1006.1411

In order to do that populating, you are going to have to run some INSERT statements1419

that are going to insert information into the Departments table to add these names.1422

And then, you are going to have to run a couple of insert statements that are going to use the deptID's1427

that were generated when you added these departments to the Departments table,1433

to add rows to our depts_items linking table.1438

And you are going to be linking department ID's: for example, deptID 1, you are going to be linking with item 1001 and with item 1004.1441

And then, just make sure that the foreign keys that you defined in depts_items as we went over have the same column types1452

as the primary keys columns that they refer back to, which, for the deptID and the itemID, is an unsigned integer.1459

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

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.