Function

00:00

I welcome back to module five. This is less than 5.5 function. This lesson we're gonna describe implementing a my simple function within our migration files were also going to discuss a couple additional concerns with the PHP are Titian process when we begin implementing additional my sequel objects,

00:18

So let's go ahead and begin moving through these bullet points. So we need to create a function that accepts the user i D and return to their grade point average or G p A.

00:27

So to implement this function, we're gonna need to utilize the grades table because that's where all our students grades exist.

00:33

In addition, we need to respect the composite key. So even though the first bullet point says, except a user, I d return their grade point average. We need to be aware that there's also the concept of different schools that could have users with the same user i d.

00:50

And ignoring this would cause multiple students to average together if more than one student had the same I d. So if you had three students at three different schools that happened tohave the same, I d. We would accidentally average all their graves together all those three students and return that we don't want to do that.

01:07

So that means we're gonna have an additional

01:11

parameter. And that is the school I D. So when this function is implemented, it's gonna accept two parameters the school I d and the user i d

01:21

and the additional concerns that we need to be aware of when we begin to implement these additional my sequel objects is that we can no longer use PHP are Titian migrate fresh And that was the reason we cannot use this command is because this command automatically drops all tables and runs the up

01:40

function in migration files. Because this is a function dropping all tables will have no effect on it.

01:46

So then once it drops on the tables and it gets to the up function call off, this migration file is going to fail because it's going to say that function already exists. And that is true for functions, views, triggers and procedures that he used in these migration files. So we want to switch to migrate, refresh

02:04

What this does is it runs all the down functions and then runs the up functions.

02:07

So we won't will not run into that problem.

02:10

So in the next section of this lesson, we're going to begin writing our query that will return the average great point there. The grade point average of the students based on the school I d and the user, I d. And it isn't. Exercise it. Be a good idea to go ahead and see if you can write that query that would that would use the school I d and the user i d

02:30

before looking at that part of the video and seeing if we come up with the same quarry

02:36

and once we designed that, we're gonna go ahead and go through the process of implementing it in a migration file.

02:43

So let's go ahead and head over to our virtual machine.

02:46

All right, let's start reading that quarry. So I'm gonna start with a select statement from his star. I know. I need the grades tables. I'm just gonna do from grades

02:55

where school I d equals one and user. I d equals one. I've hard coded. These values were gonna soft code. Um, when we movement to the function and what that means is that they will be dynamically provided parameters.

03:09

So let's from the average function.

03:13

And I think it's great Score. Sure is.

03:16

We go ahead and run this

03:19

getting no back. Let's go to the Great State will make sure that makes sense.

03:23

So I have this in ascending order. There's no user idea of one, so I'm not going to get anything back. But I have a user idea of to at school I d one.

03:31

So let's go ahead and switch the user. I d to two

03:36

and I'm gonna run that again.

03:38

49 12.

03:39

That makes sense. This user only has one score, so the average of that scored is that score itself

03:46

so something we can do? Indeed. Beaver, if you're using D beavers, we can go into the procedures we can right click,

03:53

create new procedure.

03:54

We select a type of function. I'm called this Get underscore G p. A one hit. Okay,

04:00

Nothing saved in the database yet.

04:03

I'm just gonna get the basic framework for my function out of this.

04:09

More switches to a decimal cause the GPS typically investment

04:13

make it a length of eight with a precision of two. So two decimal places

04:18

and

04:20

when I put in our return statement wanna put brackets? And then I would have pace my quarry in there.

04:30

And I'm just going to rearrange this a little bit.

04:36

I put my semicolon at the end here.

04:41

I'm going to place in the parameters. So we have school,

04:46

Big ant. You want to use the same data types that are on the tables? Otherwise, you can get very ambiguous airs. If the size of manager were to go out of the scope of one of the objects.

04:58

So user with an underscore because it's the key word.

05:01

Big Ent

05:04

returns the decimal eight to Okay, that looks good.

05:11

We also need to put a

05:14

deterministic keyword here.

05:16

