Matthew M.

Matthew M.

SQL Command: UPDATE

Slide Duration:

Table of Contents

Section 1: Advanced PHP with MySQL
Course Introduction

13m 36s

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

18m 46s

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

35m 37s

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

36m 36s

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

34m 36s

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

14m 11s

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

17m 38s

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

28m 14s

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

22m 51s

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

15m 30s

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

32m 44s

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

17m 47s

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

39m 57s

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

17m 30s

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

22m 20s

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

16m 19s

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

53m 37s

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

31m 12s

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

37m 49s

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

21m 7s

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

22m 43s

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

22m

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

39m 47s

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

39m 11s

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

46m 8s

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

11m

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

29m 59s

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

19m 14s

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

21m 24s

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

28m 11s

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

23m 1s

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

27m 11s

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

24m 57s

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

32m 43s

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

30m 30s

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

24m 22s

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

17m 6s

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

24m 32s

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

24m

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

10m 33s

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

16m 2s

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

32m 14s

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

12m 33s

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

42m 16s

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

29m 34s

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

26m 13s

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

14m 36s

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

Share this knowledge with your friends!

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

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

  • Study Guides

  • Download Lecture Slides

  • Table of Contents

  • Transcription

  • Related Services

Start Learning Now

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

Sign up for Educator.com

Membership Overview

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

SQL Command: UPDATE

  • An UPDATE statement is an SQL command used to update the data values of specified rows in database tables. It has the syntax:
    UPDATE items SET name=‘Blue T-Shirt’, price=8.99 WHERE itemID=1001;
  • Its WHERE clause operates just like for SELECT statements to be able to select specific rows from a table. It can thus also use comparison & logical operations in its where condition.
  • To update column values for all rows in a table, the WHERE clause is simply omitted:
    UPDATE items SET imageFileExt=‘jpg’;
  • Running an UPDATE query from PHP works the same way as running an INSERT query from PHP because it does not return a result set. To process an UPDATE query's results:
    • The result of the query() method can be tested for its truth value to test the success of the UPDATE.
    • The MySQLi->affected_rows property can also be accessed to get the number of rows that were updated.
  • htmlspecialchars() is a built-in PHP function that will encode any HTML special characters within a string as proper HTML entities. The function will encode the following special characters by default:
    • – becomes &quot;
    • < - becomes &lt;
    • > - becomes &gt;
    • & – becomes &amp;
  • addslashes() is a built-in PHP function that is used to escape characters that should be escaped in database queries. It will escape the following characters:
    • – escaped as \’
    • – escaped as \”
  • Additional Resources:

SQL Command: UPDATE

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
  • UPDATE Statement 0:54
    • UPDATE Statement & Its WHERE Clause
    • Example: UPDATE Statement
  • Using UPDATE from PHP 5:21
    • Using UPDATE from PHP Overview
    • Coding Example: Using UPDATE from PHP
  • htmlspecialchars() 9:04
    • htmlspecialchars()
    • Encoding Special Characters
    • Coding Example: htmlspecialchars()
  • addslashes() 20:40
    • addslashes()
    • Coding Example: addslashes()
  • Homework Challenge 27:55
    • Homework Challenge: 1 - 2
  • Homework Challenge (cont.) 30:14
    • Homework Challenge: 3 - 5
  • Homework Challenge (cont.) 30:41
    • Homework Challenge: 6 - 9

Transcription: SQL Command: UPDATE

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

In today's lesson, we are going to be learning about a new SQL command known as the UPDATE command.0005

We are going to be going over what an UPDATE command, or an update statement, looks like,0011

which is used, as the name implies, to update information in the database.0015

What it does is updates information for a particular set of rows in the database.0019

We are going to talk about how to run an UPDATE query from PHP script.0026

And then, we are going to go over two built-in functions in PHP: htmlspecialchars and addslashes,0031

which are two functions that are used to be able to effectively retrieve information from the database,0040

to output it, and then also to be able to appropriately add information to a database.0047

As mentioned, an UPDATE statement is used to update data values for specific rows in a table in the database.0056

It has the following syntax here: it is the keyword UPDATE, followed by the name of the table that you are trying to update rows on.0065

This is saying, "Update rows in the Items table."0074

