Hello and welcome to Web Applications for Everybody.

We are going to go in this bit of a code walkthrough of the Auto-Grader.

I mean the assignment for profiles, positions and education.

So, there's two real learning objectives for this.

One is we want to do a little bit of exercising JSON,

but we're also going to end up with a many-to-many relationship.

And so, like always,

I've got a whole bunch of things that you're

supposed to do and how you're supposed to do this.

And again it builds on the previous one.

So, I assume you've watched

all the previous ones or you have the previous assignments working.

So, let's go ahead and just run this code and demonstrate it.

http://localhost/webapplicationsforeverybody.

This is not res-position, this is res-education.

I got stuff in the database.

Let's see what I got, profile.

Now let's just get rid of all of them.

That didn't work.

So let's just go here.

Delete from profile with no WHERE clause.

Sometimes it's nice. No WHERE clause.

So now I should have nothing in positions because I could have shown you that

but the cascading foreign key got rid of all that stuff so we still have our users.

And let's check we've got

the one user with the hash password which we're supposed to have.

Now the first thing I'm going to do is we're going to put in some new tables. Here we go.

Let's just borrow this out of the assignment and create

two new tables with some foreign keys. Go okay.

So, now we have the education.

We're going to use this as a lookup table and then

institutions are going to be this lookup table,

and we're going to have a many-to-many relationship.

This is a two-way link where we have profile ID link and

a profile institution linking the institution and so each of

the people are going to have when they choose an education.

And we'll see how that works in a second.

And then I've got to seed the institution table with some interesting data.

So go into the institution table and insert some data.

And so now in our institution table we've got some institutions, okay?

So, now that we've built this, let's log in.

No, that's not right.

umsi, you missed out a U. Okay, I guess that's the right password.

No. umsi, php123.

My browser's keeping those passwords.

Okay, and the new entry.

So, a lot of this looks the same.

I got first thing and

the position is like from before.

The education is a little different.

Okay, so this is the one we're going to do differently.

So in education we're going to plus.

We're going to say 1990 and then we're going to have the school.

But what we're going to do is we're implementing a typeahead.

And that's in the assignment.

To implement a typeahead it tells you how to build the typeahead.

So, if I type Un,

university of, I can choose this.

Now the interesting thing is just the use of JSON and jQuery.

So, if I do a view developer console and this is how those things work.

I don't know if you can see it.

Now let's do that again. Let's empty this out.

Basically what happens is you've told jQuery that as people type,

call server-based code for the prefix.

And so there's a database query that's going into this database table,

here, looking for prefixes Un.

So, if I type Un,

you see that it actually made a query.

And in that query, if you look at the response to that query,

that query is a little table, right?

Of all that is select using the LIKE clause.

Select, using a LIKE clause.

LIKE is kind of a wildcard U% is like the wildcard character.

So, this is going to be Un% like university Un%.

And then net matches those prefixes.

Now when I pick one of these things,

it really is still all in the browser.

It's all jQuery. And this is still just a text field.

There's nothing tricky here.

This is a sort of an intelligent text field.

Let's move this over.

Let's make this wider.

This is an intelligent text field that now has a value of the University of Michigan.

And when it gets submitted,

it just gets submitted as a string.

And you'll see what we do when we submit it, okay?

And so that's the tricky bit is to add this.

And what we want is we want the users to re-use existing ones or make new ones.

So you can even add a new one called

1996 Lansing Community College.

This doesn't match anyone but it's going to end up inadvertent.

It's going to add one and then link it together.

And so if I hit this add button, and take a look,

you'll notice in the institutions,

by me typing a new one,

Lansing Community College has been added here.

And so then if we look at the profile,

I got a profile, this is me.

I've got some positions.

No, I just got one position.

And then I got education entries.

And so if I look at the education entries,

the education entries are

a many-to-many pointing profile ID 10 and the institution ID one,

which was the Cambridge one I think.

You can find out. Cambridge, no the University of Michigan,

and then 10 is the Lansing Community College one.

And so we've got a lot of stuff that we got to make work.

We can of course edit these things and we

can delete a position and then add a different position.

Pizza Hut.

And of course we have some code that shows these things with

little bits of queries that put all these together and print that all out.

And so that's kind of how it works.

