Result Sets

Now that we have successfully run a query, 0:00 we need to take a closer look at what is returned to us by our query method. 0:02 All of the return data comes back to us in the form of a PDO statement object. 0:06 Okay continuing, we're going to get rid of this exception that we have here. 0:12 It's refreshed I've already fixed it in the file. 0:17 So refresh this and we're back to our PDO statement object showing the query string. 0:19 And we need to extract the results from this. 0:25 We wanna get, get all of our films out of our PDO statement object. 0:29 And the first place I would say check is we go to our documentation and 0:34 look up the PDOStatement object. 0:38 I'm just gonna actually copy this and paste it and hit enter, and 0:40 that will take us to the manual page for the PHP PDOStatement. 0:46 So, here is our PDOStatement class. 0:51 It, says it represents a prepared statement and 0:54 after it is executed, the associated results set, so 0:57 that is what we're gonna work with is our associated results set. 1:01 We're gonna go on down and 1:05 look at the methods and the method that we're gonna want here. 1:06 Let's say we could do fetch, fetch, fetch all. 1:11 Yeah, that sounds good, fetch everything. 1:14 This says it returns an array containing all the result set rows. 1:16 So it's going to return to us an array, and 1:21 it doesn't have any required arguments. 1:25 So let's go ahead and run that method. 1:26 On our results object which is a instance of the PDOStatement object. 1:29 And back over to our code, we're gonna go down here and 1:35 we're gonna work with this results set. 1:38 So here I'm gonna continue to var_dump and 1:41 then dive, our result set, but we want to use the fetch all method. 1:43 Before we dig into our fetch all method and 1:49 see what it's gonna return to us, we wanna get rid of that error or 1:51 the possibility of an exception being thrown by the query being incorrect. 1:55 The way we're gonna do that is with wrapping it in a try catch block. 2:01 So we'll go ahead up here to line four, 2:05 come down a little bit, and wrap this in a try block. 2:07 Try no arguments. 2:11 I'm gonna tab this in. 2:14 All right. 2:18 Close this guy out, and then open up catch. 2:19 And we are still going to catch the Exception class, and 2:24 pass it through to an object $e, close it out. 2:29 And in here the same thing as before. 2:34 We're just going to echo out our exception object, which is $e. 2:36 And the method that we're going to use is going to be 2:42 the same as before, getMessage. 2:45 Okay, pass that through just to make sure that our tri catch block is working. 2:50 We'll again, get rid of our correct statement and then refresh. 2:55 And then now you'll see it says General Error. 3:00 That's saying it's our syntax error. 3:03 That's exactly what we wanted to see, 3:05 except we didn't want to see the rest of this. 3:07 So we need to add a die statement to the end of this. 3:10 Okay, so let's refresh again. 3:16 There we go much better. 3:19 It just kills it, and gives us the actual error itself. 3:20 That's exactly what we're looking for. 3:23 All right, let's go back in and fix our statement here. 3:26 Select, all right save that. 3:31 Double check that we're back to working. 3:35 And we are, great. 3:37 Now instead of just dumping the results, 3:39 we're going to use that method that we looked at which is fetch all. 3:41 So, object operator, fetch, capital A for fetchAll. 3:46 And then open and close parens with no arguments and let's go see what it does. 3:51 Hit refresh. 3:56 All right. We have an array with a thousand results. 3:58 That's a lot. 4:02 And it's really hard to look at right now, 4:02 so let's put some pre tags in here so we can see this a bit better. 4:04 Right before here we'll go ahead and do, echo. 4:09 And then open up some tags for pre. 4:15 Close it out. 4:17 I'm gonna copy this line. 4:18 I'm gonna head down here and close out my pre tag. 4:22 Let's go back and see if that makes it looks a little nicer. 4:26 Much better. 4:31 So, looking at our result set here again, we have a thousand results. 4:32 A lot of results. 4:35 We definitely would wanna paginate this in a production environment, but 4:36 again, this is just for examples and not to be put into production. 4:39 What do we have? 4:43 We have each one of the films it seems. 4:44 And a lot of information that looks duplicate. 4:48 Okay, so the only difference between them is the key. 4:50 So you see we have a film id and that's equal to a string of one. 4:54 And then we have a numerically indexed zero, so 4:59 the first index zero, with an id of one. 5:03 Double checking title. 5:07 Key of title, that's the academy dinosaur. 5:09 All right? 5:12 And then index one, which is a second key. 5:13 And that's the same, title as well. 5:17 So it looks like we have something indexed by column name and 5:19 something indexed by a numeric index. 5:22 So let's go back to our PDO statement, fetchAll. 5:25 Look at the manual, and the first optional argument is the fetch style, 5:29 which takes another argument called fetch argument. 5:35 Okay, so let's look down here at fetch style. 5:38 So it says fetch_style controls the content of the returned array as 5:43 documented in the Fetch documents. 5:47 So let's go to Fetch, see what that says. 5:49 Okay this is a result set, single row, fetch_style parameters. 5:53 Okay so the default here, it says, is returns and 5:59 array indexed by both column name and zero indexed column number. 6:02 That's what we have. 6:08 That's our default. 6:09 But the option to do just the associative array, is our first argument here. 6:10 Great, great. So that's PDO fetch. 6:15 ASSOC or associative. 6:18 So it returns an array index by column name as a return in your result set. 6:20 Let's do that. 6:25 So we wanna set fetch style to PDO FETCH_ASSOC. 6:26 So I'm gonna copy this here and 6:30 I'm gonna pass that through in our code here in our FetchAll statement. 6:32 Right here is our first argument. 6:37 Gonna paste in that class constant. 6:39 Hit Save. 6:42 Let's go back to our code and refresh. 6:43 Okay. 6:46 Still 1,000 results. 6:47 That's right. That's exactly what we were looking for. 6:49 And now all of them are indexed by column name. 6:52 So there's no miscellaneous zero through whatever for the keys. 6:55 It's just an associative array for each film, that's awesome. 6:59 So now we know that our result set is a set of films indexed by a column name, 7:03 so we should be able to build out our code a little bit. 7:09 So next we'll do that by providing some links, 7:12 work with these results, and create a little bit more of a website. 7:15