1
00:00:01,250 --> 00:00:08,740
We've successfully moved our
media catalog into a database.

2
00:00:08,740 --> 00:00:11,840
All the pages are now
working as they did before.

3
00:00:11,840 --> 00:00:15,010
However, we have a couple
places where queries are not

4
00:00:15,010 --> 00:00:17,110
as efficient as they could be.

5
00:00:17,110 --> 00:00:20,710
We have a number of functions that
are taking the full catalog array

6
00:00:20,710 --> 00:00:24,440
before using PHP to narrow down
the items to a smaller list.

7
00:00:25,490 --> 00:00:30,740
Instead, we should modify these functions
to use their own queries to retrieve

8
00:00:30,740 --> 00:00:33,300
only the items we need in the first place.

9
00:00:33,300 --> 00:00:37,450
For example, retrieve just the books or
just the movies.

10
00:00:38,730 --> 00:00:39,940
As we do this,

11
00:00:39,940 --> 00:00:43,960
we'll look at a number of different
ways to use sequel specific features.

12
00:00:43,960 --> 00:00:48,710
Like limits, ordering, and aggregate
functions to fine tune our queries.

13
00:00:49,940 --> 00:00:53,650
Let's start with using sequel to grab
the four random items for our home page.

14
00:00:54,950 --> 00:00:58,550
>> Launch the workspace associated
with this video and open index.php.

15
00:00:58,550 --> 00:01:03,460
We're only displaying four
random items on the home page.

16
00:01:03,460 --> 00:01:07,700
But we pulled the entire catalog
from the database before using PHP

17
00:01:07,700 --> 00:01:09,750
to select the random items.

18
00:01:09,750 --> 00:01:14,180
Instead let's create a new function
that uses a SQL query to pull for

19
00:01:14,180 --> 00:01:16,970
random items directly from the database.

20
00:01:16,970 --> 00:01:20,680
Let's open the functions.php file
from within the includes folder.

21
00:01:20,680 --> 00:01:23,374
We'll start by duplicating
the full catalog array.

22
00:01:27,114 --> 00:01:29,874
Then we'll rename it random catalog array.

23
00:01:34,634 --> 00:01:38,599
This function is going to work exactly
the same way as our full catalog array,

24
00:01:38,599 --> 00:01:41,650
with two small changes to the SQL query.

25
00:01:41,650 --> 00:01:46,740
We include our database connection,
query our database, fetch all results,

26
00:01:46,740 --> 00:01:48,940
then return those results
back to our function call.

27
00:01:50,060 --> 00:01:52,634
Let's break this query line
up by statement types.

28
00:02:00,074 --> 00:02:03,650
This will make it a little easier
to read as we add more statements.

29
00:02:03,650 --> 00:02:05,190
We'll start by adding an ORDER BY.

30
00:02:08,220 --> 00:02:12,600
Then we'll use the SQLite function,
RANDOM, just like in php

31
00:02:12,600 --> 00:02:16,300
when using a function, make sure you add
the opening and closing parenthesis.

32
00:02:17,350 --> 00:02:19,870
Then we'll add a limit and tell it 4.

33
00:02:19,870 --> 00:02:25,000
Now we're only selecting
four random items.

34
00:02:25,000 --> 00:02:28,950
If you're following along using
a local environment with my sequel,

35
00:02:28,950 --> 00:02:31,380
the function is called random.

36
00:02:31,380 --> 00:02:35,380
If you're using another database, make
sure you look up the specific syntax for

37
00:02:35,380 --> 00:02:36,960
your database.

38
00:02:36,960 --> 00:02:40,290
Now we need to use this
function on the index page.

39
00:02:40,290 --> 00:02:43,860
We'll save this file and
go back into index.php.

40
00:02:43,860 --> 00:02:46,130
We can remove this catalog.

41
00:02:46,130 --> 00:02:49,860
That calls our full catalog array, since
we're no longer using our full catalog.

42
00:02:51,040 --> 00:02:54,824
Then we go down to where we called
the built in function array_rand.

43
00:02:56,440 --> 00:03:00,420
Will change this to our
new random catalog.

44
00:03:02,380 --> 00:03:06,230
We're selecting the information from
our database within our new function.

45
00:03:06,230 --> 00:03:08,160
So we don't need to pass any arguments.

46
00:03:09,220 --> 00:03:13,180
If you want to take this as step further,
you could pass the number of random items.

47
00:03:13,180 --> 00:03:15,680
Make sure that you use a prepared
statement when accepting

48
00:03:15,680 --> 00:03:17,350
outside information.

49
00:03:17,350 --> 00:03:22,080
Our random variable now contains a full
array of items, instead of just the ids.

50
00:03:22,080 --> 00:03:25,254
So let's change id to item
in this foreach loop.

51
00:03:28,404 --> 00:03:33,697
We've already changed or get_item_html to
use the media id instead of the array key,

52
00:03:33,697 --> 00:03:37,930
so let's modify this function to
just pass the single variable Item.

53
00:03:41,620 --> 00:03:45,000
We also need to update our function so
it only receives one argument.

54
00:03:46,070 --> 00:03:53,840
Go back to functions, and
will scroll down to our get item HTML.

55
00:03:53,840 --> 00:03:56,460
We'll remove the ID and
just keep the item.

56
00:03:58,130 --> 00:04:01,470
Let's take a quick peek in the browser to
make sure our home page is still working.

57
00:04:03,400 --> 00:04:08,560
We refresh and the four random items
change, but if we try to go to the catalog

58
00:04:08,560 --> 00:04:14,710
page, we see that our changes to the get
HTML function broke our catalog pages.

59
00:04:14,710 --> 00:04:15,808
So let's fix those.

60
00:04:15,808 --> 00:04:19,421
Open catalog.php.

61
00:04:19,421 --> 00:04:21,090
Scroll down.

62
00:04:21,090 --> 00:04:25,510
And where we're using the get_item_html,

63
00:04:25,510 --> 00:04:28,210
we're going to pass just the item and
not the ID.

64
00:04:29,930 --> 00:04:33,230
Now we go back to our browser and
refresh our page.

65
00:04:34,780 --> 00:04:36,780
Great, our catalog pages
are working again.