Preview
Video Player
00:00
00:00
Working with Query Results
4:48In 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.
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