[ Silence ]
>> All right.
Welcome back this is Computer Science S75.
I'm Chris Gerber.
And tonight I'll be covering Section 5.
We're going to talk about PDO, some more sql commands,
talk to you about join, index and commit and well get
into a little bit of more of the detail around project one.
Hopefully that everyone is doing well with project zero,
you're moving along, you're ready
to turn things in Wednesday.
We'll give you the instructions Tuesday night how
to actually proceed of that.
Well, let's get started.
To kick things off, I'd actually like to do a little refresher
about how we have been working.
So, let me bring up bit of code.
All right, so what we're going to look
at first is just a little bit of how we have been working
with databases in php.
So you'll see, this is the model that I'm been working
on as I begin to work on my implementation of project one.
I've got a few definitions upfront just
to make things easy.
Ans so, I have a single plate were I can configure things.
I've define my host, my user, my password and my database all
with the defined commands.
So I've basically set up, it's not quite available--
a variable, it's actually a constant,
so I can use this later in my code.
So, if down the road I actually changed the account name
or the password, I'm just changing in one spot.
So, let's move a little further down the code.
Yeah. So, this is basically the approach we were taking before.
We've got-- We could skip the first few lines but we set
up a connection, we used Mysql connect.
I had host name, user name and password.
We would then select the database with Mysql select DB.
And then, we were using things like, S print F to take a query,
fill in the blanks automatically and build up the statements
so that we could then do, for example in that Mysql query
to actually get back some data.
So, this is where we were just a few days ago.
This is all basically seemed familiar so far?
All right, so looking at this rough idea, what can tell me--
who can tell me what's potentially good or bad
about what I've done so far here?
So, anything that jumps out that seems promising, disappointing.
Sure.
>> One thing that's good is that you've encrypted your passwords
with the SHA1 in the database.
And yeah.
>> Yup. So, the first thing pointed out was
that we actually encrypted the password,
we're using a password hash, were using SHA1.
So, that was something that had come up in lecture
and certainly that's one of the things I want incorporate,
so, that's a great one.
What else did people noticed from my code so far?
[ Pause ]
All right.
So, one thing I might throw out there,
I've got this function log in user and I'm connecting
to the database and selecting the database,
and that's probably something I'm going to do a lot.
So, that's something where I might want to consider,
does it make sense to put it in this individual functions
and repeat that code over and over?
Or do maybe want to factor that out
to some higher point in the code?
So, that's something to consider.
Anything else?
I'll point out that we also use the Mysql escapes string
on the email because I didn't really know
where that was coming from and I just want
to make sure its safe when it's coming in.
All right, and then one of the other things is something
that David just started talked about tonight.
When we start looking at this code,
I've got all this Mysql specific commands.
So, What's the problem with this Mysql specific commands?
Yep
>> Well, we did mention that if you might read your database
from another system, like get another new database
or something, it would be beneficial to use something
that for some humbled multiple databases,
so you just might as well.
>> Right, so the answer was and it's great answer.
If in the future I want to use something other than Mysql,
I've pretty much locked my self in.
So, that's why we're going to start talking about PDO tonight.
The other thing I'd like you to keep in mind as we look
at this is that S print F command
where I've basically build this query once.
I take the parameters and I build a unique string
and I'm going to pass that one string into the database.
So, let's switch over to the slides.
So, actually I have one other thing I want to talk
about related to this.
We've talked about indexes.
You know, one of the key reason we use indexes are
to improve performance at the cost
of using some additional storage.
So, just as a refresher, we've got the create index command,
you can specify that they're unique, specify the table
and the columns that are going to be used.
When we look at our code, we've talked about this
in the example before, we've got the email and the password,
and were going to be querying this over and over.
But really if you start looking at the statement I build
up here, it's probably more likely
that ID is actually the field
that I've set up as a primary key.
The fact that we're going to continually query against email
and password, it actually implies to me that I might want
to create an index that uses both those fields
so that every time I'm authenticating someone
against the database,
that performance is going to be improved.
So, something to consider
as you're working through your project.
All right, so I want to jump over and talk about PDO.
We want to start leaving behind this idea
of this Mysql statement,
it's not that they're bad but we can do better.
So PDO is PHP data objects,
it provides a data access obstruction layer
but it does not provide a database obstruction layer.
So basically the difference there is, we don't have
to learn a new set of PHP commands
to talk to our databases.
No matter what we change on the back end, if it's Mysql,
oracle whatever, were still using the same PHP commands.
But whatever we pass in through the PHP commands,
a select statement, an insert, an update, a delete,
those commands are not going to get translated.
So, if your using a specific set of sql commands
that are specific to your database, those do not change.
So, if you start using custom functionality, for example,
if I was going to do a create table through PHP and I'm going
to use Mysql, we talked tonight about using NODB,
it could be one of your database options.
Oracle is not going to know anything about that.
So, if I remap the commands and use oracle as the back end,
the PHP part will stay the same, those commands will be the same.
But the actual create table command will have to be edited
because this function or the PDO doesn't know how
to automatically make those translations.
One of the other nice things about PDO, we mentioned some
of the different drivers that are available, some of platforms
out there are Informix, MS SQL, MySQL, Oracle, SQLite.
So certainly plenty of options which is really nice as well.
All right.
So, let's actually see what some of these starts to look like.
Get out of my code.
[ Pause ]
So, I created a very simple example,
basic idea of a select statement using PDO.
So, we can start.
I have to sort of spelled it out on one big command
to get started, but I'm creating a database handle,
dollar sign DVH, so, starting here, equals new PDO.
So, I'm actually creating an object and it's a PDO object.
And I'm passing in some parameters.
The first parameter is the DSN, which I don't of the top
of my head remember what the acronym is for.
But basically what it tells it is,
the type of database you're connecting to,
the host that it's on, and which database
in that host you're going to use.
So, I've specified Mysql for the database type,
I've specified local host as my host,
and I've specified the database name of jharvard_section5
which should feel familiar from all the work we've seen in PHP,
Mysql so forth-- PHP in my app.
Then the next two parameters are the user and the password.
And we've certainly seen jharvard as the user
and crimson is the password plenty of times this semester.
The next thing I'm going to do is just a simple query,
I take the database handle and I tell I'm going to do a query.
And I've got a very simple query statement here,
select star from the students.
We all know what that does straightforward.
I'm going to take the results and put it
into a variable called $students.
As you might guess, this is going to be an array.
So I can, as we go to the bottom of the screen here,
use a four each to iterate across that, taking each element
out of students and calling it a student.
And then, I can print the various fields.
So, one thing you'll see here is I suddenly have ID first
and last available, and what's happening is PDO is actually
looking at the column names in the table
and making those the keys for my associative array
so that I can pull the values out.
So, student is the row, id is the column,
it's mapped at into an associative ray of element
and we'll automatically pull out the value.
And then when we're done, just for good measure of set,
the database handled to null
which essentially close is the connection.
So, we can actually see what this does.
[ Pause ]
Before I actually kick off this command, one thing I want
to point out is for the bulk of this course,
we've actually been running PHP through the web server,
but PHP is just a standard scripting language.
When you execute it, you can specify that the output is going
out through, for example, through apache
out through the web server,
but you can actually run this commands from the command line
as well and just see the output.
So I've actually, I'm going to do that right here.
And you can see that it's going into my database,
previously, set up a small table.
I've got a column ID, it's the primary key.
It's just in 1, 2, 3, auto in command data.
I've got a first name and a last name.
And it's pulled out to those records
and printed in the screen for me.
So, just as a very quick overview of PDO,
does that make basically make sense so far?
OK, great.
[ Pause ]
So, as we start looking into PDO,
you'll see a few standard things, we've got the dsn
which we mentioned and the dbh, database handle that we mention.
There are basically five statements that are sort
of my wheelhouse when I'm using PDO,
query executes the statement and return the result set,
that's what we just saw.
Exec executes the statement.
But instead of returning the results,
it just tells you the number of rows that were impacted.
So, if you were doing an update operation
and you expected three rows to be updated, you could determine
if that actually happened if for example zero were updated,
you could start to get a sense
as to was the command successful based
on what you expected the account to be.
Prepare, bindValue and execute, get a little more interesting.
So what prepared us is it let's us pre-prepare a statement
for execution on the database.
So, that actually gives us several advantages.
First of all, it's letting the database know up front.
We're going to do this work and let it do a bulk of the work
up front to get ready.
It knows what tables it's going to need.
It knows basically the approach that it's going
to take query the data.
And then, we can specify parameters
and we can use those parameters to then tweak the query,
so we might be selecting with the parameter of ID equals fill
in the blank, and then I can say, "Run this query with a 1.
Run this query with a 2.
Run this query with a 3."
And the database doesn't have to go through the full process
of building the instructions each time,
it just has to rerun the same query with the new value.
To do that, we're going to use the bind value command
which takes one of the parameters and inserts the value
that we want to use into it.
And then, once we've prepared it and bound it,
we actually use execute as to oppose to exec, execute spelled
out to actually run the statement.
So, why do we do this?
Basically, there two really good reasons, performance first off,
the preparations perform once, so the database can do that work
and it doesn't have to repeat it every time we're going
to execute the command.
And then, you know, parameters can have new values bound
at ease executions well which is great.
The other advantage is security.
As David mentioned earlier tonight,
by using this parameters, we actually get the work
of escaping the parameters done for us up front.
So, we don't have to remember to do Mysql, real, prepare,
whatever the whole statement was.
You don't have to remember that even.
You just have to know that as long
as you're using these parameters it's going
to take care that for you.
[ Pause ]
So, let's see what that looks like.
[ Pause ]
So, I'd shown you my table of students
with my self and David and Ellen.
I've also got a table of grades that I had pre-created.
And what I want to do is fill in a number of values.
So, I've created this array that has the data that I want
to load into the database.
The first value is the ID number of the student,
the second value is the project number they were working on,
and the third value is the grade that they got on the project.
And I just want to bulk load this data into the database.
You can see I've set up my connection to the database
as the last line here.
Well, let's see how we might actually go
through this process using parameters.
All right, just to make it clearer for my self,
I started off by deleting from grades.
So, anything that wasn't there, I'm getting rid off.
Next, I can prepare this statement.
We want to insert into grades, grades was the table,
the columns are going to be student, project and grade,
and we want to fill in values.
And you can see what I've done here is I've got :student,
:project, and :grade.
So basically, what I've done is name the parameters.
These are the blanks that I want to fill in
and these are the names I want to use to fill them in with.
Now, you could actually use question mark for each one
and then just remember the position of each question mark.
But I find using the full name as actually little more clear.
Then, I've created a loop here to insert all this grades.
At each point, I'm going to bind a value
to the appropriate parameter and then I can execute the query.
So the first time through loop, I take those first elements
of the array and I bind them to student, project, and grade
and then I can execute the command and loop
around to the next item.
The other thing that's interesting here is this last
set of parameters.
Now, what does that look like to folks?
Sure.
>> Integer and strings.
>> Exactly.
So, what I can do is I can actually--
sorry, it was integers and strings.
What I can actually do is tell the database
in advance what types of fields this are as well
which will help it with the process of making sure
that the data that's being bound in is appropriate
for the fields, so that I don't try to take a string and cram it
into an integer or it will know that it actually has
to do formatting appropriately to make that happen.
Those parameter types are actually optional,
but I certainly recommend it as a-- if nothing else,
good clear it to yourself
as to what you're expecting to be happening.
And at the very end of this example, I just do another query
of the database, the same code as before just
so we can display the results.
I'm using the grades table instead of the students table
but the concept is the same.
So, let's see how this actually works.
[ Pause ]
So you see it's gone through and it's entered the data
from that array directly into the table of query to back out,
relatively straightforward.
So, you can select a series of columns from a table name,
there's a number of options here and I'll come back to that,
joined to a second table name based on some conditions.
So we've got left, right, outer, inner, what does all this mean?
Basically, we have inner joins where we have left
or right outer joins, there's also full outer joins
which we'll hint at but we won't go into a lot of details there.
So, let's see one of this.
So, starting with inner join, I've got two tables
and this are the tables that we've been talking about.
We've got students, Chris, David, Alain, we've got grades
with the student number of project and grade.
I moved the ID column in the first table to the right just
so you can see that ID and student go together
and you can sort of see how things might line up there.
With an inner join, what we're going
to see is a result like this.
It's basically going to look for every place on the left
where it can find an ID number that has a matching number
on the right and display those records.
So for an ID of one, it goes over.
So, see a student is in the first and second rows
and it brings that combination of data together, 1, 1, 1, 1.
It goes on to number two, it finds the third and fourth row
in the second table and brings those rows together.
For Alain, his ID 3, there are no grade,
so it doesn't bring anything back there.
Likewise in the grades column, there's a student 4
who we haven't define anywhere
and it doesn't bring that back either.
The inner being, it's all the places
where only there is a direct match.
Code was, this is what it would look like.
The table that I actually brought back was select star
from students, join grades and students.id=grades.student.
And we talked about that earlier
where when you're using database columns
but you have multiple tables to work with,
as long as the fields are unique you don't necessarily have
to specify the table name but it's good practice
to always specify table name dot column name just to be explicit
about where those columns are actually coming from.
So, we can see this in code.
[ Pause ]
I'll skip that the part of the codes that's repetitive.
And you can see I've plugged in exactly the sql statement
that we just saw at the very top, select*, FROM students,
JOIN grades, ON students.id=grades.student.
And we can just go ahead and run.
This is relatively straightforward here
[ Pause ]
And you'll see that we actually get the same data set,
the one difference between the ID column is back
in its first position rather than in the middle of the table.
So, what about this case
where the inner join isn't giving us all the data
that we need?
Now, we can start looking at left and right outer joins.
[ Pause ]
So the left join, we're starting with the same two tables
of left, than physically in the same space.
The students is the left table, grades is the right table.
Now, what do you folks think the difference is here
versus what we talked about with inner joins?
OK. So, the difference is what we're saying
when we specify a left join is that every record
in the left table, whether it has a corresponding record
in the right table, it needs to be displayed.
So in the last example, Alain didn't get listed
because he didn't have any grades.
But now, we're saying that absolutely has to happen.
So, that data set will come back looking like this.
It will suddenly have this null fields
because there is no record to go with it.
Now, who can think of a situation
where this might be useful?
Sure
>> In this very ideas right here where some person comes in
and doesn't have all the information that needs
to be displayed to see all the students.
>> Exactly.
So, as Wester [assumed spelling] said,
in the case where we still need to see all the students,
so if I have a class list and I need to see
who hasn't been turning in their homework, I need to know
who all my students are whether
or not they've actually earned any grades.
So, let's go head and jump over to the sql statement.
Nearly the same as the last example,
the one subtle difference is the addition
of the word LEFT right before the word JOIN
to explicitly say that, "Yes,
we're doing this left outer join."
As a side note, you could actually include the word outer
but it's optional and I didn't feel
like the extra type of thing.
So, coming back to real code.
[ Pause ]
The same idea, we've just added the left to the sql statement.
And as I prematurely ran it,
you'll see that the actual output is just
as we had predicted it would be.
I think I'm actually going to for times sake, skip PDO5.
As you might guess, PDO5 is the right join
which is essentially the same as the left join except your saying
that the right table is the dominant table.
And no matter what you want to include the records
from that table regardless of whether there's data
that corresponds in the left table.
So, I can actually run that just to demonstrate the output
that we would expect there.
Where we get this mystery student 4,
we see their grades come through
but we don't actually see any name information
because they're not in our students table.
[ Pause ]
The other type of join that's
out there is this full outer join.
And basically the idea is, we're saying
that both tables are equally important
and we need every record from both tables no matter what.
So, quite simply, we want this output which is the combination
of both, we have the student that doesn't have any grades,
we've got the grades that don't seem to have any student
and we need all the data to come back.
The reason I put a little asterisk
on this one full outer join is it's not actually supported
by Mysql.
So, although it is an available option in certain database,
is it's not one that we'll be working with.
So, this is an example where if we're using PDO and we've,
you know, we've got this standardize PHP statements now,
we might be using a database at one point
that supports a function, but then we switch
to another database and that functionality is no
longer available.
So, something to be in the back of your mind
when you do change databases, if you do change.
All right.
The next new sql feature that I wanted to talk about is commit.
So basically the idea of commit or transactions in general,
is that we want to specify that either all the commands need
to happen or none of them should happen,
these whole atomic operations.
Specifically in Mysql, we need to specify InnoDB
as our database type to get this functionality.
But then, we can start doing commands like start transaction
and then either commit or roll back.
In everything that happens between those two points,
it will happen as one complete operation.
So, this is very rough code.
This is not tested.
This is just to give you an idea
of what something might look like.
So, we've got a foreach where we're looking at balances
for user ID equals one, not my cleanest code.
I wouldn't recommend putting this in production anywhere.
But I'm taking the balance by getting the balance
out of the row and I'm going to add a hundred to it.
And then, I want to execute this, update the user,
set the balance to this new value.
Now, what's potentially bad about this?
Sure.
>> Same user goes and runs this twice and try
and get their balance up to 200.
>> Right, so the problem is the case where the user tries
to run this twice in parallel.
And then, you know, what's the right value?
If I'm suppose to add a 100 in both cases,
which update gets put in when?
Do they only get $100?
Do they end up with their full $200?
We don't really know because it's--
there's an opportunity between each statement for execution
to pause and move on to another process.
So, what we wind up doing is using these commands.
So because this is so well-known,
this idea of transactions,
PDO actually supports it native ways.
So, it's got a begin transaction command that you can send
to your database handle and a commit and also rollback
as my guess commands that you can put
in directly into your code.
So, let's see that with some real code
[ Pause ]
All right.
So one thing I-- just as a quick note, as I've packaged
up this little function at the top here,
just so I can run it multiple times, what it's going
to do is show me the results or the current state
of my grades table so that we can see how things are going
along as make changes.
So, I'll start off at the beginning.
Before the update I'll just put the grades,
so we have a base line.
Then, I can begin a transaction and I can update the grades
and I can say, for student 1, I'm going to bump up my grades
to A minus, you know, I only had B and B+ before but, you know,
I got the curves so I'm excited now I got my A minuses.
And I'm going to commit those changes to the database.
Then using my powers as a teaching fellow,
I'm going to say that, you know,
"I don't think David necessarily did
as well as he thought he did.
And I'm going to bring David Malon's [assumed spelling]
grades down to an A minus from the As that he had previously."
But I'm going to wrap that in a transaction as well.
So as soon as I update the values,
I'm going to query the database and see what things look like.
But then, I'm going to have regrets.
I'm going to actually roll back that transaction and then look
at the database again.
So, we've got three points.
Before I do anything, after I've manipulated my grades
but committed them, and then updated David's grades but not
yet determined whether to commit a rollback
in the very end of the application.
So, let's see what happens there.
[ Pause ]
So, we've got a whole full screen of stuff here.
But you can see before we started, the grades were, B, B+,
A, A, C as we had see before.
I had my two updates, the one that I committed for my self
that updated the first two lines to be A-,
and then the uncommitted or rolled back changes
that updated lines three and four and left those at A-.
But then I had my regrets, I rolled back
and David was once again returned to his As.
So, all this code is actually up on bit bucket for you,
I'll make sure I get the URL out as part of getting it posted
with our standard location in the section code.
Yeah. So, any basic questions
about the basic ideas of PDO so far?
All right, great let's move on to the walkthrough.
[ Pause ]
So, our new project is CS 75 finance,
I'm going to talked a little bit about the architecture,
a little bit what you might think about for database design,
show you a little bit about how we actually do this Yahoo
stock quotes.
And then, talk about other considerations
that you might want to think about as you work on a project.
Just as a-- yeah, Just as a quick over view
of the Yahoo stock quotes, you can actually go out to Yahoo
with this URLs going to quotes.csv and do query.
So in this case, I'm searching for Goggle and Yahoo quotes
with this little magical snl1.
And I've broken that out, there's actually if you go
to this gummy-stuff.org site, they've got must be
about 30 different parameters that you can pass in here.
S specifically says show the symbol,
N says specifically show me the name,
and L1 says show me the last trade for the stock.
So, this query would actually bring back a CSV file that has
that information for both Goggle and Yahoo.
But let's with that background get on to the live demo.
All right
[ Pause ]
So, let's start at the beginning.
I'm sticking with this whole MVC concept in sort of expanding
on what David had done.
So I've got a dot HT access file
that looks a little bit different
than the one we had used before.
But some basic ideas really haven't changed.
I'm still turning the rewrite engine on.
I'm specifically using a rewrite base of just slash now
because I'm actually going to work
in our project one directory as oppose to one
of the MVC slash 7 slash, et cetera directories
that David's been working with.
The third line is just going to say
if the URI is index.php, ignore it.
Otherwise, I do this very right.
For simplicity tonight, all you really need
to know is I'm capturing two parameters that end
up being called one and three, and its getting turned
into index.php?page equals the first parameter
and param equals the second parameter.
So, if I have a URL that's project 1/home, it's going to go
to index.php?page=home and param equals nothing.
But if I want to a page that's project1/"/GOOG, I can say I go
to the index.php?page="param=GOOG
and then presumably actually get a stock quote
for Goggle base on the URL.
From dot HT access, we jump over to index.php,
which is just taking this parameters
and deciding what controller it's load.
So, if the page is set, it's going to use that as the name
of my controller and it's actually going to load
that file, ultimately, through this require of statement.
If it's not set, it's going to default to home as the page.
So no matter what, people end
up at my home page unless they know where they're going.
Leave it to as an exercise to a sort through the rest of that,
but it's basically just the dispatcher
for MVC at this point.
The other magical piece is actually code
that I took pretty much straight from David.
[ Pause ]
And that's the surrender function that he had,
which basically is for rendering my views.
So, a controller can say render
with this template and this data.
The data is automatically expanded
from an associative array into a series
of actual variables using the extract command.
And although my name is on the top of this,
this really is essentially David's work, almost unchanged.
[ Pause ]
So, given all of that, I've come to this MVC model again.
This is my controller for home.
This is the base page that I go to.
It includes that helper functions, so it knows how
to actually render views after work.
And basically, all its going to do is look
at my current session, see if a user ID has been set
and then decide which view does the user get to see.
Do they get to see the home page or do they actually have
to go to a log in page?
And you can see where we're actually getting to the point
where using this model view control or architecture,
potentially, things are very straightforward to look at.
I've got all about five functioning lines of code
that handles the entire controller for this page.
All right.
And let's see what some of those view components look
like so we can put the pieces together.
[ Pause ]
So, one of the ones that we just saw referred
to as this login.php, and this is
where things are actually getting a little
more interesting.
Who knows?
We hadn't really seen any html yet, but now that we're
in the view, it's the right time
to start seeing the html code appear.
So, I'm going to render a header and look at that shortly.
Then, I'm going to start putting things together.
So, I want people to actually log in.
So, it sounds like I need a form.
I've just created two fields for now, an email address
and a password, but that sort of fits with the spec
that were working with for this project.
And then, I've started adding a little more.
So, we haven't really started talking about JavaScript yet
but I wanted to have some place holders in the example as we get
to that in the coming days here.
So, I've done two little things, this validate form
which just a short explanation.
Basically, what I'd like to do is before the user even takes a
trip out to the web server to do any work,
I want to start doing some work on the client side to make sure
that things are OK before you even go through the effort.
So, this very, very short example of validate form,
all it's going to do is look at the fields
that have been filled in, it's only going
to look at the email field.
And what I want to do is just check
that at least 6 characters have been entered,
the idea of being-- if you were able to get the address, x@x.to,
that was the shortest email address I could think
of that was possibly going to be valid.
So, if you haven't put in at least 6 characters,
I didn't want to worry about it.
The explanation of this will start coming Wednesday as we get
in to JavaScript jQuery.
This is just here for as you move forward at the project.
The other little tweak that you'll see
in here is the statement here,
input name equals email dot focus.
Basically when the page first loads,
the cursor doesn't have any focus, this is ultimately going
to say move that cursor into the email fields
so the person can start typing.
Again, another one of those details, you don't have to worry
about tonight but a little preview
for what you might start seeing on Wednesday.
And then lastly, will include the footer.
So basically, all this template has done is created a form
on the screen.
And I can actually show you what that looks like just
so you have a little sample.
This is actually that page.
We saw the email address, you see it has actually right now.
We saw the password.
We saw the log in button as part of the form.
There's also this little link home page at the top
which we haven't seen yet, that's actually going to come
out of the standard header.
So, this should look relatively straightforward except
for the one line that says script.
And again, as we start getting into JavaScript on Wednesday,
that line will make sense.
But otherwise you'll see, I'm opening my html in my head,
plug in the title using the same approach
that David had use the last time he did MVC.
And I've created one link at the top of the body there that says
if you click on home page it just going to go to slash
on the site which translates to http://project1/.
All this code is also up on my bit bucket account.
So, when that link is published on the home page for the class,
you'll also have access to all of this to review as well.
And the footer is even less convoluted.
All I'm doing is closing my basic text.
[ Pause ]
The other option, if the user was already logged
in to the site, I want to show them a little information
on the site.
So, I've just got a few sample links just
to get things started here.
You might think about how you might build
out this project later to include more details here.
I've specifically card quoted
that you can request a quote for Goggle.
Obviously for your project, do you want some more flexibility
than just one stock symbol?
I've provided a link so I can actually view my portfolio.
And I've provided the opportunity to log out.
You'll want to start thinking about things
like how will they buy a stock, how will they sell a stock,
do you need separate pages for those.
If some ones adequate page already, is that the time to buy
or do you want a separate purchase page?
If they're already looking at their portfolio,
is that the time to sell stocks
or should there be a separate page for selling?
Some design considerations you can start thinking about there.
[ Pause ]
All right.
So, let's start looking at something real.
Let's get a quick view of what the start
of the portfolio might look like.
[ Pause ]
All right.
So the portfolio control, if I click at my portfolio link,
what is the controller going to do?
The controller really just wants to talk to the model
and pass any information out to the view controller.
So basically, the sense of being relatively straightforward,
if the users logged in with session users ID as set, then.
I'm going to get the user ID from the session
so you can think about how we've been the past use the session
and stored a variable in there,
somewhere I must be storing this value.
Then, I'm going to get the users holdings.
And this is just one call out to the model in this approach.
Your holding is just get users shares for your user ID,
and then we use render to throw it out to the view.
So, the controller ends up just being the glue in this case.
All it knows is I want to talk to the model to get the,
and as soon as I have it, I don't want to think anything
about html, I just want to get it off to view to deal with.
So, this ends up being the entire controller
relatively straightforward.
One thing I'll point out here, you know,
we've already seen one other place in my code were I check
if the session user ID is set,
perhaps I'm not doing the right thing here.
Do I really want to check that on every single page?
Is that a helper function
that should be caught at a higher level?
Things like that that you can consider.
I don't know that I want to--
have to remember to in every file cut
and paste the same in session user ID.
And if not, render log in.
All right, so to get to the real meat of all this,
we finally make it down to the model again.
This is the same model
that we've have looked just shortly ago
when we we're looking at an example of how we used
to do things, this Mysql statements.
I've left this in there.
This is basically one of the approaches that we've used
in class for authenticating a user against your database
where you take the idea of selecting by email and password.
If you get a row, the users have the right password
and has a valid account and you can use that to log them in.
But at the moment, we're talking about portfolio.
So, let's actually go forward a little further and get a sense
of what something like this might start to look like.
So the portfolio, a user has some collection of stocks,
they have some cash balance.
I'm going down to a very basic example here and I'm only going
to look at what stocks they have.
So, you'll want to think about how do you expand this
as you start developing the project to include everything
that truly belongs in a user's portfolio.
So, we got our new PDO syntax.
We're connecting to the host, selecting the database,
log in with user name and password, all of basically
in those first two lines of code.
Then, we're going to prepare a statement.
Because the user ID is coming from someone else,
I like the idea of the prepared statement here,
it's going to protect me, it's going to do that wrapper
of making sure that anything that needs
to be escaped is escaped.
So I'm going to buy in that value in
and then I can execute the query.
Once the query executes, I'm going to get back a result set.
For each of the rows that I get, I'm going to do--
instead of doing the 4H approach like last time,
I actually took a different approach.
I'm using the statement and a fetch
to get each row that was returned.
And as I get them, I'm just going to push those results
into an array so I can return it.
So, what is this going to look like?.
We're selecting symbols in shares, so we're basically going
to end up with an array set that is for each row in the array,
I'm going to have the symbol and the value, the shares
and the value in an associated array,
so an array of associative arrays.
And I'm just going to return that to the users.
If the statement didn't execute my database handle,
just goes to null and you'll see a return no data.
So, this is basic idea of how I might go
out to the database using PDO, select a few rows
and return an array back to the controller vaguely make sense?
Are there any questions about it?
All right, no questions yet.
So once we've gotten this, we already saw
that the controller is really just passing along,
so we can jump right over to the view.
[ Pause ]
So, what are we doing with all this data that we've got?
I've decided that I'm actually going to make a small table.
It's going to have two columns.
I'm using that table heading to bold those two--
the headers on the two columns, so it's just symbols and shares.
I'm going to loop over the array
that have been passed back directly
as it come right out of the model.
And for each one, I'm going to html special care
as to clean it up, but basically put it in a table data
within the table rows, pulling things out by the names
because I've got this associative arrays inside there.
And that is roughly it,
relatively straightforward here before I put the footer on.
So, let me show you what that actually looks like just
to give you some context.
I'm going to jump ahead and actually log
in so I can do view portfolio.
So apparently in the database,
this user had two actual holdings,
one was the symbol GOOG for 10 shares, one was the symbol
for Yahoo with a 100 shares.
In fact, that's not the real symbol for Yahoo.
That was a typo when I created the data.
But basically very straightforward, and if we look
at the page source, you'll see that the page--
[ Pause ]
-- I really haven't gotten too complex with my code here.
All right.
So, the one other thing I want
to just show you was what David just started alluding
to at the end of class as well, and that is the quotes.
So, we can look at the controller for quotes.
And this is again very short quote
because all the work is really being done
by the model and the view.
Basically, I'm going to say if a param was passed in,
I'm going to call this get quote data
with the parameter, urlencoded.
And then whatever comes back, I'm passing directly
out to the view for rendering.
So, let's see what's really going on in the model here.
[ Pause ]
All right.
So, this code probably looks very familiar, it's very similar
to what David just showed a few minutes ago.
I'm being passed and assembled.
I'm creating a result array just
to store things in, nothing in their yet.
And I've got this URL to actually connect to Yahoo.
The one special piece of data that I've plugged
in is the symbol that was actually passed in.
I'll get the pointer.
So I've got the symbol here.
I've also specified this which we saw in the slides earlier
that sl1&n, s being the symbol,
l1 being the last trade value and n being the name.
What I want to do then is I can actually open this URL
as if it was a file directly with be an F open command.
Once I've a handle to that file, CSV is one of the things
that PHP knows how to work with, so I can one row
at a time use fgetcsv to pull the CSV data directly
into rows that I can work with.
If the row, row value one in this case is set, then,
I know I've got data, I found the last traded value.
So what I'm going to do is I'm actually going
to create an associative array with the symbol set
to the first parameter which was always the symbol,
the last trade set to the second parameter was the l1 is the last
trade, and the name set to the third parameter which was the n
from the URL that we used.
I set this us specifically so it just pulls down a single symbol
at a time, although the APIs do allow us to pull down multiples,
and that's something that you want to consider as well.
In this case I really--
I was only concern about returning one result.
But when your working later, say for example you're going
to be generating the value of someone's portfolio,
does it make sense for each stock symbol
to take another trip out to the Yahoo server to get
that quotes-- that symbols phrase, or do you want
to bunch them up and get a number of symbols back
at one shot so you might get 5 or 10 current stock raises
from Yahoo in a single transaction rather
than doing multiple transactions?
So things to consider their.
Once I've got the data, I close my handle and I return it.
And that data can then be pass off to the view.
[ Pause ]
So, one little tweak you'll see at the top of this file
that I haven't done in other files, is it possible
to call this page and not have valid data come back?
So, either someone picked a stock symbol that didn't exist
or Yahoo service was unavailable
and I just couldn't get data at the moment.
It's possible that things will go wrong.
So, I've got some notification to the user if no data has come
in to the view that either the symbol wasn't provided
or the quote data wasn't found.
But assuming that I do actually get my data back,
I've done a short table again scrolling off the bottom
in the screen here, and following the same templets
as I did with portfolio.
I'm just taking the three pieces of data and put them
into that table row and closing up the table on the screen
and I can show you what that looks like as well.
[ Pause ]
It's a very straightforward little table, just a symbol name
and last trade and the data's pulled directly from the model,
pass through the controller and displayed on the view here.
So, that was the code I was going to review tonight.
There's one other file in there,
you'll see it's called pwd hash dot php.
If you try to log in and your log in credentials aren't valid,
it will actually show you the password hash
or whatever you just try to log in with.
So in that, until you actually write your register function,
you can find out what the password is
and you can push them with the database,
you can actually keep moving along with your code
and before you finish all your code.
So, a few things that I want to make sure
that I got some design considerations
out there for you.
I've have mention a few as we went.
We talked about how many connections should you make
out to yahoo when you're trying
to calculate the valuable portfolio, so something to think
about there as a multiple connection, single connections.
What happens if the user actually try to sell stocks
from two different browser simultaneously?
What do you want to do about that?
What happens if the user buys additional shares of a stock
that they were already own?
And David hinted strongly at how you might consider that earlier
in the lecture tonight?
As we get into the JavaScript, you want to start thinking
about what validation do you want to do on the browser
and what validation do you want to do on the server.
If the user hasn't entered a password at all,
you can probably save yourself a trip to the server
and validate on the browser.
But if you manage to get pass that check
or the user doesn't run JavaScript,
you probably still need to confirm some things
on the server side as well, possibly the same things
that you thought you were confirming on the browser side.
So, something to think about is we're going to Wednesday there.
And one other thing that isn't obvious, but if you look
at my code-- this page is available right now,
this quote slash GOOG.
But if I logged out of the application,
the page is still actually visible.
So, think about things like this, does it make sense for me
to be able to still look up quotes even if I'm not logged
in to the application or should all the pages?
Or what subset of the pages should be restricted
to just authenticated users?
So, those are the few thoughts I had to inspire you
as you work through this.
Are there any questions on the project so far?
Just as a quick reminder, I'll be sticking
around for office hours for as long as folks need tonight.
And one of the TFs will be online office hours at 8.
But certainly, any questions right now, I'm happy to answer.
All right.
Well I thank every one for their time and thanks
to the folks watching this remotely.
And good luck.
Remember to watch for our posting on how
to submit you're project zero tomorrow
so that we can get those submitted Wednesday.
Thanks. Good night everyone.
[ Applause ]
[ Silence ]