Querying the Database

[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