Preview
Video Player
00:00
00:00
Querying the Database
5:20Out of all of the interactions performed on a database, the most often used command is a select statement. The select statement asks our database for data and retrieves those results. We’ll also look at how to handle errors with a query.
[SOUND] We left off last
time with PHP code that
0:00
opened a connection to
our SQLite database.
0:04
In this section, we'll be
retrieving data from the database.
0:09
We'll use that data in place of
the current associative array.
0:13
We'll start off by retrieving
all the results and
0:17
using those results to populate the array.
0:20
Then, we'll modify our functions to
retrieve only the specific data that
0:23
we'll be working with.
0:27
For example, on the home page,
0:30
each time the page is loaded
we show four random items.
0:32
Currently we include
the entire array of items
0:36
then use PHP to only display
four of those items.
0:39
We'll change our code to only retrieve
four items from the database.
0:44
By starting off with a smaller subset of
data, we reduce memory consumption and
0:48
increase speed.
0:53
Performance will be much more noticeable
on a giant site like Amazon than
0:55
it will be on a smaller site,
like our personal media library.
0:59
But by making these changes now, will
dramatically increase the scalability, and
1:03
usability of our site.
1:08
If your site takes longer than a few
seconds to load, you're losing visitors.
1:10
Out of all the interactions performed on
a database the most often used command
1:16
is select.
1:21
A select statement is a query on our
database to select some data and
1:21
return those results.
1:26
We'll take a closer look at how to
use PDO to perform these queries.
1:28
And what information these queries return.
1:32
I'm going to repeat myself here.
1:36
You should always be prepared to catch
errors when something goes wrong.
1:38
Give a simple explanation
to your site's visitors.
1:42
And log the details of those issues.
1:45
You can also alert the responsible party.
1:48
So that any issue can be
addressed in a timely manner.
1:50
This is especially true when your code
interacts with an external system,
1:54
like a database.
1:59
So let's get started.
2:00
We'll open connection.php
from the includes folder.
2:01
Let's remove this last message here and
start retrieving our data.
2:06
To catch errors, we'll surround our
code in another try catch block.
2:11
It's a good idea to have a separate try
catch block for each point of interaction.
2:15
This next point of interaction is to
run the query that retrieves the items.
2:21
The database object we have created
contains a method for doing this.
2:26
The query method.
2:32
The query method has one argument,
the query statement you want to run.
2:36
You've passed that query
into the method as a string.
2:40
Remember, that you surround strings in
a set of quotation marks like this.
2:44
Then, you specify
the query you want to run.
2:50
We'll type here a simple
query that returns the title.
2:53
And the category Of all
the media in the database.
2:58
This method call returns
the results of the query.
3:06
We can put that return value
into a variable named results.
3:10
Then, we can use that variable to access
the data that the database returns.
3:18
We'll look at this return value in detail
shortly, it's a little more complicated
3:23
than you might expect, but
let's finish this try catch block first.
3:27
Let's echo out a temporary
message after our query..
3:30
If this query fails,
it will throw an exception and
3:39
we'll need to be able to handle
that exception in the catch block.
3:42
Let's just echo out the message here and
end the program for
3:52
now like we did before.
3:55
Here's a quick recap of
the code we just wrote.
4:04
This block here connects to the database.
4:07
If it's successful,
4:09
we'll have an object named db that
can interact with the database.
4:10
If it fails, because the database
is down or a password is wrong or
4:14
something, a message will be displayed and
the program will end.
4:17
Continuing on, this next block of code
tries to run a query against the database.
4:21
If it's successful,
4:26
we'll have the results of the query
containing an object here named results.
4:27
Let's go preview this in the browser just
to make sure things are looking good.
4:32
We type incconnection.php.
4:37
The query ran successfully.
4:41
So we're displaying the confirmation
from the try block.
4:43
Let's change the query
to something invalid.
4:46
Just to make sure that the catch
block is working properly.
4:49
Because this query is invalid,
4:58
we're getting this error message
just like we would expect.
5:00
I'd say that everything looks good.
5:03
Let's now change that query
back to the way it should be.
5:05
We now have code in place that connects to
the database and runs a query against it.
5:09
We have the results of a query
in a variable named results.
5:13
We'll take a look at what that
variable looks like next.
5:17