Matthew M.

Matthew M.

Introduction to Databases

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!

Introduction to Databases

  • A relational database is a type of database that stores data, and relations between the data, in organized structures called tables.
  • A relational database management system, or RDBMS, is a system that provides an interface for managing and using a relational database.
  • Tables organize data into rows and columns. The columns describe the type of data that each row should supply, and the rows, also known as records, are sets of values corresponding to the table's columns.
  • Each column defined for a table has at least two properties:
    • Name – an identifier for the column
    • Data type – the type of data stored in the column
  • SQL, or Structured Query Language, is a language used with RDBMS to:
    • Define the structure of a database & its tables
    • Insert, update, & delete data from a database
    • Query, or request, specific pieces of data from a database
  • SQL also defines several standard data types for data that can be stored in a database table:
    • Integers
    • Floating-Point Numbers
    • Strings
    • Dates & Times
  • A primary key is a piece of data that can be used to uniquely identify table rows.
  • A table’s primary key refers to the column for which the value of that column in each row is guaranteed to be unique.

Introduction to Databases

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
  • Flat Files 1:09
    • Flat Files: Definition and Example
    • Problems Associated with Using Flat Files as a Database
  • Relational Databases 3:29
    • Relational Databases
    • Relational Database Management System (RDBMS)
  • Tables 7:43
    • Tables
  • Columns 9:24
    • Columns
  • What is SQL? 10:45
    • Introduction to Structured Query Language
    • Standard Data Types of SQL
  • Primary Keys 13:19
    • Primary Keys
  • Primary Key Examples 16:36
    • Primary Key Examples

Transcription: Introduction to Databases

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

Today's lesson is a rather exciting lesson, because we are going to be writing an introduction to databases,0005

which is going to lead us down the path towards integrating a MySQL database with our PHP application,0010

which is one of the main goals of this course.0014

As part of our lesson today, we are going to be discussing a concept known as flat files,0018

which is a way of maintaining data for a particular web application.0022

We are going to introduce the concept of relational databases, which is what MySQL is.0027

As far as our relational databases go, we are going to talk about what a table is, and some of the properties of columns in relational database tables.0033

We are going to introduce something you may have heard about before, called SQL.0043

It is a structured query language; we are going to talk about what that is, and how it is used to work with databases.0047

And then, we are finally going to finish up with a rather important topic, called primary keys,0055

that describes some types of data that are going to be included in our tables,0060

and then provides some primary key examples to emphasize the point.0065

In general, a database is just an organized collection of data that you can access.0071

You can add information to it, and then you can search it, or in database parlance, you can 'query' it, to find out information.0076

In our web store, we have had a database that we have kind of referred to as a database,0084

but not so much specifically calling a database.0089

And what we have been doing is storing all of our data in text files, which are often referred to as flat files.0094

So, it is considered a flat file database.0101

And these text files that we have--items.txt and departments.txt--contain all of the data about the items and departments in our store.0104

And it is essentially our database.0111

If we look at items.txt, for example, what it does is, as we know, describes the item ID of a particular item;0114

it has its name, its cost, its description, image extension, and so forth.0123

And so, we have been storing all of this information in a database that is just composed of files.0128

Well, there are a couple of problems associated with using flat files to the database that relational databases are going to help to solve.0136

First of all, it is typically slow to access and add and search information from text files.0143

As mentioned, they are difficult to search; and to do things such as updating a file is difficult, as well.0151

For example, in our current application, when we update a department, what we are doing is copying all the file out;0158

we are searching for the row that we need to update.0166

We update that row, and then we rewrite all of those rows back to the file,0168

which is not a very efficient way to do things, and can take time, and is difficult.0171

Additionally, concurrent access to flat files can be a problem.0179

If you have maybe two web users that are logged in at the same time that are trying to add an item to the store, for example,0182

they both have to have access to that items.txt file.0189

And so, we haven't talked too much about it, because our relational database is going to take care of that;0192

but you have to worry about such things as file locking issues,0197

so that multiple people can access the file at the same time without overwriting each other's data.0200

Our relational database is going to help to solve some of these problems.0206

Our relational database is a different kind of database, and it stores data, and relations0212

between the different pieces of data in the database, in structures called tables.0220

It stores things in tabular format.0227

