- Understanding Relationship Tables 3:41
- Relational Data 5 questions
- Querying Multiple Tables with JOIN 4:02
- JOINing Tables 1 objective
- Understanding SQL Injections 5:21
- SQL Injection 1 question
- Preparing SQL Statements 6:25
- Using a Prepared Statement 3 objectives
- Adding a Secondary Query 4:30
- Fetching in a While Loop 7:38
- Fetching Many Relationships 1 objective
Preview
Video Player
00:00
00:00
Fetching in a While Loop
7:38We have our people and roles in the results object and we need to add them to the item variable so that we can return them back to our function call. We could use the fetchAll method here to retrieve all the authors, but they wouldn't be in the exact format we need. Because each author is a row in the database, fetchAll would give us a whole array for each author, instead of just a simple variable. If you want the results to be anything besides a simple array, you'll need to use a while loop to go through the results one at a time and format them as you go.
We have our people and roles in a result
object and we need to add them to our
0:00
item variable, so that we can return
the back to our function call.
0:04
We could use the fetch all method
here to retrieve all the authors,
0:09
but they wouldn't be in
the exact format we need,
0:12
because each author is
a row in the database.
0:15
Fetch all would give us a whole array for
0:18
each author instead of
just a simple variable.
0:21
If you want the results to be anything
besides a simple array, you'll need to
0:24
add a while loop to go through the results
one at a time and format them as you go.
0:29
Let me show you how that works.
0:34
Back in functions.php, we execute the
query here at the end of the try block.
0:37
After this, we can move down
here below the try catch block.
0:43
We can load the first person
into a variable like this.
0:49
We can fetch every row one at a time by
wrapping the statement in a while loop,
1:05
like this.
1:10
This loop might seem a little strange, so
1:15
let me walk you through
exactly what's happening.
1:18
Remember that a while loop
is like a conditional,
1:21
except that it runs multiple times
while the condition is true.
1:24
This condition is different
than an if statement,
1:29
because it doesn't just
compare two values.
1:32
It actually executes a command.
1:35
It calls the fetch method and
loads the return value into a variable.
1:38
The variable row will now contain
the first person and role.
1:43
As long as that row variable is not false,
then the condition will be considered
1:48
true and the code inside the curly
brackets will be executed.
1:53
When the end of the while loop is reached,
the flow loops back to the top.
1:56
It again calls the fetch method.
2:01
Which this time, loads the second
person and roll into the row variable.
2:04
Because the row variable
is still not false,
2:09
it runs the code inside the loop again.
2:12
At the end, it loops back and
2:15
calls the fetch method a third time
loading the third person enroll
2:17
into the row variable and
executing the code inside the while loop.
2:21
Let's say,
this particular item has three people.
2:26
After executing this block of code for
the third time,
2:30
it would loop backup to the top
of the condition again.
2:33
It will call the fetch method again,
which this time returns false,
2:36
because there are no more people to fetch.
2:40
It already fetched all three of them.
2:43
In this case, the row variable gets
assigned a value of Boolean false.
2:45
The while loop sees the row is false and
it now and
2:51
the while loop, and skips down to
the final closing curly brackets.
2:54
Another way to describe
the while loop is like this.
3:00
Grab a record from the results.
3:04
And as long as there is a record to grab,
do something with that record.
3:06
When there are no more records to grab,
you're done.
3:11
Inside the while loop,
3:15
we want to add a new internal
array to the item array variable.
3:17
Remember that each detail about the item,
including the people and
3:21
roles is represented by
an element inside the array.
3:26
The elements that represent people
are different than the other elements,
3:31
because they are also in array.
3:35
An array nested inside of an array.
3:37
The key to this internal
array is the role and
3:41
each item in that internal array
will have its own auto-assign key.
3:45
To add a new element to an existing array,
you use an opening and
3:50
closing square bracket.
3:53
So, to item we will add another
key element corresponding
3:55
to the role that we pull
from the current row.
4:00
$Item our [$row,
the ["role"]] then we need an opening and
4:04
closing square bracket to
signify a new array element.
4:12
We're going to assign this element the
value of fullname from the current row.
4:18
This will add a new element to
the second dimension of our new key
4:28
in the item array.
4:32
By adding the array in this way,
we organize the people as we go.
4:34
The authors will be added to an author
array, artist will be added to an artist's
4:38
array and each item will only contain
the roles that have people assigned.
4:43
Book items will get authors,
but not artists.
4:48
And music will get artists,
but not authors.
4:51
Let's recap this code.
4:55
If our first query doesn't find an item
that matches the ID, we return false.
4:57
If we do find an item, we then run
this query against the database
5:03
to get a list of all the people in roles.
5:07
We then move through those
people one at a time and
5:10
add them to the correct
role in our item array.
5:13
Finally, we return the item
array back to the function call
5:17
with all the item details including
the people with their roles, so
5:21
that everything can be
displayed on the website.
5:25
I did change things a little
from the original array to make
5:28
them more standardized and flexible.
5:31
So, we'll need to change our
details page just a little bit.
5:34
All our roles are now singular.
5:39
So let's remove the S from authors,
5:41
writers and stars.
5:47
I also set up artist and
director like all the other people, so
5:56
they could have more than one.
6:01
Let's add the implode statement
to those lines as well
6:03
Now, we're ready to take
a look at this in the browser.
6:23
Once we refresh the page, we now see
that all the items are populated.
6:28
We do have our array displayed at the top.
6:33
So, let's remove that real quick.
6:35
Great.
6:44
We have our item details
page working again.
6:46
And now, all of the data is
coming from the database.
6:49
We've looked at joining tables.
6:52
We covered how to use the PDO classes
fetch methods effectively and
6:54
we discussed how to filter
input to prevent SQL injection.
6:58
Very nice work.
7:02
Everything's working,
7:04
but we have a few places where we should
improve the integration with our database.
7:05
We just set up our full catalog array
function to generate our catalog array and
7:10
then we turn around,
7:14
and send that catalog to another function
that only uses part of the catalog.
7:15
The database can find the items we're
after much more efficiently and
7:20
faster than our current method.
7:24
So, we'll create a new SQL
query that let us retrieve
7:27
very specific information
from the database.
7:30
We're also going to add some new features,
pagination and search.
7:33