Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Video Player
00:00
00:00
00:00
- 2x 2x
- 1.75x 1.75x
- 1.5x 1.5x
- 1.25x 1.25x
- 1.1x 1.1x
- 1x 1x
- 0.75x 0.75x
- 0.5x 0.5x
Our details page is lacking any people associated to the particular item: such as Authors or Directors. We want to pull all the people, with their roles, and add them to our catalog in a multidimensional associative array.
Example Code
try {
$result = $db->prepare("
SELECT fullname,role
FROM Media_People
JOIN People ON Media_People.people_id=People.people_id
WHERE media_id = ?");
$result->bindParam(1,$id,PDO::PARAM_INT);
$result->execute();
} catch (Exception $e) {
echo "bad query";
echo $e;
}
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Back in Workspaces, open functions.php and
0:00
scroll back to our
single_item_array function.
0:02
Our single_item_array function
has one query right now
0:05
that retrieves data from the Media table,
Genre table and
0:10
optionally from the Books table,
but only if that data exists.
0:14
Let's add another query that will retrieve
the people associated with this item.
0:19
We'll need to add the people with
their roles to our item array,
0:24
in the same format that
they were in before.
0:27
Let's take a look at our data.php
file to review what the item array
0:31
used to look like.
0:35
Inside each item we had an array element
for each role, using the role as the key.
0:36
Inside that element, we had an array
with one element for each person.
0:44
We didn't specify the key, so they must
have been indexed zero, one and so on.
0:49
Let's go back to our functions.php file,
and the single item array function.
0:55
We can close this file.
1:01
We want to create the same structure here,
so
1:02
that we don't have to modify the code
in our details page very much.
1:05
We get the main item details here.
1:09
If we have an item at this point,
we also want to look up people.
1:12
But if no item matches the id,
there's no need to look any further.
1:16
I'm going to change this
variable from catalog to item,
1:20
since we're only returning one item.
1:23
If our item variable is a Boolean false,
1:31
we could actually just return that
false without proceeding any further.
1:33
We can use a conditional to check for
that, just like we do in the details page.
1:38
if (empty($item))
1:42
return $item;.
1:48
This is called an early return.
1:53
Once you have everything
you need from the function,
1:55
you can put a return here
to return that value.
1:58
You can have multiple return
statements in a function.
2:01
And once one is encountered,
then the function returns that value and
2:04
stops executing anymore code.
2:08
You'll often see multiple return
statements nested in conditionals, so
2:10
that only one of them will be executed.
2:14
If a conditional only has one command you
don't technically need the curly brackets
2:17
or hard returns.
2:22
This single line conditional
is perfectly valid.
2:23
I don't usually like to write
conditionals like this, because it can be
2:26
confusing to scan and see exactly what
code is contained within the conditional.
2:29
But an early return with only one
command inside the conditional,
2:34
is a perfect case to use this style.
2:38
If no item matches the id, we do
an early return with a Boolean false.
2:40
If we do have an item though,
2:46
we need to add the people to the item
array along with their roles.
2:47
So we'll want to query the database for
a list of people and roles.
2:52
The code to run this query will look
pretty similar to what we've seen before.
2:55
So, let's copy our try
catch block from above.
2:59
We have already included
the database file, so
3:10
we can keep using the db variable.
3:13
We'll be looking up people
using our media people table,
3:15
and the media_id that
we've received as input.
3:18
So we'll need a prepared statement again.
3:20
We only need to select two columns.
3:23
Fullname and role.
3:29
We first select from
our Media_People table.
3:33
Then we'll JOIN our people table.
3:37
We'll join people on media, people-id.
3:42
We'll join this to, People.
3:50
People_id.
3:56
We can remove our LEFT OUTER JOIN,
but keep the WHERE.
3:58
We'll need to change the table
name in our WHERE clause.
4:03
Media_People.
4:08
We'll use the same placeholder for
the id as we did above,
4:11
so our buying statement stays the same.
4:14
And of course, we execute the query.
4:17
This will retrieve all the people
linked to this particular item, and
4:20
put them in the results object.
4:24
Next we need to format these results.
4:27
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up