And what is known as a relational database system, or RDBMS, is a system that provides an interface for managing and using a relational database.0232

A relational database is comprised of all the tables that contain the information in the database.0243

The RDBMS provides additional features on top of that.0248

It provides an interface for accessing those tables, adding and deleting information in the database.0252

It also provides ways of providing security on them.0258

It provides things to allow concurrent access to the data.0263

And so, MySQL is a relational database management system, and so it provides these additional features.0267

So, not only are we using a relational database, but we are using this management system on the outside0274

that provides this nice interface that allows us to do different things.0278

RDBMS's solve many of the problems of flat files, one of the first being that they are faster than flat files for both storing and retrieving data.0284

We talked a little bit, just a minute ago, about searching for data in a flat file.0292

But actually, when we need to look up an item in the store, we need to go through line-by-line,0297

each line in the file, looking for a particular item.0302

Relational databases are made for doing that in a fast and efficient manner, so it is going to increase the speed with which we can look things up.0305

Additionally, part of the service that relational database management systems provide is: they handle concurrent access automatically.0312

So, they allow multiple users to connect to the database to read and write.0321

And they ensure that one person's changes don't overwrite another person's changes.0325

In our flat file database system, if we really wanted to ensure a system that allows multiple people to access it at the same time,0330

we would have to write our own code to do that.0338

And it is rather difficult to do, and we basically would have to manage it on our own.0341

So, using a relational database eliminates us from having to even worry about concurrent access, as the database handles that automatically.0345

The other thing that and RDBMS provides is typically a privilege system for controlling access to the data.0353

So, for right now, our flat files can be accessed by anybody that has operating system permissions on those files.0359

Well, what you can do with the RDBMS is have fine-grain control over your data.0367

You add users to the database system that restricts who can access what parts of the data--0372

For example, who can access certain tables of data, who can perform certain operations,0383

who can add things to the database, who can only query from the database, and so forth.0387

And the nice thing about that is: again, this is something that is built into the database management system.0393

So, it is not something that we have to implement.0399

If we wanted to implement that on our own, outside of the operating system privileges that provide basic access control for files,0401

we would have to implement that in PHP and validate a particular user and say,0410

"OK, this user can access that file; this user can update this file."0413

Well, the relational database management system does that for you automatically.0418

One thing to note is that you will hear me say RDBMS, or relational database; and I sort of use those terms interchangeably,0423

which is a common thing to do; so just so you know, any time I refer to an RDBMS or relational database, I am referring to the same thing.0431

I will refer to a MySQL database, and in this case I am referring to the whole database management system,0439

which provides these things--like the privilege system, facility for concurrent access, and so forth.0445

Also, for the remainder of this course, when I just use the term 'database,' I am referring to a relational database,0452

because that is what MySQL provides, and that is what we are going to be using for the rest of the course.0458

Relational databases store data in structures called tables, which organize data into rows and columns.0465

If you have ever used a spreadsheet before, it is pretty much the same exact thing.0473

What you have is defined columns that describe the types of data that rows in the table should provide.0478

And what each row is--it is known as a record.0487

And it is a set of values that provide values for all of the different columns.0492

It would make it easier to explain if I had a concrete example.0499

Let's say we have a table called Customers, which is used to store the names of all our customers for our fictitious web store.0501

We can define a couple of columns: one column is called firstName, and we are going to use that to store the first name of our customer;0509

we have a column lastName that stores the last name, and a column middleInitial that stores the middle initial of the customer.0516

So then, what we have is: that defines the structure of this table.0522

It defines the different columns, and the different data that is in this table.0527

And then, what we do is add rows to the table that represent these real-world objects, these customer objects.0531

Then, customers have a first name, a last name, and an initial, and so for each row, we provide values for the first name, the last name, and the middle initial.0537

For example, this first row of the database represents the customer Joe T. Smith.0545

We can add as many of these as we need to for each customer in the store.0550

And that is how the table organizes data: rows provide values to the data defined by the columns.0554

Now, columns in tables have at least two properties.0565

The first one is a name (which is what we saw in the last one: we had the firstName column,0571

the lastName column, and the middleInitial) that can identify the data that is stored there.0575

They also, like PHP (we have variables that have data types, integers, floats, and so forth)--columns have an associated data type with them.0582

