Working with Query Results4:48 with Alena Holligan
In this video, we’ll use our PDO Statement Object to return all the media items from the database in an associative array. This will allow us to use that data to display the media items on our site.
$results = $db->query("SELECT title, category FROM Media");
var_dump( $results->fetchAll(PDO::FETCH_ASSOC) );
We're currently using the fetchAll method on our PDO object. 0:00 This loads all of our media items from the database into an array variable. 0:04 That array resembles our catalog array. 0:09 Let's take a look at the documentation for fetchAll and 0:12 modify our query to more closely match our catalog array. 0:16 In a browser, when we view the page source on connection.php, 0:20 we can see that the fetchAll method returns a multi-dimensional array, 0:24 with one element for each item. 0:28 Each item then contains a secondary array with one element for 0:31 each of the item attributes, you'll notice that each piece of data shows up twice. 0:35 Once in an element with an associative key, like the catalog array uses and 0:40 once with an integer key. 0:45 This gives you flexibility when working with the data. 0:48 But we can change this so we just receive the format that works for our code. 0:51 Let's go back to the documentation for the fetchAll method. 0:56 The first optional argument is fetch_style. 0:59 This controls what the contents of the returned array are. 1:02 It says that we can use the fetch styles in the documentation on the fetch method. 1:06 Here's the documentation for the fetch styles. 1:13 The default here is FETCH_BOTH. 1:16 Default means that this is what's going to happen if we don't tell it to do something 1:19 else, and that's exactly what we see. 1:22 The fetch_both style returns an array indexed by column name and 1:25 0-indexed column number. 1:29 I won't cover all the fetch styles, but 1:32 there is one here called FETCH_NUM, this returns an array by column number. 1:34 That's part of what's returned by default, the half with the numbers. 1:40 But we don't want that. 1:44 Up here we have FETCH_ASSOC. 1:46 It returns an array index by column name, as returned in your result set. 1:48 Assoc is short for associative, as in an associative array. 1:54 That's the type of array we use in our current array catalog. 1:58 So let's copy this FETCH_ASSOC style. 2:02 We'll go back to our code and pass it to our fetchAll method call. 2:08 I won't be covering objects in a lot of depth in this course, but 2:14 we will be utilizing objects. 2:18 So I want to reiterate the basic syntax for objects. 2:20 A single arrow, the hyphen and 2:24 the greater than sign going between the actual object and the property or method. 2:26 Classes can have properties and 2:31 methods that don't require an actual instantiation of the class. 2:32 That's when you'll see these two colons. 2:37 They go in between the class name on the left and a property or 2:40 method, on the right. 2:43 For now what you need to know is how to retrieve the properties and 2:45 methods of a class. 2:48 In other words, its variables and functions. 2:50 If you have a specific instance of a class, you use the single arrow, 2:53 hyphen and greater than. 2:58 And if you call a property or method on the class itself, 2:59 you use the double colons. 3:03 Let's take a look at these changes back in the browser. 3:05 As you can see, about half the values are gone now, the values with the numeric 3:10 keys are gone and only the values with the associative keys remain. 3:14 We now have just two attributes for each product, title and category. 3:18 We have more information about each product in the database 3:23 that we'll need to retrieve. 3:25 Let's modify our query to pull one more piece of data, the image URL. 3:27 We still have more information in our database. 3:38 But the return value holds enough of the catalog array 3:41 to power all the listing pages on the site. 3:44 You might still notice some differences, for 3:47 example, the key at the top level of the array. 3:49 Here, each product is automatically assigned an integer, starting at zero. 3:52 In our catalog array, we specified our own key, 101, 3:57 102, 103 and so on, instead of 0, 1 and 2. 4:03 We set up the array with specific keys about the details of the item. 4:07 This allows us to use those keys to find a specific item in the catalog array. 4:12 With a database though, 4:17 we won't need to grab all the media items just to find a specific item. 4:19 There's a better way to just get the data we need for 4:24 a specific item when we query the database. 4:27 We also need to pull the rest of the item information for the details page, but 4:31 we'll tackle all of that shortly. 4:36 We all ready have all the information we need for the category and 4:38 the full catalog pages. 4:42 We're ready to look at what it will take to use these results for our library. 4:44
You need to sign up for Treehouse in order to download course files.Sign up