Query by ID5:27 with Hampton Paulk
Continuing on we will fetch a single result by using the film id column and a url get variable.
Okay, blank file. 0:00 Switch back over to your index.php file. 0:02 I'm going to do a Cmd+ A here, Select All, or 0:05 you can do the drop down Edit and Select All. 0:08 And then I'm going to copy this and then paste it into my films.php file. 0:12 Now I don't need this same listing here. 0:19 So actually I'm gonna get rid of this ordered list altogether. 0:21 Okay. 0:25 And then here it's not gonna be films by title, 0:26 it's actually gonna be the film title itself. 0:29 Let's just say, Film Title. 0:31 All right, and then it's gonna say sample database. 0:35 This is all fine, scroll back up to the top. 0:38 So, I'm gonna save this file and go back and refresh, and 0:40 I shouldn't get the 404, you should get Sakila Sample Database and 0:44 the modification that we made, which is film title. 0:48 It's not the actual film title, but 0:52 we're gonna replace that now by doing an extra query here. 0:53 So instead of the 404, now you should see Sakila Sample Database. 0:58 Same as before, but instead, the small modification we made, which is Film Title. 1:02 We wanna populate this with the actual film title that uses the ID from the URL. 1:07 Let's do that by modifying our query. 1:13 Go back to films.php, and here, instead of select star from film, 1:17 okay, I want to select, star from film where, and 1:22 then it's going to be film_id equals, and then here we want to put the id in. 1:28 For now we'll just manually put one in we'll just say 201. 1:36 Hit Save, and then we're gonna have results, but 1:39 our result is really gonna it, it's gonna be one result we need not a fetch all. 1:43 Here we're gonna use a different method very similar. 1:48 We're just gonna get rid of the all, so it just says fetch. 1:51 And instead of films, we're gonna move this to just be one film. 1:55 So from here, 2:01 the next thing we need to do is modify our code at the bottom of this page. 2:02 And instead of saying film title, we're actually, 2:06 gonna open up our php tags and do echo and then $film. 2:11 So singular and then title. 2:16 Close that line and close our statement. 2:22 Okay, let's save our file, and then we'll go back up here, and 2:25 I will reopen the preview window, and that's gonna give us our main file but 2:29 then when we click on an actual film, you'll see it'll go use the film ID, 2:35 except we're not injecting the film ID into our query, 2:40 we're just saying film 201 which is cyclone family. 2:44 Let's say we'll do, a different film just for now. 2:48 Scroll up here and say 501. 2:51 Hit Save, head back over and refresh our preview. 2:54 Okay, that works for us. 3:00 Now, the last thing we wanna do is actually use 3:01 the number from our get variable. 3:05 So we'll go in here, right here on line four. 3:08 And we'll check to make sure that the getVar is actually present if 3:12 it's actually set. 3:16 We'll do that by opening up a conditional statement which is an if statement here. 3:19 And we're gonna say, if $_ capital G-E-T for our get. 3:22 And we're checking to see if the GET var of id is actually there. 3:28 And we're gonna see if it's not just present, we wanna see that it's not empty. 3:34 So not empty, using the empty keyword or empty method. 3:38 [BLANK_AUDIO] 3:42 All right, and then we're gonna open our curly braces. 3:45 And if it's not empty, meaning the id is actually present, we wanna set it and 3:47 do something with it. 3:51 So we'll say if, that it is not empty, we're gonna say film ID, 3:52 so setting a variable called film ID, and 3:58 we'll set it equal to this exact string up here, which is our GET var. 4:01 Just clean that up a little bit. 4:07 Close it out with a semicolon, and 4:10 then now we can simply concatenate the ID directly onto the end of this. 4:12 Save our file, and 4:22 let's switch back over to our preview, see if this works as expected. 4:23 So here's ID number one. 4:27 We hit Refresh, Academy Dinosaur. 4:29 Let's go back to our page, scroll down a little ways here. 4:31 Here is Tadpole Park, 874. 4:36 So we'll click on this. 4:37 It says film ID is equal to 874 and our title does show Tadpole Park. 4:39 All right. So just as a quick review, 4:46 we now have index.php. 4:48 The index.php file is running a query to fetch all the results from the film table. 4:51 And then we're going to create links using our anchor tags and 4:57 a looping construct of four each. 5:01 And then we are echoing out the film title creating a link to film.php or 5:03 films.php which we have created that file and then, then we're doing a new query and 5:08 there to select all of the films where the film ID is equal to the the GET var or 5:14 the film ID. 5:19 All right, up next we talk about something very, very important, security and 5:20 sequel injection. 5:25
You need to sign up for Treehouse in order to download course files.Sign up