For example, for our firstName column (we are going to learn more about the specifics later),0591

that would be a string data type, because it contains text.0600

So, that first column in our database has a name equal to firstName, and then it has a data type associated, which is string,0604

which means that every row in that table, to store a value in this firstName column, for example--you need to provide a string for it.0611

You provide Joe, for example, and so forth.0620

You can have other things, like numbers; maybe you would have a birthDate column.0624

And there is a special data type for dates and times, in which you could store Joe's birth date, for example.0631

Columns have both names and data types associated with them.0641

Now, I want to talk about SQL; this is something you may have heard before.0648

It is definitely something that is directly associated with relational databases.0651

What it stands for is Structured Query Language, which is not particularly important, as much as what it does.0657

And what it is: it is a language to work with relational databases.0664

It can be used in several different ways.0669

It can be used to define the structures in a database and the tables in a database.0671

For example, it might say, "OK, I have this database that has five tables."0675

"One table represents customers; it has these columns. One table represents orders and has these different columns," and so forth.0679

It provides a way to, in database terms, insert, update, and delete data in the database.0688

You can insert rows in the database; you can delete a row from the database.0695

For example, you could delete a customer from the database.0699

And you can update it; for example, maybe you have an item, and you want to update its price.0702

You could update that in the database, using what is known as a sequel update command.0705

And by the way, you can refer to it as 'sequel' or SQL; people call it different things.0710

The other thing that you can do with SQL is query (in database terms, that is another name for requesting or searching a database)0716

the database for specific pieces of information.0726

For example, you might say, "Give me the address of customer Joe Smith."0729

Maybe you have that stored in your database, and then you run this query--this SQL command--on this relational database.0733

It understands that command, and then it is able to provide that information back to you.0740

The other thing that SQL provides is several standard data types for data that can be stored in a database.0744

As mentioned, the columns in tables have data types associated with them.0753

For example, our firstName column contains string data, and our birthDate column would contain date/time data types.0758

Well, SQL provides a couple of different data types for all of Integers.0767

It provides a couple of types for floating-point numbers, a couple of different data types related to strings,0774

and a couple of data types related to storing dates and times.0781

And we are not going to talk about the specific data types now; but as we build up our web application0785

and start introducing SQL commands, we are going to talk about these data types specifically, as they come up.0791

I want to finish up this lesson with an important topic known as primary keys.0800

One of the main things that you do with a database is: after you add information to it, you query the database; you request information from it.0807

We have to have a way to look up data in the database: primary keys are a means of doing that.0813

For example, let's say we want to look up customer Joe Smith's address in the database.0820

Maybe he is a row in this Customers table.0826

Well, in order to do that, we have to have a way to uniquely identify Joe Smith.0830

And a piece of data that can be used to uniquely identify a table row is known as a primary key.0835

For example, we couldn't necessarily use the name Joe as a primary key, because you might have other people named Joe in your database.0844

So, when you query the database and say, "Give me the address associated with Joe,"0850

it is not going to be able to uniquely identify it.0854

What a primary key is: it is something that has to be unique.0859

For example, something in the real world that is unique is a Social Security number.0861

Each person only has one Social Security number, so that could be a piece of data that you could add.0866

It can be a column that you could add to your database, socialSecurityNumber.0871

That is not something you would probably want to do, but it is just as an educational example.0874

And what that would do is: you could look up a database and say, "Give me the row associated with Social Security...."0878

And if we know that that is Joe's Social Security Number, the database can return the information associated with Joe.0885

It is a way to uniquely identify a particular row in a table of the database.0890

And what you can do is note columns of a table as primary key columns.0896

What that says: for example, if we had a Social Security number column, we would tell the database, "This is a primary key column."0902

And what that means is that, for every row that is in that table, a value can only exist one time.0908

For example, you can't have two rows that have the same Social Security number; that defeats the point of a primary key.0917

And so, what you do is define it as part of the database (and we are going to learn more about how to specifically do that0924

when we create tables) to say, "This column represents a primary key, so ensure that a row isn't added0928

that tries to add a value for this primaryKey column that is a repeat of another one in the table."0936

When we talk about a table's primary key (which is a term you will hear used),0945

we are referring to the column whose values serve as the primary keys for the rows.0950

