Preview
Reading Project Data
6:04Let'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
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
Database Map
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
[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