1
00:00:00,430 --> 00:00:04,990
We have our people and roles in a result
object and we need to add them to our

2
00:00:04,990 --> 00:00:09,120
item variable, so that we can return
the back to our function call.

3
00:00:09,120 --> 00:00:12,430
We could use the fetch all method
here to retrieve all the authors,

4
00:00:12,430 --> 00:00:15,610
but they wouldn't be in
the exact format we need,

5
00:00:15,610 --> 00:00:18,750
because each author is
a row in the database.

6
00:00:18,750 --> 00:00:21,290
Fetch all would give us a whole array for

7
00:00:21,290 --> 00:00:24,500
each author instead of
just a simple variable.

8
00:00:24,500 --> 00:00:29,655
If you want the results to be anything
besides a simple array, you'll need to

9
00:00:29,655 --> 00:00:34,911
add a while loop to go through the results
one at a time and format them as you go.

10
00:00:34,911 --> 00:00:37,411
Let me show you how that works.

11
00:00:37,411 --> 00:00:42,660
Back in functions.php, we execute the
query here at the end of the try block.

12
00:00:43,820 --> 00:00:49,071
After this, we can move down
here below the try catch block.

13
00:00:49,071 --> 00:00:52,571
We can load the first person
into a variable like this.

14
00:01:05,051 --> 00:01:10,698
We can fetch every row one at a time by
wrapping the statement in a while loop,

15
00:01:10,698 --> 00:01:11,592
like this.

16
00:01:15,951 --> 00:01:18,054
This loop might seem a little strange, so

17
00:01:18,054 --> 00:01:21,370
let me walk you through
exactly what's happening.

18
00:01:21,370 --> 00:01:24,640
Remember that a while loop
is like a conditional,

19
00:01:24,640 --> 00:01:29,330
except that it runs multiple times
while the condition is true.

20
00:01:29,330 --> 00:01:32,500
This condition is different
than an if statement,

21
00:01:32,500 --> 00:01:35,240
because it doesn't just
compare two values.

22
00:01:35,240 --> 00:01:38,290
It actually executes a command.

23
00:01:38,290 --> 00:01:43,500
It calls the fetch method and
loads the return value into a variable.

24
00:01:43,500 --> 00:01:48,530
The variable row will now contain
the first person and role.

25
00:01:48,530 --> 00:01:53,271
As long as that row variable is not false,
then the condition will be considered

26
00:01:53,271 --> 00:01:56,971
true and the code inside the curly
brackets will be executed.

27
00:01:56,971 --> 00:02:01,671
When the end of the while loop is reached,
the flow loops back to the top.

28
00:02:01,671 --> 00:02:04,091
It again calls the fetch method.

29
00:02:04,091 --> 00:02:08,790
Which this time, loads the second
person and roll into the row variable.

30
00:02:09,900 --> 00:02:12,850
Because the row variable
is still not false,

31
00:02:12,850 --> 00:02:14,780
it runs the code inside the loop again.

32
00:02:15,860 --> 00:02:17,600
At the end, it loops back and

33
00:02:17,600 --> 00:02:21,680
calls the fetch method a third time
loading the third person enroll

34
00:02:21,680 --> 00:02:25,820
into the row variable and
executing the code inside the while loop.

35
00:02:26,950 --> 00:02:30,051
Let's say,
this particular item has three people.

36
00:02:30,051 --> 00:02:33,325
After executing this block of code for
the third time,

37
00:02:33,325 --> 00:02:36,612
it would loop backup to the top
of the condition again.

38
00:02:36,612 --> 00:02:40,368
It will call the fetch method again,
which this time returns false,

39
00:02:40,368 --> 00:02:43,190
because there are no more people to fetch.

40
00:02:43,190 --> 00:02:45,591
It already fetched all three of them.

41
00:02:45,591 --> 00:02:51,832
In this case, the row variable gets
assigned a value of Boolean false.

42
00:02:51,832 --> 00:02:54,770
The while loop sees the row is false and
it now and

43
00:02:54,770 --> 00:02:59,050
the while loop, and skips down to
the final closing curly brackets.

44
00:03:00,170 --> 00:03:04,222
Another way to describe
the while loop is like this.

45
00:03:04,222 --> 00:03:06,642
Grab a record from the results.

46
00:03:06,642 --> 00:03:11,710
And as long as there is a record to grab,
do something with that record.

47
00:03:11,710 --> 00:03:14,350
When there are no more records to grab,
you're done.

48
00:03:15,580 --> 00:03:17,334
Inside the while loop,

49
00:03:17,334 --> 00:03:21,902
we want to add a new internal
array to the item array variable.

50
00:03:21,902 --> 00:03:26,993
Remember that each detail about the item,
including the people and

51
00:03:26,993 --> 00:03:31,021
roles is represented by
an element inside the array.

52
00:03:31,021 --> 00:03:35,001
The elements that represent people
are different than the other elements,

53
00:03:35,001 --> 00:03:37,650
because they are also in array.

54
00:03:37,650 --> 00:03:39,830
An array nested inside of an array.

