1 00:00:01,600 --> 00:00:07,380 Now that you know the basics of the PHP language, 2 00:00:07,380 --> 00:00:11,260 it's time for us to start talking about how to integrate with databases. 3 00:00:11,260 --> 00:00:14,550 For this course, we're going to use a sample database that is structured to 4 00:00:14,550 --> 00:00:17,600 fit the construct of a movie rental store. 5 00:00:17,600 --> 00:00:20,850 This sample database is called the Sakila Database. 6 00:00:20,850 --> 00:00:22,510 Using parts of the Sakila data, 7 00:00:22,510 --> 00:00:26,528 we'll learn how to communicate using PHP data objects, or PDO for short. 8 00:00:26,528 --> 00:00:32,080 PDO uses object-oriented methodology, such as classes, objects, methods, 9 00:00:32,080 --> 00:00:35,170 and properties to read and write to the database, 10 00:00:35,170 --> 00:00:39,110 which makes interfacing to databases very clean and efficient. 11 00:00:39,110 --> 00:00:41,940 The table structure that we'll be working with is large and 12 00:00:41,940 --> 00:00:45,340 covers not just films, but also actors in those films, 13 00:00:45,340 --> 00:00:50,500 film categories, staff, customers, addresses, and even inventory. 14 00:00:50,500 --> 00:00:52,460 It's a great example database to use for 15 00:00:52,460 --> 00:00:56,960 testing and experimentation as you learn your way around databases. 16 00:00:56,960 --> 00:01:00,290 But for us, our main focus will be in the film table. 17 00:01:00,290 --> 00:01:03,000 Let's take a look at what is inside our film table. 18 00:01:04,270 --> 00:01:07,960 If you open up the example workspace, next to this video, 19 00:01:07,960 --> 00:01:12,456 you'll see that it starts off with three files, a database.db file, 20 00:01:12,456 --> 00:01:17,990 an index.php file and a style sheet or style.css. 21 00:01:17,990 --> 00:01:20,690 Our database file is local to the workspace, so it's great for you to be 22 00:01:20,690 --> 00:01:24,410 able to play with and experiment and try out different things with the database. 23 00:01:24,410 --> 00:01:28,310 It's also SQLite instead of MySQL so it's very light weight. 24 00:01:28,310 --> 00:01:31,220 We'll be able to use most of the same query commands, especially for 25 00:01:31,220 --> 00:01:32,910 this particular lesson. 26 00:01:32,910 --> 00:01:35,870 So, if we wanna take a look at the database, we will hear in a second through 27 00:01:35,870 --> 00:01:39,430 the console, but let's take a look at our index file and our style sheet. 28 00:01:39,430 --> 00:01:41,790 Well, really just the index file. 29 00:01:41,790 --> 00:01:46,080 You'll see it's just a standard HTML file, with the extension of .php. 30 00:01:46,080 --> 00:01:49,400 There's actually no PHP code in it at the moment. 31 00:01:49,400 --> 00:01:54,570 It is giving us a title, or a header saying the Sakila Sample Database. 32 00:01:54,570 --> 00:01:58,070 Then a list of films by title, and you'll see film one through four. 33 00:01:58,070 --> 00:01:59,030 If you go over and 34 00:01:59,030 --> 00:02:04,870 hit Preview, you can see the title starts off with Sakila Sample Database, 35 00:02:04,870 --> 00:02:09,090 Films by Title and then, Films One through Four in an ordered list. 36 00:02:09,090 --> 00:02:10,000 These are just examples. 37 00:02:10,000 --> 00:02:14,220 We'll replace that with the code from our database by the end of this. 38 00:02:14,220 --> 00:02:17,300 So let's say we wanna take a look at our database. 39 00:02:17,300 --> 00:02:21,140 I'm gonna go ahead and open up the console by clicking on View and 40 00:02:21,140 --> 00:02:23,700 show console if it's not already visible. 41 00:02:23,700 --> 00:02:25,490 I don't really need the index file. 42 00:02:25,490 --> 00:02:27,440 I'm gonna make this a little bit bigger. 43 00:02:27,440 --> 00:02:28,290 Okay. 44 00:02:28,290 --> 00:02:33,310 So to go in here, our first command will be to open up the SQL like console. 45 00:02:33,310 --> 00:02:38,960 The way we're gonna do that is by typing the command sqlite and 46 00:02:38,960 --> 00:02:41,550 then the number 3 for the version here. 47 00:02:41,550 --> 00:02:43,550 Hit Enter, that's our console. 48 00:02:43,550 --> 00:02:47,610 And then, we can always type .help, and 49 00:02:47,610 --> 00:02:50,010 that will give us a list of commands that we can use. 50 00:02:50,010 --> 00:02:52,620 We're gonna use a command called .databases, 51 00:02:52,620 --> 00:02:55,830 to show what databases are currently existing in the console. 52 00:02:56,830 --> 00:03:05,280 I'm gonna hit Ctrl+L to clear the screen, and then I'm gonna hit .databases. 53 00:03:05,280 --> 00:03:09,070 Hit Enter, and you'll see that it says, name is main, and 54 00:03:09,070 --> 00:03:11,080 then there's no file currently attached. 55 00:03:11,080 --> 00:03:12,990 So, we're gonna wanna open and 56 00:03:12,990 --> 00:03:16,540 attach that particular database file that's in our workspace. 57 00:03:16,540 --> 00:03:21,920 The way we're gonna do that so we can use the console, is to type the command .open 58 00:03:21,920 --> 00:03:26,590 and then the file name, which I can just type, D-E-A-T, and hit Tab to complete it. 59 00:03:28,360 --> 00:03:29,050 Hit Enter, and 60 00:03:29,050 --> 00:03:33,630 now when we run up through the history twice, we can see .databases again. 61 00:03:33,630 --> 00:03:37,120 Hit Enter and you'll see it's actually linking main to a file. 62 00:03:37,120 --> 00:03:41,405 Gonna clear my screen again, and then type a new command .tables. 63 00:03:41,405 --> 00:03:44,740 So, .t-a-b-l-e-s. 64 00:03:44,740 --> 00:03:47,850 And then hit Enter, and you will see that we have several tables. 65 00:03:47,850 --> 00:03:50,820 Now these tables are going to match up with what is in 66 00:03:50,820 --> 00:03:53,790 the Sakila database example on their website. 67 00:03:53,790 --> 00:03:57,430 Let's use their website to look at the structure a little bit more. 68 00:03:57,430 --> 00:04:01,010 I'm gonna go over, start a new tab, and then go ahead and 69 00:04:01,010 --> 00:04:02,960 search for Sakila database. 70 00:04:04,480 --> 00:04:05,840 All right. Our first result would be 71 00:04:05,840 --> 00:04:07,780 the MySQL Sakila Database. 72 00:04:09,000 --> 00:04:16,790 And then we're gonna look at Structure, Tables, and then The film Table. 73 00:04:18,940 --> 00:04:20,940 So you'll see here the film table, 74 00:04:20,940 --> 00:04:24,830 if you scroll down, all of the columns that are available to you. 75 00:04:24,830 --> 00:04:30,400 So those columns that are available are film ID, title, description, release year, 76 00:04:30,400 --> 00:04:31,800 so on, so forth. 77 00:04:31,800 --> 00:04:35,960 If we back to our index.php in the preview, 78 00:04:37,970 --> 00:04:41,500 you'll see that here it'll list the films one, two, three, and four. 79 00:04:41,500 --> 00:04:43,130 So those would be the titles. 80 00:04:43,130 --> 00:04:45,280 So that's really what we need. 81 00:04:45,280 --> 00:04:50,280 And then we're gonna wanna be able to click on any four of these, and that then 82 00:04:50,280 --> 00:04:55,860 link off to a new page that shows the information to just one of the films. 83 00:04:55,860 --> 00:04:59,220 You can always reference the Sakila Sample Database website, and 84 00:04:59,220 --> 00:05:01,745 I'll put the link in the notes. 85 00:05:01,745 --> 00:05:04,560 Okay, I don't need this anymore, I'm gonna click that. 86 00:05:04,560 --> 00:05:06,620 I'm also gonna go ahead and close this. 87 00:05:06,620 --> 00:05:11,470 And now that gives us a quick base of what our database structure looks like, 88 00:05:11,470 --> 00:05:13,110 what tables we're gonna look at. 89 00:05:13,110 --> 00:05:17,070 We're really just gonna concentrate on just the film table, and 90 00:05:17,070 --> 00:05:20,360 then all of the columns that we're gonna be able to pull information from. 91 00:05:20,360 --> 00:05:23,940 Our next step is to connect to the database using PHP.