Introductions5:24 with Hampton Paulk
Using parts of the Sakila Data to build out a listing of DVD titles we will learn how to communicate using PHP Data Objects or PDO for short. PDO uses object oriented methodology such as classes, objects, methods, and properties to read and write to the database which makes interfacing to databases very clean and efficient.
Now that you know the basics of the PHP language, 0:01 it's time for us to start talking about how to integrate with databases. 0:07 For this course, we're going to use a sample database that is structured to 0:11 fit the construct of a movie rental store. 0:14 This sample database is called the Sakila Database. 0:17 Using parts of the Sakila data, 0:20 we'll learn how to communicate using PHP data objects, or PDO for short. 0:22 PDO uses object-oriented methodology, such as classes, objects, methods, 0:26 and properties to read and write to the database, 0:32 which makes interfacing to databases very clean and efficient. 0:35 The table structure that we'll be working with is large and 0:39 covers not just films, but also actors in those films, 0:41 film categories, staff, customers, addresses, and even inventory. 0:45 It's a great example database to use for 0:50 testing and experimentation as you learn your way around databases. 0:52 But for us, our main focus will be in the film table. 0:56 Let's take a look at what is inside our film table. 1:00 If you open up the example workspace, next to this video, 1:04 you'll see that it starts off with three files, a database.db file, 1:07 an index.php file and a style sheet or style.css. 1:12 Our database file is local to the workspace, so it's great for you to be 1:17 able to play with and experiment and try out different things with the database. 1:20 It's also SQLite instead of MySQL so it's very light weight. 1:24 We'll be able to use most of the same query commands, especially for 1:28 this particular lesson. 1:31 So, if we wanna take a look at the database, we will hear in a second through 1:32 the console, but let's take a look at our index file and our style sheet. 1:35 Well, really just the index file. 1:39 You'll see it's just a standard HTML file, with the extension of .php. 1:41 There's actually no PHP code in it at the moment. 1:46 It is giving us a title, or a header saying the Sakila Sample Database. 1:49 Then a list of films by title, and you'll see film one through four. 1:54 If you go over and 1:58 hit Preview, you can see the title starts off with Sakila Sample Database, 1:59 Films by Title and then, Films One through Four in an ordered list. 2:04 These are just examples. 2:09 We'll replace that with the code from our database by the end of this. 2:10 So let's say we wanna take a look at our database. 2:14 I'm gonna go ahead and open up the console by clicking on View and 2:17 show console if it's not already visible. 2:21 I don't really need the index file. 2:23 I'm gonna make this a little bit bigger. 2:25 Okay. 2:27 So to go in here, our first command will be to open up the SQL like console. 2:28 The way we're gonna do that is by typing the command sqlite and 2:33 then the number 3 for the version here. 2:38 Hit Enter, that's our console. 2:41 And then, we can always type .help, and 2:43 that will give us a list of commands that we can use. 2:47 We're gonna use a command called .databases, 2:50 to show what databases are currently existing in the console. 2:52 I'm gonna hit Ctrl+L to clear the screen, and then I'm gonna hit .databases. 2:56 Hit Enter, and you'll see that it says, name is main, and 3:05 then there's no file currently attached. 3:09 So, we're gonna wanna open and 3:11 attach that particular database file that's in our workspace. 3:12 The way we're gonna do that so we can use the console, is to type the command .open 3:16 and then the file name, which I can just type, D-E-A-T, and hit Tab to complete it. 3:21 Hit Enter, and 3:28 now when we run up through the history twice, we can see .databases again. 3:29 Hit Enter and you'll see it's actually linking main to a file. 3:33 Gonna clear my screen again, and then type a new command .tables. 3:37 So, .t-a-b-l-e-s. 3:41 And then hit Enter, and you will see that we have several tables. 3:44 Now these tables are going to match up with what is in 3:47 the Sakila database example on their website. 3:50 Let's use their website to look at the structure a little bit more. 3:53 I'm gonna go over, start a new tab, and then go ahead and 3:57 search for Sakila database. 4:01 All right. Our first result would be 4:04 the MySQL Sakila Database. 4:05 And then we're gonna look at Structure, Tables, and then The film Table. 4:09 So you'll see here the film table, 4:18 if you scroll down, all of the columns that are available to you. 4:20 So those columns that are available are film ID, title, description, release year, 4:24 so on, so forth. 4:30 If we back to our index.php in the preview, 4:31 you'll see that here it'll list the films one, two, three, and four. 4:37 So those would be the titles. 4:41 So that's really what we need. 4:43 And then we're gonna wanna be able to click on any four of these, and that then 4:45 link off to a new page that shows the information to just one of the films. 4:50 You can always reference the Sakila Sample Database website, and 4:55 I'll put the link in the notes. 4:59 Okay, I don't need this anymore, I'm gonna click that. 5:01 I'm also gonna go ahead and close this. 5:04 And now that gives us a quick base of what our database structure looks like, 5:06 what tables we're gonna look at. 5:11 We're really just gonna concentrate on just the film table, and 5:13 then all of the columns that we're gonna be able to pull information from. 5:17 Our next step is to connect to the database using PHP. 5:20
You need to sign up for Treehouse in order to download course files.Sign up