Matthew M.

Matthew M.

Using PHP from MySQL

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

Lecture Comments (3)

0 answers

Post by Joshua Baker on July 31, 2014

If your table has multiple columns you can insert into each like this:

$query1 = "INSERT INTO persons ('name', 'age', 'gender') VALUES ('josh', '21', 'male')";
$result1 = $getDb->query($query1);

1 answer

Last reply by: Jared DiChiara
Thu Feb 28, 2013 1:17 PM

Post by Behrouz Hosseini on November 23, 2012

hi
Can you please let me know why we are using the @ before the $db variable?
Thanks

Using PHP from MySQL

  • PHP provides a built-in extension called Mysqli for interacting with MySQL databases from PHP code. The extension is enabled in 'php.ini' via the line:
    extension=php_mysqli.dll
  • There are essentially four steps required in PHP to execute an SQL statement on a MySQL server from a PHP script:
    1. Connect to MySQL database
    2. Run SQL statement
    3. Process statement results
    4. Close connection to database
  • To connect to a MySQL database, you create an instance of a MySQLi object using its constructor that takes arguments for the hostname, username, password, & database name for the MySQL database you are connecting to.
  • To test for connection errors, the MySQLi object properties conn_errno or conn_error can be tested.
  • A SQL statement can be run on a database using the MySQLi->query() object method that takes a SQL query string as its argument. For successful queries, it will TRUE for non-SELECT queries or a MySQLi_Result object for SELECT queries. FALSE is returned if a query fails.
  • The MySQLi object property affected_rows can be checked to get the number of rows affected by a query.
  • For INSERT statements on tables with an AUTO_INCREMENT column, the MySQLi object property insert_id can be accessed to retrieve the ID auto-generated for an inserted row.
  • The MySQLi->close() object method is used to close the object’s connection to the MySQL database. It will return TRUE if the connection was successfully closed and FALSE otherwise.
    • Note: any access to affected_rows & insert_id must be made before close() is called
  • Additional Resources:

Using PHP from MySQL

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:10
    • Lesson Overview
  • PHP/MySQL Architecture 0:56
    • PHP/MySQL Architecture
  • MySQLi Extension 3:05
    • MySQLi Extension
  • Steps to Using MySQL from PHP 5:53
    • Four Steps to Using MySQL from PHP
    • Step # 3
  • Connecting to a Database 8:12
    • To Connect to a MySQL Database
    • To Test for Connection Errors
  • Running an SQL Statement 11:54
    • Running an SQL Statement
    • Example
  • Processing the Results 15:58
    • For non-SELECT Queries
    • MySQLi Object Property: affected_rows
    • For INSERT Statements on Tables with an AUTO_INCREMENT Column
  • Closing the Connection 19:45
    • Closing the Connection
  • Coding Example 21:24
    • Coding Example: Using MySQL from PHP
  • Homework Challenge 29:00
    • Homework Challenge: 1 - 4
  • Homework Challenge (cont.) 31:10
    • Homework Challenge: 5 - 9

Transcription: Using PHP from MySQL

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

In today's lesson, we are going to learn how to connect to our MySQL database from within a PHP script.0005

Specifically, we are going to briefly talk about how the PHP MySQL architecture works--how those two pieces of software interact.0011

We are going to talk about something known as the MySQLi, or MySQL Improved, extension,0021

that is provided and built into PHP, that allows us to connect to a MySQL database from PHP.0027

We are going to talk about the steps you need to complete to interact with a MySQL database from PHP,0033

which is going to include these four steps here that we are going to talk about in detail,0042

which would be connecting to the database, running your SQL statement,0046

processing any results from running the statement, and then closing your connection to the database.0050

Quickly, I just want to mention about the PHP and MySQL architecture:0057

What we have been doing so far is using our MySQL Monitor, which is a program running in our Windows Command Prompt,0063

to connect to our MySQL database (sometimes...this is not the best art...it is drawn like that),0073

which we know runs as a server on our localhost.0081

What we do is make a connection from the MySQL Monitor to the MySQL database, and then run our commands,0084

such as we practiced where we learned about INSERT commands and SELECT commands.0092

Well, we need to have a way to issue those commands to MySQL from PHP, as well.0097

So, what we have is: instead of this MySQL Monitor, we are going to have a PHP script that is going to do something to access MySQL.0102

And it has methods that are going to be used to connect to the MySQL database.0124

