Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed CRUD Operations with PHP!
You have completed CRUD Operations with PHP!
Preview
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