For example, if we were using socialSecurityNumber, we would say, "The primary key of table Customers is socialSecurityNumber."0956

Another unique thing is that books have ISBN numbers, which uniquely identify a book.0965

If we had a table of books, we could say, "The primary key for that table is ISBN."0969

And we might have a column in the table called ISBN, along with maybe the name of the book, and so forth.0974

And so, the key thing to note is that there is a one-to-one correspondence between a primary key and a row in the database.0983

You can't have one primary key value that links to multiple rows in a database.0989

In that case, it wouldn't be a primary key.0994

For example, to explain a little further what we were just talking about, let's say we had our three customers in the database from before.0998

But I have changed some of the data here.1006

So, let's say we wanted to look up Joe Smith; let's assume this column wasn't here, for now--we just had our three columns, as before.1007

If you want to look up Joe Smith (maybe we had additional columns over here that had address information,1019

so we want to find Joe's address), we say, "OK, give me Joe Smith's address."1025

Well, how could we do that? We couldn't say, "Give me Joe's address," because there is another Joe in the table.1030

Well, let's try his last name; we could say, "Well, give me the person whose last name is Smith--their address."1036

Well, that doesn't work, because we have a Jane Smith also.1041

Likewise, we have a doubled-up middle initial, so we couldn't say, "Give me the address of the person with middle initial D."1044

However, if we add our primary key column, socialSecurityNumber, which is a one-to-one correspondence1053

between a person (or a customer) and that ID, we could say,1060

"Give me the address of the customer with Social Security number 123456789."1065

And then, our database is going to be able to go and look it up in this table, and find this exact row right here,1071

and pull up any of the information in that row, as needed.1077

One thing to note is that this is called a table schema--it is a way of describing a table,1082

and often you will see a table described by its column names.1089

For example, this table has four columns: firstName, lastName, middleInitial, and socialSecurityNumber.1093

Typically, when you see column names listed, if it is a primary key, it is underlined.1098

That is something that you might see out there in literature and documents on the Web1102

that you come upon that talk about the structures of relational databases.1108

If something is underlined, that typically means it is a primary key.1113

Another option (because let's say we don't want to store Social Security numbers, which you probably don't,1117

for privacy reasons)--another thing that you can do--is just to randomly generate a unique ID every time you add a user to the database.1122

For example, when we add Joe T. Smith to the database, we can assign him the number 1.1131

When we add Jane D. Smith to the database, we can assign her the number 2.1137

And we have defined a new column here; instead of Social Security number, we have defined a primary key column called customerID.1142

What that means is that each row in this particular table has to have a unique value for it.1149

And then maybe we add Joe D. Simpson, and we assign him number 3.1156

The way you can do that is: you can manually do it--you can check the database, and one way to do it is just continually incrementing a number.1159

So, we could say, "What was the last person added to the database--what is their customer ID?"1167

Well, Joe Simpson had a customer ID of 3; "OK, when I add a new user, I am going to create a new user with customer ID 4."1171

So now, that ensures that each row has a unique customer ID, and it has a primary key to associate with it.1184

When I want to look up Jane Smith, I will say, "Give me the customer information associated with customer ID 2."1190

And so, this customer ID doesn't have a real-world analog, whereas Social Security number does.1198

There are some things in the world that you can use to model real-world objects,1203

like Social Security numbers and ISBN numbers, that can be used to uniquely identify things.1207

But oftentimes (and what we are going to be using in all of our examples in this course),1212

we will just be using randomly generated numbers, because that is something that we can ensure will always be unique.1216

One thing that you want to note is that if, let's say, you don't use something like this,1223

where you are continually incrementing a number each time a person is added to the database.1228

You want to make sure that, if you don't do that, and you define a primary key column like Social Security number,1234

that that is something that will ensure that any future users added to the database won't have multiple variables.1239

We know that each person (at least in the United States) has an individual Social Security number.1246

So, we know that any time we add a new person in the database, that Social Security number is not going to come up.1251

So, that reason is why we couldn't use something like last name, because there might be another Smith we add to the database.1257

And so, when you pick your primary key column, you want think about the future, as well,1266

and make sure that this is something that is always going to be unique, even in the future.1271

That ends today's discussion on the introduction of databases.1277

Thank you for watching today's lesson; I look forward to seeing you next time.1281

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.