PHP: Accessing Databases with PDO and MySQLi

Introduction

• Welcome
• What you should know before watching this course
• Using the exercise files
• Setting SQLite permissions
• A quick primer on using PHP objects

1. Why Use Object-Oriented PHP to Access a Database?

• Overview of PHP database APIs
• Using prepared statements
• Using transactions

2. PHP Data Object (PDO) Basics

• Creating a database source name
• Connecting to a database with PDO
• Looping directly over a SELECT query
• Fetching a result set
• Finding the number of results from a SELECT query
• Checking if a SELECT query contains results
• Executing simple non-SELECT queries
• Getting error messages
• Using the quote() method to sanitize user input

3. PDO-Prepared Statements and Transactions

• Binding input and output values
• Using named parameters
• Using question marks as anonymous placeholders
• Passing an array of values to the execute() method
• Binding results to variables
• Executing a transaction
• Closing the cursor before running another query

4. Advanced PDO Fetch Methods

• Generating an array from a pair of columns
• Setting an existing object's properties with a database result
• Creating an instance of a specific class with a database result
• Reusing a result set

5. MySQL Improved Basics

• Connecting to a database with MySQLi
• Setting the character set
• Submitting a SELECT query and getting the number of results
• Fetching the result
• Rewinding the result for reuse
• Handling non-SELECT queries
• Getting error messages
• Sanitizing user input with real_escape_string()

6. MySQLi Prepared Statements and Transactions

• Initializing and preparing a statement
• Binding parameters and executing a prepared statement
• Binding output variables
• Executing a MySQLi transaction
• Dealing with "commands out of sync" in prepared statements

7. Diving Deeper into MySQLi

• Buffered and unbuffered queries
• Using real_query()
• Freeing resources that are no longer needed
• Submitting multiple queries
• Creating an instance of a class from a result set

Conclusion

• PDO and MySQLi compared