Reading Project Data6:04 with Alena Holligan
Let's take a look at the data you'll be working with throughout this course. Once we understand the structure of the database and connection between the tables, we'll start writing our first SQL statement.
PDO::query executes a SQL statement in a single function call, returning the result set (if any) returned by the statement as a PDOStatement object.
For a query that you need to issue multiple times, you will realize better performance if you prepare a PDOStatement object using PDO::prepare() and issue the statement with multiple calls to PDOStatement::execute().
Import for MySQL
[MUSIC] 0:00 Our connection is all set up, and our application is ready for data. 0:04 Before we can start connecting the website with the data, 0:08 we need to understand what data we'll be using. 0:11 So let's take a look at the database. 0:14 The database for this project contains two tables. 0:17 The first is a Projects table with project_id as the unique key, 0:20 along with title and category fields. 0:25 This second table is a Tasks table. 0:28 It has a task_id as the unique key, along with title, date, and time fields. 0:30 The Tasks table also contains a project_id field, 0:37 which links each task to a specific project. 0:41 I've also added a map of the database in the notes associated with this video. 0:45 So you can use that as a reference for the tables and relationships available. 0:49 Now that we understand what data we have, 0:54 let's start connecting it to our application. 0:56 As we've talked about in previous courses, it's a good idea to separate concerns. 0:59 This means that we'll keep the code that pulls information from the database 1:04 in one place and the code that displays that data in another place. 1:08 We'll create functions that pull the specific data we want from the database. 1:14 Then we'll use those functions throughout our application. 1:19 That way, if our data source changes, like connecting to an API 1:23 instead of a database, all we need to update are the functions, and 1:27 the application will continue to work as before. 1:31 Let's start by creating a function to read the project data that's already there. 1:35 >> Let's preview the project in the browser to see what we're starting with. 1:40 We see our home page giving us a choice of actions to perform and 1:44 the navigation at the top. 1:47 Choose Projects from the navigation. 1:49 We see a link to add a project, but our project list is empty. 1:52 Let's create the function we need to pull our projects from the database. 1:57 Let's remove the var_dump and close the connection file. 2:01 From the includes directory, open functions.php. 2:05 And we're ready to add our first function, 2:09 function get_project_list. 2:15 We'll start by including your connection file, include 'connection.php'. 2:19 Next, we're ready to create our SQL statement. 2:27 Since this function is not accepting any parameters, and 2:31 we're using a simple query, we can use the query method on our pto variable db. 2:35 db->query, and the following select, 2:41 SELECT project_id, title, 2:48 category FROM projects. 2:53 The query method executes the SQL statement and 2:59 returns a result set all in one step. 3:02 If the query is successful, 3:05 the result set enables us to iterate over the returned results. 3:07 So we can return the results of this query method directly. 3:11 If the query is not successful, then we want to display an error and return false. 3:17 We'll use a try catch block to catch any issues with our SQL statement. 3:23 Try our statement, and catch. 3:28 Exception $e. 3:37 Then we'll echo, Error, 3:42 getMessage, And then return false. 3:50 Now let's open our project list file and utilize the function. 4:03 Scroll down to the unordered list element. 4:07 This is where we'll loop through our results. 4:10 Let's open and close our php tags. 4:12 Then we can add the foreach loop. 4:20 Get_project_list. 4:25 As $item. 4:30 Echo our list item. 4:34 $item['title']. 4:41 And close our list item. 4:45 Now let's refresh the browser and see how our page looks. 4:49 Great, we see the three test projects I included to start. 4:54 Before we move on, let's see what happens if I have an error in my connection, 4:58 like trying to connect to a table that's not there. 5:02 We see the error message we've told our script to display, 5:08 which tells us that there's no such table. 5:11 But we also receive a warning because of an invalid argument supplied for 5:14 the foreach loop. 5:19 The foreach loop is expecting an array, but we've returned the boolean false. 5:20 Instead of allowing our function to return a false value, 5:26 let's return an empty array. 5:29 Now, let's refresh the browser again. 5:36 By returning an empty array after our error, we assure that the method call 5:39 is always returning an array, and our foreach is now valid. 5:44 Because the array is empty, 5:49 the statements inside the foreach loop will never be executed. 5:51 Let's fix our SQL error, and refresh the browser. 5:55 Great job. 6:02
You need to sign up for Treehouse in order to download course files.Sign up