What happens when the user requests a web page that accesses a MySQL database:0129

you have a web client, like Firefox, which sends an HTTP request (as we know) to a web server (in this case, in XAMPP, Apache).0135

Apache will see that it is a PHP file, and it will go ahead and invoke the PHP Interpreter.0149

And the PHP Interpreter is going to understand these commands that access MySQL.0156

And behind the scenes, what it is going to be doing is going out and connecting to the MySQL database,0162

just as we did over here with the MySQL Monitor.0166

And so, the way it is set up is: we have added another piece to this web architecture that we have.0170

Instead of just a browser in Apache and PHP, we have added this MySQL back end that is directly communicated with by PHP.0175

And the way that we are going to communicate with our MySQL database in PHP is using an extension that is built into PHP by default.0187

It is called MySQLi, which stands for MySQL-improved.0195

It is an improved library for interacting with the MySQL database.0198

And it is an extension that, in XAMPP, is enabled by default.0202

And the way you would enable it (if it wasn't, just so you know) is: php.ini has configuration directives to install extensions.0207

And some of the extensions we have been using (such as mail, for example)0219

have already been enabled in our php.ini file, and we haven't specifically talked about them.0223

But just so you know, they have an extension word with = , and then you list the name, in this case, of a DLL file,0228

because it is a Windows operating system, that tells PHP, when it load the PHP configuration, to also load this extension.0237

So, this extension here, php_sql.ini.dll, contains the software that is needed to connect to a MySQL database from PHP.0246

The extension provides both an object-oriented and procedural interface.0258

What that means is that you can use just simple function calls to connect to a database.0263

For example, in our fileLIB.php, we have defined just general user-defined functions that we call without having to specify an object instance.0267

For example, we have the getItem function that we call in our code, just as such.0278

Now, if that was part of an object, we might call it like this, assuming it was a non-static method.0286

This extension provides a way to use either method; you can use just straight function calls, or you can use the object-oriented method.0297

In this class, we are going to be using the object-oriented interface.0305

And we are going to be making use of two classes that are provided by this MySQL extension to use this object-oriented interface.0308

The first one is called MySQLi; that is the name of the class, and that is going to represent a connection to the MySQL database.0315

And we are going to use that to execute queries.0322

There is also a class that we are going to be using called MySQLi_Results,0325

which represents results you get back from running, for example, a SELECT query on a database.0330

In today's lesson, we are not going to be talking about this MySQLi class.0337

We are going to talk about that in a lesson when we learn about how to run SELECT statements from PHP.0343

But today, we are going to be introduced into the MySQLi class.0348

There are four essential steps to connecting to a MySQL database from PHP0355

that all involve different function calls you have to make.0361

The first step that you do is: you need to connect to the MySQL database, which is just like we did from MySQL Monitor,0365

where we would type MySQL , and then we would provide our username and password and the database you want to select.0371

We are going to do that same thing.0379

We are going to do that using this MySQLi object, however.0380

So, the first thing you do is connect to the database.0388

Then, you run an SQL statement; you send the SQL statement to the MySQL server, which returns the results.0391

Then, you process any results from that particular statement.0397

For example, if you are inserting an item, you will be able to access the ID that was generated for a table that has an AUTO_INCREMENT column.0401

For a SELECT statement, it will return back a result set that allows you to see all the different rows that met your search criteria.0411

After you are done processing the results, then you just close the database connection.0419

You have open and close function calls that are two steps in the process.0423

And then, you run your SQL command, and then process the results.0430

Now, step 3, which is the processing of the results, is going to vary depending on whether using a SELECT query,0438

or a non-SELECT query (such as INSERT), because when you have a SELECT query, it is going to return results to you.0445

And as mentioned briefly (we are going to talk about this in our future lesson), the MySQLi result class is what is returned0451

when you execute a SELECT query that allows you to parse through all the rows that returned from a particular query.0461

And that process of doing that is a little bit different than when you have an INSERT query, for example,0470

that doesn't actually return rows; it just lets you know whether the INSERT succeeded, and possibly whether an ID was generated.0475

In this lesson, we are just going to be talking about how to do an INSERT statement,0483

and then how to process the results from that statement.0488

To connect to a MySQL database, the way you do it is: you simply create an instance of the MySQL object.0494

And you do that with its constructor.0501

Its constructor can take a number of parameters, the first four of which are the hostname of the MySQL server you are trying to connect to,0503

the username you would like to connect as, the password associated with that user account0510

(to enable you to log in), and then optionally the database name for the database you are connecting to.0515

The way the code looks is just like this: we are creating a variable, in this case, just called db to represent database connection.0521

We are creating a new MySQL object, and then in the constructor, we provide four arguments.0529

In this case, they are underlined, because these would be strings that you would insert on your own.0535

So, for host, you might provide (in our case) localhost as the string.0541

For username, you might provide phpuser, which is the account we set up.0548

You provide whatever password you set up on your computer.0553

And then, for our web store application, we can pass it the database name advanced_php.0557

When you run this constructor, or you create an instance of this MySQL object,0569

what it does is: it is going to try to connect to the MySQL server at localhost using the username and password you provided.0577

And try to use the database that you requested.0584

And assuming that that successfully works, then that MySQLi object is what you are going to use0587

to access and run queries on that MySQL database that you have just connected to.0594

One of the things that you will notice here is that there is the @ sign, the error suppression operator, before database,0600

because this will throw an error if there is a problem connecting to the database.0607

For example, maybe you provided the wrong username; you provided phpuser1, and it is not allowing you to connect.0610

This will suppress the error, and what it will allow you to do is handle it gracefully.0617

Typically, after you try to connect to the MySQL database by calling this constructor, you will test for connection errors.0621

And the way that you can do that is this MySQL object that was created in this step here.0629

You can test it; it has two properties: conn_errno and conn_err, and these are properties of this MySQL object0634

that provide status information about whether the connection was successful or not.0646

If the conn_errno is equal to 0, that means there were no problems connecting to the database.0651

conn_err is a message that is provided if there was an error; and if there is no error, it ends up being the empty string.0658

A typical test that you might perform would be: you would use the instance of the MySQLi object.0668

You would access, for example, the conn_errno property of that.0675

And if it is not equal to 0 (which implies that there is an error), then you might do some error processing code.0680

Maybe you will log an error message to a file or throw an exception.0684

The typical process with connecting to the database is two steps.0690

You create a new instance of the MySQLi object, and then you perform a connection error test0693

to make sure that your connection was successful--because, if you don't successfully connect to the database,0701

then the rest of the steps that we are going to need (such as trying to run queries and get result sets)0706

are not going to work, because you do not have a valid connection to the database.0711

After you connect to the database, the second step that you are going to be performing is running an SQL statement.0716

For example, for this particular lesson, we are going to talk about running an INSERT statement, which we learned about a few lessons ago.0724

The way you do that is: there is an object method called query that is a part of the MySQLi class.0729

And what you do is pass it a string that represents a valid SQL query.0740

And then, it goes ahead and executes that query on the server, and will return our results.0746

If you are running non-select queries (for example, an INSERT statement, like in this lesson),0752

if the query is successful (meaning the INSERT succeeded), the method will return true.0759

If you are running a SELECT query (as we will see in an upcoming lesson) and the query was successful,0765

it will return this MySQLi result object that is going to allow you to access all of the rows that were returned from the SELECT query.0770

And then, if there is an error with any of the different queries, it is going to return false.0778

One thing to note is that, when we are using MySQLi Monitor, for example, we always had to terminate our queries with a semicolon, like this.0783

Well, when we are creating a string to pass to the query method of the MySQLi object,0800

we don't actually need to include the semicolon; it handles that automatically for us.0806

For example, if we wanted to run an INSERT query--let's say we have a random table here; it could be any table name.0810

Let's say we want to insert...it has one column to which we are going to insert a value; maybe it's just the primary key, so it's just an integer, for example.0819

Well, we create a query that would say INSERT INTO, for example, People.0826

And maybe the value we would insert would be Joe Smith, for example.0832

We would create this query string, making sure, especially on these quotation marks (and actually, it should be in double quotes here)0839

that we should have this string value (if it's a string value) enclosed in single quotes.0849

Remember that string values, when part of a SQL query, have to be enclosed in quotation marks.0855

We would create this query string; and then, the way we run it is:0860

we access the instance of the MySQLi object; we run the query method on it and pass it this query string that was defined here.0865

Or, you could even explicitly, instead of using this intermediate variable, just define it directly within the parentheses.0875

It is an option that is up to you.0881

And then, we store the results of this in the variable called results.0884

Now, for an INSERT query, we are going to either get true or false; it was either successful, or it wasn't successful.0888

And as mentioned, for a SELECT query, we are going to get a MySQL Results object.0893

One thing I want to note, also, is that I am referring to an SQL command, such as a SELECT command,0899

as both a statement, a query, a command...they are all sort of synonyms for the same thing.0907

Whenever you hear me say "a SQL command" or "a SQL statement" or "a SQL query,"0912

it is referring to any of the SQL statements that we have learned about--0920

for example, the INSERT and SELECT that we have learned about.0924

So, even though SELECT is technically what a query is--it is querying the database for information--I will also use the term 'query' to refer to INSERT.0927

So, I might say we executed an INSERT query on the database.0937

I just wanted to mention that, so you understand the nomenclature, as far as when I am referring to these different things.0940

And that is just commonplace in the database world, as well.0948

You say, "I executed this query," "I executed this statement," and so forth.0952

Command is probably the least-used of all of them.0955

After we have opened a connection to the database, we have generated a query string and run the query on the database,0960

and we have received our results back, we want to process the results.0966

As mentioned, for non-SELECT queries like INSERT, no result set is returned, meaning no rows are returned from the database.0971

Instead, we just get true or false.0977

How do we process the results of an INSERT statement that was successful?0980

Well, one of the things that the MySQLi object provides is: it has a property called affected_rows0988

that gets updated any time that you run a non-SELECT query.0993

And what it does is tells you the number of rows that were affected by the last SQL query that you ran.0998

For example, let's say that we just ran that INSERT query that we saw in the last slide.1006

Well, we could, immediately afterwards, access the instance of our MySQLi object, access the affected_rows property value, and test if it is equal to 0.1011

Now, because we expect our INSERT statement to insert one line, we would expect that the number of rows affected would not be 0.1022

For an INSERT statement, the number of affected rows is how many rows were inserted.1032

So, we could test that, if it is equal to 0, then you could do some error processing code that would say there was an error.1036

Maybe you will exit your program.1042

Now, note that we also could have just tested Results to see if that returned false, to see if there is a failure with the query.1044

But maybe...it is not something we are going to do in this course, but one thing you might do is1053

have multiple queries, multiple INSERT statements, that you would be submitting.1057

And it might affect multiple rows; maybe one of them doesn't work, but the other one does.1061

And then, there are just other times when you want to know the number of rows that were added to the table.1065

And so, we are going to be using this just to give you experience with using the affected_rows property, because it is something you will see in code.1072

And when we learn about how to update rows in a database and delete rows in a database, we are going to be using that affected_rows property, as well.1080

One other thing specific to INSERT statements is: if you are inserting a row into a table with an AUTO_INCREMENT column1088

(for example, in our Items table, we have the itemID as an AUTO_INCREMENT column,1095

so every time we insert a row into the database, it increments the last itemID generated and generates that for that row),1098

well, what we can do is: if we have run an INSERT statement with the table with the AUTO_INCREMENT column,1107

we can access that INSERT ID that was generated.1112

For example, when we insert an item to the database, we are going to want to know what itemID it was given.1115

The way you can do that is: there is another property that is part of the MySQLi object, and it is called insert_id.1122

And that can be used to access whatever ID was automatically generated for the last INSERT statement that was executed.1129

So, after we run our INSERT statement, and we have checked and verified that at least one row was inserted,1137

then immediately afterwards, we can go and access the MySQLi object instance,1145

and access the insert_id property (and maybe we store it in a variable called rowID).1152

Now, rowID is going to hold the ID that was generated for the last row that we inserted into the table.1157

For INSERT statements (non-SELECT statements), processing the results is typically going to involve1163

testing whether the appropriate number of rows were inserted, or as we will learn, updated or deleted,1171

and then also, for INSERT statements, gathering the insert_id that was generated for a new row inserted into a table.1176

We have opened the database; we have run a query; we have been able to process the results to it.1186

The final step in accessing MySQL from PHP is to close the connection to the database.1192

After any queries have been run and the results processed using a MySQLi object, we are going to close the connection.1199

And the way that you do that is: there is an object method in the MySQLi class called, not surprisingly, close.1209

And it is a method that just closes that connection to the database, which is something you should always do,1217

when you have an open connection, to help save resources.1223

You don't want to have numerous open connections to a database.1226

What happens is: when you want to close the connection, you simply access the MySQL instance object.1230

You call the close method on it, and it is going to return true or false, just letting you know whether or not the connection was successfully closed.1238

A common thing you can do is to test to see if the connection was closed successfully, by testing, in this case, this variable close.1246

And then, do whatever error processing you might want to do, such as logging errors,1253

saying, "There was a problem closing a connection to the database," and so forth.1256

One important thing to note is that, to access the affected_rows and insert_id properties of this MySQLi object,1262

you have to do that before the close method is called.1271

So, if we call db, arrow, close, and then we try to find out the number of rows1273

that were affected by the last query, we are not going to be able to do that.1279

That is an important thing to notice.1282

We have talked about the four steps; let's see what they all look like in a real piece of PHP code.1286

One thing that I have already done is: I have opened up a command prompt, so that we can look at the MySQL Monitor,1294

so that we can see...what we are going to be doing in this example script is inserting an item into our Items table.1303

So, I have the MySQL Monitor up so that we can see the items in the database, and verify that the item that we tried to add was added to the database.1311

For example, if I run the SELECT query from the Items table as it is right now, before we have run any scripts,1320

and I am just pulling up the itemID, name, and price, because that is all that fits on the screen;1328

we can see that there are six default items in the database currently.1334

What we can do is: the example script that we have is called insertItem.php,1338

and what it is going to do is go through those four steps that we just went over1346

to insert an item into our Items table of our advanced_php database.1349

The first step (step 1) that we learned about was to open a connection to the database.1355

Here, we are creating a new instance of the MySQL class.1359

We pass it the hostname, the username, password, and database that we want to use.1364

And notice here that this password is in plain text, which may be typically something you wouldn't want to do in the real world.1371

But just for the purpose of this example, to not get into the details of that, we are just going to put it as plain text.1379

So, we have Educator as our password.1385

The other thing is that we notice we have the error suppression operator here.1389

So, if this throws an error, it won't get output, and we can handle it gracefully.1392

We have a test to see if a connection error has occurred; we test the conn_errno property on our MySQLi object.1397

And if it is not equal to 0, which means an error has occurred, then we are going to output an error message and exit the script.1406

In this particular case, as we learned, the error message, when an error occurs, is stored in the property conn_err.1412

So, if an error does occur, we are just going to output the message stored in that class property.1420

Assuming we open our connection to the database and everything is successful, then we are going to run our SQL statement.1427

We create an INSERT query that is saying, "insert into the Items table a new row."1433

We are passing it null, so that, as we know, that will trigger MySQL to auto-generate an ID for this new item.1440

We are giving it the name newItem; we are giving it a price $24.99; we are giving it a simple item description;1448

and then we are giving it image file extension jpg.1455

Notice, another thing to re-emphasize is that any string data that we include in SQL queries needs to be enclosed in quotation marks.1458

So, here we have enclosed the name, the description, and the image file extension, all within single quotes.1467

And so, because we are including the single quotes here, we have included the whole query string within double quotes,1475

because in PHP, as we know, that allows you to include single quotes without having to escape them.1481

And then, what we are going to do is run this INSERT query that we have generated.1486

And we do that using the query method.1489

Now, if you will notice here, we haven't stored the results of the query in this particular case.1493

Because it is an INSERT, even though that is something you can do, we are not going to be testing to see if it returned true or false.1502

We are going to be using the number of affected rows to test whether it was true or false.1509

So, in this case, we are not storing the result.1513

Now, when we run a SELECT statement, we are definitely going to be doing that, because that is how we are going to get our result set returned to us.1517

Here, we run the query, and then we are going to process (as step 3) the query results.1523

What we test is this affected_rows property of the MySQLi class that is going to have the number of affected rows from the last query that was run.1529

We are going to test to see that only one row was inserted; we have only had one INSERT statement that we did.1540

And if it is not equal to 1, then we are going to output an error message saying, "OK, there was an error inserting the item," and exit the script.1546

If that all goes well, what we are going to do (assuming that it was one row that was inserted) is:1553

we are going to output a message that says, "The item added was assigned the itemID" and then1560

we are going to access this insert_id property of our MySQLi class1565

to get that insert_id that was generated for the new row in the Items table.1570

And we are just going to output that to the user.1574

As step 4, we are going to close the database connection.1577

You notice that this echo statement actually is up here, because in order to access this insert_id class property, as mentioned,1581

it needs to be accessed before the database is closed.1589

Down here, we have our final step in accessing the MySQL database, which is calling the close method on our MySQLi object.1593

And then, we simply test to make sure whether the connection was successfully closed or not, and output an error message if it wasn't.1602

If we go to the script in our browser called insertItem.php, and we click on the script as is,1610

we are going to see that the item was added to the Items table successfully, and it was assigned the item ID 1007.1618

If we go and run our SELECT query again from MySQL Monitor, we can see that, in fact, a new item was added,1625

and it was given the ID 1007, as it had stated.1632

Now, just to show you how some of these error mechanisms work, let's say that, for example,1637

we misspelled our username; we tried to log in as phpuser1.1642

Well, what that is going to do is generate a connection error; we are going to suppress that error.1648

But we are not going to be able to access if an error occurred by testing the conn_errno property of the MySQLi class.1653

And then, we are going to output an error message.1662

If we go ahead and save this and reload the page, we are going to get the error message that was generated by MySQL,1664

which is saying, "Access was denied for php1user at localhost."1672

And that makes sense, because as mentioned, we had an incorrect username.1676

Let's say we correct the username, but then maybe we have an error in our query.1682

Let's say we misspelled values; and then, what is going to happen is:1686

the number of affected rows is not going to be equal to 1, because the query is going to fail.1691

So, we should be able to get this message: There was an error inserting the item.1694

Now, if we refresh the page, there was an error inserting the item.1699

So, even though there are four main steps that are in the process (open a connection, run the query, process the results,1703

and close the connection), there are little substeps that go along the way that are important for good application development,1710

which involves testing things along the way--testing to make sure that your connection was achieved;1717

checking to make sure that your query was run successfully; and then also, checking to make sure that the connection was closed.1723

This is a script that sums up all of those four steps for running an INSERT statement from PHP.1731

The homework challenge for today is going to be: I am going to have you mimic what we did in class today.1742

I am going to have you create a new table, called People, in the advanced_php database.1746

And there are a couple of reasons for this.1752

One is that it will allow us to run this example; two is that it will give you a little more practice using the CREATE TABLE command.1754

I want you to create a table called People that contains three columns: one called personID,1764

one called name, and age, that represent--personID is going to be a primary key,1769

which is going to be an auto-incremented primary key column; name is going to represent the full name of a person.1773

This database is just going to hold people that have names and ages.1782

And then, age is going to be an unsigned integer that holds the age of the person.1785

For the name, I want you to use a variable-length string data type, which (you should remember) uses VARCHAR data type.1790

And then, you can set some maximum limit on it, as to whatever you think would be reasonable for the full name.1801

I will leave that up to you.1807

Then, what you need to do is create a web form called insertPerson.html1809

that has two input text boxes: one where you can enter the person's name, and one where you can enter the age.1813

And then, have this HTML form submit that information.1820

You can do POST or GET--whatever you want--to insertPerson.php.1823

Now, there is a typo here; this should say insertPerson...1830

In this insertPerson.php script, what I want you to do is: the script itself is going to try and insert this Person into this People table in our database.1835

The way you are going to do that is: you are going to have it create an INSERT statement from the form data that was provided by either GET or POST.1846

And make sure it is a well-formed INSERT statement; make sure that any strings you have within your SQL statement are properly enclosed within quotation marks.1854

And just generate a valid INSERT statement.1868

Then, run the INSERT query, which is going to give you...you are going to have to use the query method of our MySQLi object.1872

The other thing to mention is that the first thing you are going to have to do is:1878

you are also going to have to create that MySQLi object to get the connection of the database.1882

So, you are going to need to provide the hostname, the username, the password, and the database (advanced_php).1887

Then, I want you to check the affected_rows property to make sure that one row is inserted into the database,1894

to test and make sure that the query works--that you didn't have any errors in your query.1900

Then, output the insert_id that was generated for that new row; and that is going to end up1905

being the personID of this new Person object that you have added to the database.1910

And so, that will just give you practice accessing the insert_id property.1915

And then, go ahead and close the connection to the database, using the close method.1921

A couple things: make sure that you test for a successful connection initially, using either conn_errno1927

to test if it is not equal to 0, or you can use conn_err to test for an empty string.1933

If that error is equal to the empty string, that means that no error was encountered.1939

And just do some proper error checking.1942

And then also, just check that close properly occurred, and output an error message if the close operation failed.1945

And then, as mentioned, make sure you properly quote any string values within your INSERT query.1952

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

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.