So, I'll go ahead and delete it and then log out.

And so that's pretty much how it works.

Up next, we're going to talk a little bit about the code and walk through the code.

Okay, so now we're going to take a look at

the code and I think I want to focus this time on the edit code.

In other ones I focused on the add code.

The idea is not to give you all the code but

to be able to have you work the pieces all together.

So, let's take a look at edit.

This is quite a bit more complex.

It depends heavily on the util code.

I keep moving more and more of the code. It's like between

add and edit into util just because I don't like repeating myself.

And the head code is important as well.

So, we'll take a look at the head code.

And so really I've got jQuery,

I've got some CSS in here for bootstrap and jQuery, and then jQuery-ui.

And jQuery-ui is where this little auto-complete thing comes from.

And I give you this head guy in the handouts so you can just borrow that.

So, again this looks pretty familiar now.

We get some util stuff,

we got the script protecting itself,

we got handling that cancel button.

We're demanding a profile ID get parameter right there.

If there isn't one it just blows up and gets mad.

And then it basically makes sure that we do have a profile.

It's also validity checking this number now

by reading this across to doing select for the profile,

and user ID equals the session.

This is the way to, if you notice, no you don't.

You're supposed to do this where if you look at the profile,

there's also a foreign key into the user table.

And so what I'm looking for is,

I'm not just letting anybody I'm protecting

myself that you can't just put a profile that doesn't belong to you.

That has to also match the user ID, right?

So that's what I'm saying here.

If the profile ID is the one from

the request and the user ID is the one from the session.

And I'm only going to let you retrieve profiles that belong to you.

That's kind of what I'm saying there. Okay.

And so at this point we're just,

that was mostly security and checking and

making sure and being safe etcetera, etcetera, etcetera.

So, then we have our post code and it should look familiar,

the validate profile code,

validate position that's all in util.

The validate profile and it's basically returning a true or an error string.

And if it's a string I do the error and if the positions are wrong I do an error.

My whole goal is to make sure that the profiles are valid. Okay?

And it looks like I did not validate the education.

So, I should do that.

I guess should, let's do this.

Should validate

education. I got to-do in there,

that's actually what coders do because we all kind of even this text area knows that.

Okay, begin to update the data.

Okay, so this is all pretty straightforward.

All we're doing is we're updating the profile which

is this table right here with the new stuff.

And that's pretty much the same as it's been in the last couple of assignments.

And then we're going to take the position entries for

the profile and this is a many-to-one relationship.

Let's go ahead and change this.

Let's get two positions in here. 1980

Save. So now we've got two.

Let's go back here,

browse, we've got two, right.

Two positions for a profile.

This is a many positions to one profile,

so that's a many-to-one relationship.

OK, so, this is the old trick, right.

In the middle of the edit,

I might have deleted one of these things and added another one.

And so, what I'm going to do is,

I'm going to delete all the old ones.

OK, all the old ones and then add the new ones.

So, I'm going wipe them all out.

So, I'll delete from position where profile ID equals.

Now, we already know that this is good because we tested it way far above.

And that I've moved the code to insert the positions.

It's the same as the previous assignment,

but I've now inserted it into this util code.

Here is insert positions.

And it's going to take the database connection and

the profile ID and it's just going to loop through these things,

do validate if they're there,

do validity checking on them,

and then insert them.

And in the edit case, we're not inserting,

and so it's the old profile ID,

because we already know what the profile idea is,

it's coming in on that get request, right.

And so that's coming in.

And we're going to the same thing for education.

We're going to clear out the educations that hook up to.

So, if I go in education,

there's only one in here.

Let's put another one in.

Let's make another education.

1995 Duke University, save.

So, now we should have.

All right, so this is a many to many.

We can have many institutions

go into one profile and many profiles go into one institution.

So, if we had a second profile in here,

then we could add another profile and there could be,

so it's a many-to-many relationship.

And I'll go through that in some detail once I've taken a look at the code.

And so I've got, we're still above the line, right.

We're still on the model code,

so I'm going to load all of the positions and all the schools

into arrays and that is here.

Here it's just a select.

I'm using this fetchAll that you may not have seen before.

When you have a select that's going to return multiple rows and you just

want an array of the rows and each row of course as an array,

it's going to be a linear array of rows and a key value array as each row.