Let's go ahead and click. Save. I'm not going to run this from here. I'm just gonna copy the code.

05:21

So that looks like that's our code for the function. I'm gonna hit. Cancel. We'll go back to my script. Pain

05:30

paste it in there.

05:30

I'm gonna go ahead and switch to my terminal. I'm in a ssh session with my vag Great machine. I'm in the right directory, so I'm going to have PHP artist in

05:43

make colon migration

05:46

get underscore GP a function

05:49

when I use a decent naming naming scheme so I can tell what's probably in the file before actually look in it

05:57

and it looks like it ran that.

05:59

So I'm gonna go ahead and open that file

06:02

right Click at it.

06:04

I have my up function have my down function.

06:10

So what we need to do is we need to make a call to DB Colon Colon, un prepared.

06:15

And that means it's just gonna run the

06:18

actual sequel statement that creates this.

06:23

However, we need to be careful because there are some slight differences

06:28

in this

06:30

and that we don't need,

06:31

um the change of the, uh

06:34

the same Michael into the dollar sign or some other symbol to reference a terminating statement.

06:41

Some to go ahead and paste this out starting at the end, or I'm gonna copy it first.

06:49

You go back to that file,

06:53

paste it in there

06:57

so I don't need this semi colon at the end here,

07:00

and I believe that is correct. So I'll just save it.

07:05

And then on the down function, I need to put d d b colon, colon unprepared again

07:14

brackets quotes,

07:17

and then

07:19

I'm gonna take this call

07:27

and put it in there,

07:29

and I'm gonna save the file.

07:31

I believe this will work. Now, let's go ahead and run the migration.

07:39

So I'm gonna do a piece. P art Ishan,

07:44

My great

07:46

brie. Fresh.

07:50

Okay, The air here is I forgot a semicolon.

07:55

So I forgot to semicolon right there. And then it got to this symbol and he got confused. So let's go ahead and run that again.

08:09

We're using refresh instead of fresh, because fresh will not function correctly when we go to. And Stan, she ate the objects again because it won't run the down part of the It will not run this part.

08:24

And that will cause this to get an object already exists air. And it looks like I forgot to do something here. I forgot to use the variables instead. So I need to use school

08:35

And be sure to not give these the same name as the column name. Otherwise, my sequel gets confused and is not sure which one you're referencing and it won't give you an air will just return bad data. So let's go in and save that.

08:50

Let's go ahead and run the refresh Command again.

08:52

So that's gonna run the down part of all your files and then run the up part of all your files,

08:58

which is what we want.

09:00

And we have that g p a function.

09:01

So let's go ahead and refresh

09:05

here. It ISS

09:07

looks good. Let's give it a test. A good way to test. It's just to select,

09:13

test

09:16

from

09:18

and dual. And this is just a key word that means I want to select a record from a non existent table. So if I run this,

09:26

I'll get a result back. And what we can do with this if we can say get G p A

09:35

11 user, one school, one

09:39

run that no back. But we had a

09:43

A user i d of two that had a great back. So let's school one user to

09:48

that right that I reversed that.

09:52

So a school first user

09:54

grades, You know, I I have to see the database again.

10:01

Let's go ahead and run that cedar.

10:11

Okay? It's seated correctly. So now if I refresh I have data. I could go back here and I can run this test

10:20

so school one user to doesn't have anything

10:24

so have a user idea of one

10:26

as

10:26

cool of three. So

10:30

switch this to a

10:31

three

10:35

or reversed it.

10:37

It should be 31

10:48

There we go. All right, so it looks like it's working. Now let's go ahead and head over to the summary,

10:54

and that brings us to the lesson 5.5 functions summary. So what did we do in this lesson? Recreated a g p. A function that accepted two parameters. The user I d and the school i d. The architecture of the function was an embedded query.

11:07

And within our migration file, the function is now automatically created when we run our migrations. However, we need to use my great refresh when we re implement our migrations because we need to ensure that the down function of the files are run. Because my great fresh does not run the down part of the files

11:26

so

11:28

it would run into a object already exists air.