And then, it has the SET keyword; and then, what you do is have a comma-separated list of column names with the values you want to set them to.0077

For example, this is saying, "Update the rows in the Items table, and set the column name to have the value Blue T-shirt,0087

and set the price value equal to $8.99, for any row WHERE (and again, it makes use of a WHERE clause) itemID equals 1001."0100

What this entire query here is saying is, "Update the item 1001 row in the Items table, so that its name is Blue T-shirt, and its price is $8.99."0111

Again, just to point out, notice that our strings are enclosed in single quotation marks.0123

The WHERE clause operates just like for SELECT statements, and also as we have done in our DELETE statements.0127

So, it can use comparison and logical operators.0136

So, you can actually update multiple rows at the same time in a database.0140

For example, maybe instead of saying "where itemID equals 1001," you could say, using a comparison operator, "where itemID is less than 1004."0144

Now, this may not be something you want to do; but just as an example, this would say,0156

"Update all the rows in the Items table; set their name value equal to Blue T-Shirt; set the price equal to $8.990159

for all of the items where the itemID is less than 1004," so you would update multiple rows.0167

If you want to update column values for all of the rows in a table--for example,0172

let's say we have switched over our website so all of our item image files are jpg, and they have a jpg extension:0177

well, you could run this UPDATE statement here, and you just leave out the WHERE clause.0187

And that is going to update every row in the table.0191

So, this is going to say, "Update all of the rows in Items, and set the column value imageFileExt for each row equal to the string jpg."0193

And you notice that there is no WHERE statement here; and what that is going to do is make that UPDATE statement apply to all the rows in the table.0203

Let's take a look at...I have logged into our MySQL Monitor, so we can play with the database.0214

And if we take a look at what we have in our Items table, for example: this is the information we have about the particular items.0221

Let's say we want to update our Baseball Bat row to just be a baseball; let's say the item represents a baseball now.0235

Well, what we can do is say, "Update the Items table; we want to set the name column equal to just Baseball."0244

"And we want to do that for all rows where the item ID equals 1003."0253

Because that item ID is a primary key, we know that this UPDATE statement is only going to apply to one row.0261

And when we run it, it is going to say, "Query was OK; one row was affected; one row was matched; one row was changed."0266

If we run our SELECT query again, we can see that now, item 1003 is called Baseball.0274

One thing to note is: let's say we try to run the same query again, where we are trying to update the name of it.0280

It is going to say that one row matched: it found one row that matched this WHERE clause.0287

But it wasn't changed, because nothing changed in it, so it is going to say "0 rows affected."0291

And that is going to come up when we talk about how to use these UPDATE statements from PHP,0296

because we use the affected_rows property of our MySQLi object to sometimes determine whether a query was successful or not.0302

So, even though a query may be successful, in that it assures that the data is updated to how you want it,0309

it may not affect any rows if the data actually did not change.0317

The way we run an UPDATE query from PHP is the same as we do for our INSERT and DELETE queries.0323

They don't return a result set; we can test the return value of query--it is a truth value to see whether it was successful or not.0330

It returns true if the update was successful, false if it wasn't.0338

We can also check the affected_rows property of our MySQLi object, and what that allows us to do is to see the number of rows that were updated.0343

Now, as mentioned, the first time you run the query, if you are changing information, it will let you know the number of rows that were changed.0353

If you run it again, affected_rows is going to say 0 if you update the same data.0358

So, that is something you have to consider when you decide how to test if an update was successful or not.0363

Assuming we already have a connection to our database, it has the db variable, it is a MySQLi object, and0373

it is already connected to our advanced_php database: if we have this query here, UPDATE Items, SET price equal to 15,0378

there is no WHERE clause on this query, so this is going to update all of the rows in our Items table.0385

And it is going to say, "Set the price of every item in the table to 15."0389

We are going to run the query; this Result variable can be tested for true or false, to see whether the update succeeded or not.0393

And then, we can also output the number of rows that were affected by the query.0402

In this case, if we had 6 items in our table, affected_rows would say 6 the first time it was run.0407

The second time it was run, it would report 0.0411

Let's go take a look at a script we have created that shows how to use this in PHP.0415