You can use this thing called fetchAll and it really is,

you surprising me do this before.

Or just while on my way through it,

looking to see if the row is, well,

as long as the row is not false,

just keep adding onto the end of that array,

but that's built right into PTO.

And you might think, oh,

that's going to do a lot of rows.

Well, you're supposed to use limits

and clauses and things to make sure that in any query you're doing,

you're not bringing back a billion records.

So, this fetchAll turns out to be a really useful thing

and it replaces four lines of code with one line of code.

And so, I'm going to just do the same thing.

I've got pulling the institutions in.

And you can see this when you're looking at the view guide.

That's going to give you one two educations and one two positions.

And that's what these give you back is an array where each one of these is a row.

OK? And so in edit, I load them up.

I leave them in positions in schools,

because the first thing you always have to do in edit,

is you have to read what's in the database and you have to render it in forms, right.

So, we've got to make this,

if you inspect this element and we take a look at it,

this pattern is kind of the same.

Well, edu_year one that we haven't done yet.

So, let's look at the positions.

Inspect the positions.

So, year one and then desk one.

You remember the pattern? Year one desk one.

And then session two is year two and desk two.

And that of course, correlates with this,

where we're pulling the stuff out of the post database,

now one, two, three, four, five.

And that's how we're kind of looping through an array of fields as it

were where it's kind of an array we're constructing by concatenation.

OK? And so, if we take a quick look at this education code,

there's a plus there.

And let's just take a look at the Javascript,

although this should be familiar with you,

because it's not that different.

The addPos, remember the addPos?

We are going to go there and grab a little plus sign and then every time you click it,

we're going to add a new thing and then concatenate

some strings together and automatically count these things up.

So, for education, we're going to do the exact same thing.

Check, count them up.

And we're actually going to grab.

We're going to do this one a little bit different.

We've got this edu template which is just some text in here.

So, this is a pattern for putting hidden text in.

We make a script tag.

And I give it an ID and a type equals text,

instead of type equal javascript.

And now this is just a little string and I'm going to

replace this @count with the numbers.

So, you see this @count. I just made this up.

So, I'm doing a string replace.

Let's go up here. And I am doing,

right here, I'm going to grab the source,

that is that text down there.

Then I am going to do a replace of that @count@ with a little number.

And so it's kind of doing the same thing as this is,

it's just a little.

It's six and one half a dozen the other.

This is ugly and a little harder to get syntactically correct.

I'm using a template in the second one.

OK, so that's how we do that.

But, we also then have to,

I skipped ahead here.

We have to, when we were showing this page,

we have this array of schools and we have an array of positions.

And if there is more than zero schools,

we have to loop through.

And we're going to start with this variable countEdu.

Now, the interesting thing is,

this is a PHP variable for now.

It's the same name as the one we used in Javascript,

you'll see this in a second.

So what I have to do is I have to construct the markup.

So, when I just display it,

these markups are coming from PHP,

looping through the existing things that it pulled out of the database.

OK? And so, we make the div.

If there are some schools,

we put out the div Edu one.

Now it's very easy to show the position because you're familiar with that.

So, we have the plus value for the addPos,

we have the div.

You'll notice that, let me go down here a bit.

If there are nothing here,

we just have an empty div and that's where we'll put the new ones.

So, we have count positions here and then we put out the div.

We give it an ID based on composition,

we get input type equals text.

Year one, year two, year three.

And then we take the old data,

the old year out of the position,

and then we take the old description of the position, making good,

taking care of all HTML entities,

and we construct the JQuery to get rid of it,

so we're constructing this little minus sign to

say go to pound sign position one and remove it.

So, if I go like this,

position one, it goes away, poof.

Right, so that's generating this and there you go.

And we do a similar thing for the educations.

We have an educational counter,

we have a little field, we have a little plus, another plus guy,

and then we have a field that goes round and round and constructs all this markup.

So, all this markup is coming from PHP.

Right?

The one, two, three.

Edu year one, edu school one,

the school, and that's all just strings at this point.

Value equals University of Cambridge.

This of course here has been HTML-escaped of HTML entities,

the way you go.

So, when this code is all done right here,

in the server, in PHP,

we have this variable countPos,

which is the number of existing positions that have come out from the server.

