Filtering by Project6:36 with Alena Holligan
Too much data can be overwhelming and not very useful. We'll start filtering our data by project, so only a portion of that data is shown at a time.
Grouping has started to make our data more manageable. 0:00 But we're still showing all the data all the time. 0:03 That can get overwhelming quickly if we're using this application everyday. 0:07 So the next thing I want to do is filter our data so 0:11 only a portion of that data is shown at a time. 0:14 We'll start filtering by project. 0:18 So the first thing we need to do is give the user the option 0:20 to select a project for the report. 0:24 Let's create a new form with a drop-down. 0:26 Back in reports.php, below the header, we'll start with a new form. 0:29 Once again, I've added some styling for you. 0:36 So set the class equal to 0:39 form-container space form-report. 0:43 Set the action equal to report.php and 0:50 set the method equal to get. 0:55 This will allow us to share the specific report with others. 1:00 Let's add a label for our filter. 1:04 Filter:. 1:14 Now we're ready for the select field with id and name equal to filter. 1:18 We add a blank option that tells the user to select one. 1:36 Then we can add our projects. 1:48 We'll use PHP to pull the project information from the database with another 1:51 foreach loop. 1:55 Foreach get_project_list 2:02 as item. 2:10 We want to know both the item and the value we wish to filter so our option 2:16 value will include both these pieces of information separated by a colon. 2:20 Each of the items in this foreach loop is a project. 2:27 So we'll start with echo option value equals project. 2:30 Then add a colon, followed by the project ID. 2:37 Item, project ID. 2:42 For the display, we'll use the project title, echo item title. 2:50 Then we can close the option tag. 2:58 Finally, we need the submit button for this form. 3:03 Once again, add the class equals button. 3:10 type equals submit. 3:16 And the value we'll use is Run. 3:19 Let's preview this page in the browser. 3:24 We select Project 1 and Run. 3:30 We can see that the data added to our query string but 3:33 it hasn't changed the report. 3:36 Let's go back to work spaces and do something with that new data. 3:38 At the top of the report's page, 3:42 just below the variable, let's add a conditional to check. 3:44 if not empty GET filter. 3:48 Then we'll set the filter variable equal to this form value. 4:01 INPUT_GET, filter, 4:12 FILTER_SANITIZE_STRING. 4:17 We're also going to use the explode function to make this variable and 4:24 an array of values instead of a string, We'll use colon for the delimiter. 4:28 Now we need to update our function to accept a more detailed filter. 4:40 In functions.php, the get_task_list function is going to need a where clause. 4:45 Let's start with an empty string. 4:53 Then we can add it to our SQL statement. 4:58 We only need a where clause if the filter parameter is an array. 5:04 if is_array filter. 5:11 Then we use the first element to determine the field. 5:19 if filter 5:23 equals project. 5:27 We're going to set our $where = WHERE 5:33 projects.project_id equals placeholder. 5:37 Then we use the second element to determine the value we're going to bind. 5:45 So after our prepare. 5:50 We add the conditional again. 5:55 if is_array filter, results. 5:57 BindValue(1, $filter) and 6:08 then use PDO::PARAM_INT. 6:15 Let's preview this in the browser again. 6:24 Our query string stays the same. 6:27 And this time, we see that our filter is now working to select only the tasks for 6:29 a particular project. 6:33
You need to sign up for Treehouse in order to download course files.Sign up