It is just called updateItem.php: it follows the four...as with any SQL queries, you have the four steps that you go through when running a MySQL query.0421

You connect to the database; you run the query, process the results, and close the connection.0433

We have, in the beginning of this script, our connection to the database.0438

Then, we run our SQL statement; in this case, we are running a static statement that is just saying,0441

"Update the Items table; set the price equal to $0 where the itemID equals 1001."0447

So basically, we are saying, "Update item 1001 so that its price is 0."0454

We run the query; we test that only one row was updated, because the WHERE clause specifies a primary key value.0460

We know it should only update one row.0468

The first time we run this, if the query was successful, it is going to return that 1 row is affected.0470

And then, we are going to be able to output, "The number of rows affected was..."0476

Now, if we run it a second time, as you will see, it is going to show that may be considered an error--0479

"There was an error updating the item"--and that is because it was trying0486

to update the same information again, and it doesn't affect any of the rows.0490

Then, we simply close our database connection.0493

If we look at the state of our Items table again, we can see that item 1001 currently has a price of $15.0496

If we go ahead and run this first example, it is going to run that query.0505

We can see that the number of rows updated was 1; it lets us know that the query was successful.0509

If we go and look at our Items table again, we can see now that the price of the item 1001 has been set to 0, so the UPDATE query was successful.0514

If we try to refresh the page, which is going to try to run that query again, we are going to get that error message, "There was an error updating the item."0522

And so, that is not necessarily an error, but it is because no rows were affected, and we were testing on the affected_rows property.0528

So, that is just something you need to consider.0535

And if we look at our Items table, nothing has changed; so that is why it shows up as 0 rows affected.0537

One of the things PHP provides is a special function called htmlspecialchars.0546

And what that does is encodes any characters that have special meaning in HTML into the proper HTML entities.0553

For example, if we have data stored in our database that contains a double quote or a less than sign,0564

or a greater than sign or an and sign, those are special characters in HTML.0570

If we were to extract the data--let's say, load the information about an item from the database and output it on a page,0575

if it contains a quote character, that might affect how our HTML is going to look.0584

For example, let's say we have a text box, and we want to set the value of it.0589