And then we have our form and then we go to Javascript, right.

So, we're now in HTML. So we've exited PHP. We're back in HTML.

We're printing out the submit form and cancel form right here.

Save and cancel.

And now, what we're doing is, we're saying countPos,

this is the Javascript variable,

equals, and now we drop in a PHP,

the value of the PHP.

So, I'm going to just refresh this screen and give you a sense of what's going on there.

New page. So, this is a trick.

This is, in effect,

trickily passing the results of

a PHP computation into

a Javascript variable because I'm going to need that Javascript variable.

See this? So, countPos equals two.

There's two of them and that's coming from here,

it's just this little two is the number of things that were generated from the PHP.

So, the PHP goes like that,

it generates the one and the two.

This is probably an easier way to look at it, right.

And then position one and two.

And then this way,

it means that when we hit the plus button,

so I haven't hit the plus button.

So, if I hit the plus button,

it's going to be number three.

So, I hit plus, and inspect this,

because countPos starts at two and the first thing this Javascript code does is adds one,

the countPos, now we're in Javascript,

and then it builds this new div.

So, this is the new div and these were the old two divs and they look the same.

And if I do an inspect element,

you'll see that this is year three,

and countPos is four at this point so if I hit it again,

it's going to be year four.

Let's pos again, then this next one down here is going to be year four.

I haven't submitted anything,

I'm just playing with the document object model at this point.

OK. And countEdu, addEdu is doing the same thing,

it's just instead of doing this ugly careful string concatenation,

it is making a template with this @count,

that I just made up to be the substitution point for that little number,

whatever that number is. OK?

So, that's just that really had nothing to do with JSON which is one of the things.

And so the JSON part is,oops,

the JSON is kind of simple and boring.

The JSON is, JQuery auto complete.

So, I'm using this JQuery auto autocomplete widget.

And this is how it works.

You can have a tag just an input and then you basically say,

"Hey let's hook this up and go to this tag and autocomplete with the source of the list."

Now this one is showing a list in this JavaScript variable but you can also

make the list come from a PHP file that goes into the server.

Okay? I mentioned this way, way at the beginning.

So, let's review this.

And so let's take a look at,

I'm going to make it fresh.

Let's add in education 2001.

So now view developer console network.

Okay, so edu_school as class equals school.

Class equals school.

So, every one of these text areas that I want to have autocomplete working on,

I've put a CSS class on them.

And then I can in a single JavaScript line,

no three JavaScript lines, say,

"Hey JQuery go find all of the tags with

school class equal school and make them autocomplete fields.

And the source of the autocomplete data is this

on in server PHP script called school.php."

So, here is school.php.

It does a select with a like clause and it uses a percent so that's like UN percent.

And then it sends it back in JSON.

So it retrieves all these rows,

pulls out the name of the school.

Remember that the institution looks like this, looks like this.

Here's all the names of the schools, right?

Remember Lansing Community College I added because I put that in.

Because you can cause it to add a new one, okay?

Well it's a complex one, isn't it?

It's a complex one. That's why we're near the end of the class.

Okay, so if I type L,

it sends a request.

I didn't, I had to build this PHP code.

And it says, go do a select and give me all the things,

all the institutions in the system that have L as the prefix.

And then all this other stuff that was all done by JQuery and JQuery UI.

All that pretty UI,

I didn't make any of that.

And now this is now simply a text field, okay?

So if we inspect this,

it is just a text field.

The values for some reason not really here.

I don't know why that is. But that'll get fixed before we hit the submit button.

So, now what I want to walk you through is the submit button here.

So, let's also add another one.

Now not a position. I didn't want that one.

Goodbye. Let's add

another education 2005 school of driving.

So, that doesn't need any criteria as matter of fact for you to develop a console.

I should have showed this as it was going.

Let's look at the network, school of.

So I said, "Oh why start with school of?"

And the answer is nothing starts with school of.

Which is okay we're still allowed.

It's just a text field, school of driving.

So, the way this works is this is going to be a many to many.

So we know that lanes, I'm going hit the save in a

second here and show you what's going to happen.

University Cambridge already exists,

Duke University already exists,

Lansing Community College already exists.

School of driving does not already exist.

And so let's take a look.

So school.php was just to get the UI.

When we hit the submit button,

