Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Integrating PHP with Databases!
You have completed Integrating PHP with Databases!
Preview
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