Join David Powers for an in-depth discussion in this video Using the exercise files, part of PHP: Accessing Databases with PDO and MySQLi.
If you're a subscriber to the lynda.com online training library, you have access to the exercise files for this course. Just download them from the link on the course's page and unzip them to a convenient location. I've got them here on my desktop. The files are organized in folders corresponding to each chapter. Inside each chapter folder, there are separate folders for each video that uses exercise files. Sometimes the same file is used in several consecutive videos, but the folder relating to each video always contains the file in the state it needs to be in at the start of that video.
When changes are made to a file the version with end appended to the file name shows what the file looks like at the end of the video. Some folders also contain text files with snippets of code for you to copy and paste into the main file. Chapter one offers an overview of database connection in PHP. And describes two important features that lie at the heart of this course, prepared statements and transactions. The rest of the course is split into two halves; those are complete in themselves.
Chapters two, three, and four deal with PHP data objects, a database neutral abstraction layer that works with more than a dozen database systems. Chapters five, six, and seven are devoted to My SQL Improved, or My SQLI, which works only with My SQL or Maria DB. Both halfs use the same database tables and example.s I've avoided making direct comparisons between PDO and My SQLI.
So if you're interested in only one, you can focus on that without me constantly pointing out similarities and differences. If you're interested in both, you might like to hop between chapters. Chapters two and five cover roughly the same material, as do chapters three and six. Chapters four and seven focus on more advanced topics related to PDO and MySQLi respectively. In chapter eight, I briefly draw some comparisons between the two api's.
Because we're working with php, you'll need to copy these exercise files into your server root. I'm using a local testing server so I've copied them into a folder call OOPHP in my server's HT docs folder. So, they're here in ZAMP HT docs OOPHP. This includes folder, by the way, is not included in the exercise files. You create that yourself as part of the exercises. The example data is available in two formats, SQL Lite and MySQL.
The SQL Lite Data is in the SQL Lite folder, and is ready to use with chapters two, three and four. The MySQL data is in OOPHP.SQL, which is in the top level of the exercise files. Let's just go back to the Exercise files folder. Here it is oophp.sql. If you import this file into MySQL, it'll create a database called oophp and populated it with data. It also creates a user account called OOPHP with select, insert, update, and delete privileges on the OOPHP database only.
The account's password is lynda, all in lower case. So I'm going to use phpMyAdmin to import the data into mySQL. So here I'm in phpMyAdmin in the home page, I'll click the import tab at the top center, and then choose file. I need to go to my desktop and to the exercise files. There they are. And then oophp.sql, select that, open it and then just click Go at the bottom of the page in php my admin.
And this has created the OOPHP database and there inside are the four tables. The cars and makes tables contain details of used cars for sale. The names table contains a small selection of popular children's names and their meanings. And saving this contains some very simple data to be used to demonstrate transactions. The data and the structure is exactly the same as in the SQL light data file and you can use the MySQL data in all the exercise files, but the SQL light data works only with PDO.
So, those are the exercise files. Let's get on with the course.
- Connecting to a database with PDO or MySQLi
- Fetching a result set
- Executing simple non-SELECT queries
- Sanitizing user input
- Binding input and output values
- Passing an array of values to the execute() method
- Working with advanced PDO fetch methods
- Executing a MySQLi transaction
- Freeing resources that are no longer needed
- Submitting multiple queries
- Creating an instance of a class from a result set