we're going back to edit.php way at the top, way at the top, way at the top.

Insert educations, okay?

So it's like you've got Edu year one,

school name one, Edu year two, school name two.

And this is going to do all that stuff.

And again because we're in the middle of an edit,

we already know what the profile ID it's 11.

We're also going to use the trick to delete out the old entries, right?

We're going to clear out the old position entries and the position,

not the position entries. That's a typo.

We're not cleaning up the institutions,

we're cleaning up the education entries.

These institutions are still here.

The education entries are the entries that map from

a user to a school that indicate that they're at that school.

By the way we're modeling the year as data.

We're modelling the year as data on that many-to-many link.

Okay, so let's go to insert educations and take a look.

This is probably well this is the most complex thing we are going to do.

So, we'll make our screen bigger and we'll make our text smaller because.

Okay, insert positions.

Not too bad, right?

So here is the insert educations.

It's got a little more to it.

So the first we're going to go through we've got these edu,

we've got this post-date of edu year one,

edu school one, edu year two, edu school one,

that is this data edu year one,

edu school one, edu year two,

school two that data.

If it's not blank,

if it is blank we're going to sort of,

I guess we're not even doing validations because we

should have validated it before but we didn't do it yet.

Maybe something you could do is add code validation.

So, the key is that we got this variable school which is

the string name school which is like Duke University.

So what we're going to do is we're going to look up in the institution,

select institution ID from institution where name equals name.

So, where a name equals Duke University.

So, we're going to go to institution

where name equals Duke University and we're going to find one.

So we're going to get a sixth there. So we're going to be good.

So that basically says we're going to do a fetch.

Now remember if it does not there in SQL that's not a bad thing.

Row becomes false but it's not like a failure.

You said give me the record where this is true and the answer is there were none.

That's a valid answer.

Okay? So if the row is not false that means we got a row.

Well we know what the primary key of that institution is which is six.

So that means that institution ID is going to be six.

In the case where I just typed in

the school of driving institution ID is going to still be false, right?

We set it to false and we didn't set it to a number.

Then I'm going to do an insert into the institution.

And so when I insert into institution table so I'm going to add one to this.

I haven't hit the button yet so it's not going to do it.

But there'll be a new one added it'll get the number 11 if all goes well.

And I want to ask the PTO because I just did an insert say,

"What was the institution that you just inserted was the primary key?"

In this case it'll be 11.

And so at this point by this line right here,

I either have looked up the old institution or inserted it.

So I don't exactly know nor care which of those two things happened.

But I do have this variable institution ID that I know is the primary key of

either the old institution that was already there or one that was just recently inserted.

So that's pretty cool.

Remember rank is just my little way of having a sort order.

If you go over here I just make those go up by one,

two, three, four, five so I know which one they are in case the years aren't in order.

I don't want to order by year.

I'm ordering by rank.

Okay, so profile ID it's a many-to-many, right?

Profile ID, institution ID,

year came from the post data and rank is

the little number and we're done because I've got that working.

So, let's go now and see when I hit this button what's going to happen.

You'll also see that,

well actually these guys are going to get deleted but then they're not going to get

re-added because there's no primary key being built here.

This is the many-to-many table that's pointing to two primary keys,

primary key in profile and a primary key institution.

So I'm going to hit the button.

I've been threatening to hit the button. Now I'm going to hit the button clank.

Okay, so now we should see is these should be the same.

We should see one more because we are doing, add two more I don't know.

We ended two more,

Lansing Community College and then 11.

If you look at 11, 11 is the school of driving.

And if we look at the institution table we see that

it inserted as an 11 school of driving.

So I ran this if there was no institution inserted so we ran it.

We got back this 11,

the 11 came back here in PTO insert, the last insert ID.

Well, that's really good.

And then we inserted this record the 11,

11 profile 11, institution 11.

And so now there are four educations, let's come back.

Four educations and so we see four educations.

And now it works.

So that's a bit of a walkthrough of

this really complex and important thing where we're starting to

show as we go forward there'll be

increasing ways to JSON to back in what we're doing in JavaScript and changing the UI.

So, I hope you found this interesting and I hope you do well on this assignment.

Code Walkthrough: Profile, Positions, Education, and JSON

JavaScript, jQuery, and JSON

与讲师见面