Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
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.
Course
SQL Basics: Searching Tables with 'WHERE'
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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[0]
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[1]) 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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up