55
00:03:41,000 --> 00:03:45,080
The key to this internal
array is the role and

56
00:03:45,080 --> 00:03:49,137
each item in that internal array
will have its own auto-assign key.

57
00:03:50,160 --> 00:03:53,696
To add a new element to an existing array,
you use an opening and

58
00:03:53,696 --> 00:03:55,181
closing square bracket.

59
00:03:55,181 --> 00:04:00,620
So, to item we will add another
key element corresponding

60
00:04:00,620 --> 00:04:04,841
to the role that we pull
from the current row.

61
00:04:04,841 --> 00:04:12,200
$Item our [$row,
the ["role"]] then we need an opening and

62
00:04:12,200 --> 00:04:18,281
closing square bracket to
signify a new array element.

63
00:04:18,281 --> 00:04:22,981
We're going to assign this element the
value of fullname from the current row.

64
00:04:28,921 --> 00:04:32,284
This will add a new element to
the second dimension of our new key

65
00:04:32,284 --> 00:04:34,060
in the item array.

66
00:04:34,060 --> 00:04:38,610
By adding the array in this way,
we organize the people as we go.

67
00:04:38,610 --> 00:04:43,030
The authors will be added to an author
array, artist will be added to an artist's

68
00:04:43,030 --> 00:04:48,240
array and each item will only contain
the roles that have people assigned.

69
00:04:48,240 --> 00:04:51,320
Book items will get authors,
but not artists.

70
00:04:51,320 --> 00:04:55,060
And music will get artists,
but not authors.

71
00:04:55,060 --> 00:04:56,300
Let's recap this code.

72
00:04:57,420 --> 00:05:03,290
If our first query doesn't find an item
that matches the ID, we return false.

73
00:05:03,290 --> 00:05:07,260
If we do find an item, we then run
this query against the database

74
00:05:07,260 --> 00:05:09,170
to get a list of all the people in roles.

75
00:05:10,430 --> 00:05:13,730
We then move through those
people one at a time and

76
00:05:13,730 --> 00:05:16,310
add them to the correct
role in our item array.

77
00:05:17,370 --> 00:05:21,230
Finally, we return the item
array back to the function call

78
00:05:21,230 --> 00:05:25,340
with all the item details including
the people with their roles, so

79
00:05:25,340 --> 00:05:27,250
that everything can be
displayed on the website.

80
00:05:28,500 --> 00:05:31,905
I did change things a little
from the original array to make

81
00:05:31,905 --> 00:05:34,212
them more standardized and flexible.

82
00:05:34,212 --> 00:05:37,111
So, we'll need to change our
details page just a little bit.

83
00:05:39,191 --> 00:05:41,450
All our roles are now singular.

84
00:05:41,450 --> 00:05:47,092
So let's remove the S from authors,

85
00:05:47,092 --> 00:05:50,186
writers and stars.

86
00:05:56,846 --> 00:06:01,489
I also set up artist and
director like all the other people, so

87
00:06:01,489 --> 00:06:03,826
they could have more than one.

88
00:06:03,826 --> 00:06:07,286
Let's add the implode statement
to those lines as well

89
00:06:23,366 --> 00:06:26,327
Now, we're ready to take
a look at this in the browser.

90
00:06:28,366 --> 00:06:33,986
Once we refresh the page, we now see
that all the items are populated.

91
00:06:33,986 --> 00:06:35,966
We do have our array displayed at the top.

92
00:06:35,966 --> 00:06:37,706
So, let's remove that real quick.

93
00:06:44,626 --> 00:06:46,886
Great.

94
00:06:46,886 --> 00:06:49,786
We have our item details
page working again.

95
00:06:49,786 --> 00:06:52,807
And now, all of the data is
coming from the database.

96
00:06:52,807 --> 00:06:54,526
We've looked at joining tables.

97
00:06:54,526 --> 00:06:58,760
We covered how to use the PDO classes
fetch methods effectively and

98
00:06:58,760 --> 00:07:02,547
we discussed how to filter
input to prevent SQL injection.

99
00:07:02,547 --> 00:07:03,470
Very nice work.

100
00:07:04,710 --> 00:07:05,470
Everything's working,

101
00:07:05,470 --> 00:07:10,110
but we have a few places where we should
improve the integration with our database.

102
00:07:10,110 --> 00:07:14,950
We just set up our full catalog array
function to generate our catalog array and

103
00:07:14,950 --> 00:07:15,620
then we turn around,

104
00:07:15,620 --> 00:07:19,730
and send that catalog to another function
that only uses part of the catalog.

105
00:07:20,770 --> 00:07:24,790
The database can find the items we're
after much more efficiently and

106
00:07:24,790 --> 00:07:27,020
faster than our current method.

107
00:07:27,020 --> 00:07:30,390
So, we'll create a new SQL
query that let us retrieve

108
00:07:30,390 --> 00:07:32,530
very specific information
from the database.

109
00:07:33,800 --> 00:07:37,460
We're also going to add some new features,
pagination and search.