And then, what we might have here would be a PHP statement (let's just say it was down here)0601

that is going to echo, for example, the name variable.0607

Let's say the name variable contains a double quote.0612

Well, what is going to happen is: then, what we are going to get output from PHP (this is leaving out the rest of the attributes,0621

just so it is less to write)--let's say name was equal to...the name "Joe" in double quotation marks.0629

What is going to happen is: it is going to output the first double quote, because that is just part of what is included as our HTML.0654

When it echoes the name, it is going to output the name string, which is going to be "Joe" enclosed in double quotes.0662

So, it is just going to output this; we are going to have the ending quotation mark here, and then the closing script.0667

So, what is going to happen is: when this gets interpreted by your browser, when it gets sent to your browser,0676

it is going to see that the value input actually has no value supplied for it,0680

because this quotation mark that was included as part of the string we pulled from the database contains a quotation mark.0686

So, that is a special character in HTML, and then it closes attributes; so, that is going to mess up how our HTML is interpreted.0693

The same thing, for example, if we had a less than or greater than sign: if that was part of our code--0701

for example, maybe it was included in here--that might cause problems with HTML thinking, "OK, this input tag has been closed."0704

What HTML has, for those of you that don't know: they have special entities that are called,0712

which are these little sequences, like this, that you can use0717

to represent those characters without them being interpreted as HTML characters.0722

For example, if you wanted to output "Joe" as an actual value, we could do it like this.0729

What that is going to do is: when your browser interprets this HTML, it knows how to interpret these HTML entities.0757

And it knows that, when it encounters & , followed by quot, followed by a semicolon,0763

that it should just output a double quotation character, and it is not meant to be the character that is closing the attribute value.0769

If your string data contains these types of characters, you are going to want to escape them0778

or turn them into these HTML entities, so that they are properly interpreted.0785

And so, the htmlspecialchars function does that.0790

You provide it with a string--maybe it is a string you have read from your database (or that is what we are going to be using it for).0793

And what it does is takes that string, and if it finds any quotation marks in it, or any less than signs,0799

or greater than signs, or ampersand signs, it goes ahead and translates them into these appropriate entities and generates that string.0804

You run this as a filter around data you are pulling from your database, before you output it by an echo statement to your HTML script.0815

If we look at a new version of our updateItem.php, and if we take a look at the script,0822

it is a little bit different, in that it is going to make use of our Item class that we have been using in our database.0829

And the first thing it is going to do is allow us to choose an item to update.0837

And we do that by entering an item ID here.0841

So, it kind of has three different parts to it.0843

I might select item 1002, and we are going to say "show item."0846

And what that is going to do is: in this particular case, we are just going to be updating the name,0850

just for this example, rather than all the other properties of the item.0854

And it is going to output the name in this text box that shows what the current name of our item is, because we want to update that.0857

And so, we want to know what it is, and maybe we want to change it.0867

Let's say 42" LCD...it's kind of implied...Color Television; and we go to update the item.0870

It is going to update that name of that item in our database.0879

So, this script has three different parts to it: it has one where you select the item;0882

it has another one where it outputs what the current value of the name of the item is;0885

and then, it allows you to try and update the item in the database.0891

Well, if we look at our script here, it has three parts based on an action variable that we are going to be using,0897

that is going to be either null, showItem, or updateItem, based on which part of the script we are running through.0903

In our data processing section, we go ahead and connect to the database.0911

If the action is showItem, then what we do is run a SELECT query on the database to load the information about the particular item.0915

In this particular case, we are interested in the name of the item.0924

But we go ahead and build up an Item object that contains all of that information.0926

And then, in our section down here, where it says showItem, we go ahead and are outputting the information about the particular item.0931

Now, the thing that we are going to be doing is: you will notice that, in our text box,0944

we want to output the current value of the name of the item, so that, when we go to the script, we can see what it currently looks like.0949

So, what we have done is: in our text box, in the value attribute,0960

we have outputted an echo statement that outputs the name of the item.0965

And it accesses this Item object that we built up at the beginning of the script.0970

And it runs it through this htmlspecialchars function that is going to escape any special characters, so that we don't run into any problems.0975

One other thing to note is that it also passes, along here, the itemID, because what is going to happen is:0986

when we submit this form, we are going to want to be updating the name of that particular item in our table.0992

We need to be able to identify which item we want to update.0996

We do that by the primary key, which is the item ID.0999

So, we output here a hidden input field, the item ID that we are going to update.1002

When it gets re-submitted with the UPDATE Item, after we have changed the name, and we hit Update Item,1007

it is going to get re-submitted with an action that is going to be called updateItem.1013

So, in our processing section, then what it is going to do is create a query that is going to update the name of that particular item.1016

And that is where we get...this is Process form variables up here,1023

where it processes GET variables...itemID and name, for the name and itemID of our item.1028

And what it does is says, "Set the name of our particular item in the Items table equal to" whatever the name was set to, "where itemID equals itemID."1034

So, that is saying, "Update the name and column of the item row represented by this item ID,1044

and then, if there is no error, it goes ahead and goes down to the Output section, which updates a message1054

that says, "The item was updated successfully," as we saw.1060

If we look at the current status of our database, we can see we have a 42" Color Television, $599.99.1064

Well, let's say we wanted to update the name of that.1075

Actually, let's say, right now, if we look at it...let's take a look at what that script looks like...1077

if we go back and select item 1002 and click Show Item, this has a special character in it: it has a quotation mark.1084

So, that is going to get escaped, or turned into an entity, by our htmlspecialchars.1090

If we look at the HTML, we can see: here is that &, quot, semicolon that is representing that quotation mark1095

that is letting your browser know that it is not a quotation mark to close the value attribute,1103

but it is just a quotation mark to be output as is.1109

And so, that is how the htmlspecialchars works.1114

Well, let's say we were to go and get rid of that htmlspecialchars function call.1117

Now, if we try to load item 1002, and we click Show Item, you are going to see that all it shows is 42.1127

And why is that? Well, if we look at the source, and we blow it up, we can see that this quotation mark,1134

this double quote, was not turned into an entity.1141

So, HTML interpreted it as the closing quotation mark for this value attribute.1145

So, it sees the value of this input box as just being 42.1151

And then, it figures out how it is going to interpret the rest of this malformed HTML.1155

And so, that is why it shows only 42 in the box on our screen.1160

So, that is the importance of where htmlspecialchars comes in.1165

If we go ahead and redo the change, we are going to see that now, the quotation mark is going to show back up.1169

I think I might have just set it to 42; let me check.1193

Yes, that is why: it actually got set to just 42.1195

Let's reset it to LCD Television; if we update it, now, when we look back in our database, we can see that it is back to the way it was,1204

because we have updated and re-added that htmlspecialchars function.1217

When we go ahead and try to show item 1002, and it outputs a value, it is going to successfully output that quotation mark.1224

And we can see that it was turned into that HTML entity, &, quot, semicolon.1233

That is a function for taking information from a database and outputting it to a user, so that it is appropriately seen within HTML.1241

Now, we are going to talk about a function that is built into PHP, called addslashes, which is used1248

when you are taking user input from an HTML form, for example, and you are going to be including that1252

as part of a query to insert or update information in the database.1258

Well, as we know, with our strings, when we insert strings into the database--for example, if we had this INSERT INTO function--1262

let's say we have this test table, for example, and it takes one value.1271

Well, if want to insert the string "can't," we know that, for string data types, we have to enclose them in single quotes.1275

And if we want to include, as part of that string, a single quote, we have to escape it using the backslash, single quote character.1284

Well, that is what the addslashes function does.1291

Let's say we have a form--the same form where we were able to update the name of a particular item;1292

and let's say the user inputs that they want to have the name of the item include some data that is enclosed in single quotation marks.1299

Well, we want those single quotes stored as part of that string in the database.1310

However, when we create our query, we need to escape those, because in our query that we typically generate1314

within, for example, our PHP code, we just enclose it in single quotes.1319

Now, if this data gets inserted as is, it is going to show up like this, and that is going to cause a problem with our SQL query,1324

because now we have a malformed SQL query, because this single quote is part of the string we are trying to insert.1330

It is telling MySQL, "OK, this value ends right here," and then this part right here is going to be malformed, and it's going to cause an error.1341

So, we need to properly escape that.1351

So, what you can do is pass your user input as a string into this addslashes function,1353

and it is going to go ahead and look for any single quotes, and then double quotes as well, and properly escape them with the backslash--1358

replace all of the single quotes with the backslash, single quote, and all of the double quotes with the backslash, double quote.1366

If we look at our new version of updateItem.php...in our old version, the one we had just looked at,1373

in the section at the beginning, where we run the UPDATE query, in the data processing section (let me find it),1389

what we are doing is creating this UPDATE query, and we are just taking this name variable1407

(which is just the GET variable name that was supplied on the form, so it was whatever was supplied in this field right here,1412

which could include single quotes or double quotes), and we just output it as is into this query, without properly escaping it.1419

Well, in our new version, we do the same thing, except we take that name variable1430

and run it through that addslashes function, and then use that properly-escaped variable within our query string.1435

So, the functionality of the script is the same, except it has added that addslashes.1443

So, let's say we wanted to say 42", and then we wanted LCD to be in single quotation marks, then Television.1449

Oops, I'm on the wrong example.1459

Actually, let's update item 1003; I think it is a baseball bat.1469

Let's say, for whatever reason, we want our title to be enclosed in single quotes.1473

Well, first of all, let's verify right now in our database: the name associated with item 1003 is just Baseball, with no quotations around it.1477

If we want to update that, to have it in quotations, we can run our updateItem function.1491

Now, it is going to say it was successfully updated.1497

And if we look in our database, we can see that Baseball is now enclosed in single quotation marks.1499

Let's say, however, that we used the last version, that didn't have addslashes.1505

Going back to example 2, now when we try to update 1003, we are going to be able to successfully see it.1509

Just to change things up, let's put two single quotes at the beginning, with a space in between them.1521

Now, when we go to Update Item, the reason it didn't get an error was because these quotes cancel each other out.1529

Let's go back to example 3, where we can comment out the addslashes function.1540

If we go back to Item.php, item 1003, Baseball, is back to the way it was before, because that query I just ran changed it.1548

And if we go ahead and eliminate this addslashes function, now what is going to happen is:1558

when we try to...let's say we want to name it Baseball's; let's see...yes, we will just call it Baseball's with a single quote in it.1568

And when we go to Update Item, we don't have that addslashes; this isn't properly escaped--1583

it is going to cause a problem with our SQL syntax, and it is going to say there is an error with the UPDATE query.1586

If we go ahead and take a look at what that query looks like by just simply echoing it,1592

we will be able to see the problem that happened without using that addslashes method.1603

We can see that what happened here is: because our string, the new name that we included, Baseball's,1616

had a single quotation mark in it, when we tried to just include that in the query as is,1623

SQL is going to interpret this single quote that is a part of our string as the closing single quote for this name column.1630

And then, that is going to cause this to be a malformed SQL statement, and that is why it is going to throw an error.1639

When we add our addslashes back to it, what it is going to do is escape that.1644

So, if we go back to our script, re-add addslashes, and then take a look at the query that we have generated properly,1649

and we refresh, we are going to see that it was successfully updated.1661

And if we look, we can see that addslashes properly escaped that single quote character, so that the SQL statement was properly formed.1666

For the homework challenge, I want you to mimic what we did in this lecture,1677

except do it on the People database that we have been using in our homework examples.1683

I want you to create a script called updatePerson.php.1687

And it is going to follow the form that we used in the example today.1691

When you first load the form, if it doesn't have an action specified, it is going to present you with a text box1696

where you can enter the personID of the person you want to update.1701

When you submit the form, you are going to submit it with an action that says showPerson, for example.1705

And what that is going to do is: you are going to go ahead and run a SELECT query,1711

load that person from the database, and then output a name text box and an age text box1715

that allow you to update the name and age of that particular person.1721

One thing to note is that, in that name and age box, I want you to also output, as we did in our UPDATE example,1726

for example, item 1002, the current person's name and the current person's age,1734

because that is what you would expect on an UPDATE form.1746

And so, in order to do that, you are going to run a SELECT query that is going to allow you to pull that information out.1749

And then, you are going to output it as the value of those text boxes.1752

Then, when you provide an Update Person button, the person can click on that, and then it is going to go ahead1756

and run an UPDATE query that is going to update the name and age of the person to whatever the user entered on the form.1763

And so, the form is going to take a couple of different variables.1771

It is going to take an action variable, which is going to control whether you are initially just putting a text box1776

to say "What personID do you want us to load the person for?"; whether it is showing the person that allows you to edit it;1783

or whether it is actually performing the UPDATE action.1790

It will have a personID form variable, which is what you are going to enter to select a person to update.1794

And then, I want you to have two form variables that are going to be array form variables that are called person, and then name and age,1801

which refer correspondingly to the name and age of the person that you are trying to update.1809

And then, whenever the action form variable is not equal to showPerson or updatePerson, just output that single text box.1816

This is just an explanation of what I was saying is how to process the different parts of the form,1826

where you go from selecting the personID to the form where you can update its information,1833

and then the form where you actually click the button Update, where it is going to go and run the UPDATE query.1837

And so, when you do this, I want you to make sure you use the htmlspecialchars function.1843

You are going to need to use that on the page where you are showing the person,1849

where you are outputting those text boxes that show the current name and age of the person,1852

because maybe their name, for example, might contain a single quote.1856

And that may not be the best example for that, but it is an example of using that--of how to properly output text from a database.1860

I want you to use that on, for example, the part of the script where the action is showItem.1870

And then, on the part of the script where the action is updateItem, I want to make sure you use the addslashes method1876

to properly escape any name or age information that was submitted on the form by the user,1882

so that you don't get errors in your UPDATE statement.1889

And I would like you to, when you go ahead and update the name, for example, of a particular person--1891

go ahead and make up some name that contains a single quotation in it.1897

And that way, you can make sure that your addslashes works.1904

And try it with and without the addslashes function, and see that you get that error;1907

and then see that, when you do the addslashes, it appropriately escapes that query, and then it is able to successfully run.1910

And that should give you practice with running these UPDATE queries, and then how to use htmlspecialchars1916

to properly pull data from the database for output, and use addslashes to properly add data to the database.1924

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

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.