Advanced PHP Web Application Development

Hello again, and welcome back to Educator.com's Advanced PHP with MySQL course.0000

In today's lesson, we are going to be continuing development of our web application,0005

incorporating what we learned in our last lesson about linking tables.0008

What we learned in our last lesson was how to set up our database so that it can handle departments.0014

And what we have done is created two tables in our database: a Departments table, and a depts_items table,0020

to store all of the information about a department.0027

And the depts_items table is our linking table, and that is what we use to keep entries associating items with a particular department.0029

What we are going to be doing today in our version 16.0 of the web application is:0041

we are going to be officially moving all of our web application functionality that deals with departments0046

out of this departments.txt file and the functions that operate on that, that were in fileLIB.php,0051

and moving everything into the MySQL database.0057

And how we are going to do that is: we are going to be implementing some more methods in our DatabaseAccess class0061

that are going to allow us to add items to our store, add items to a department in the store,0068

and then also to be able to retrieve department information from the store.0074

For example, we know two types of SQL statements so far.0079

We know about INSERT statements, as the first one; so we are going to learn how to insert a department into the store, using an INSERT statement.0084

Then, as a separate operation, we are going to learn how to add an item to a department.0090

What is going to happen is: in our INSERT Department functionality, we are just going to be inserting a row into the Departments table.0096

And as we saw in our last lesson, the Departments table just has the department ID and the name of the department.0109

And then, all of the items in the store are contained in the depts_items table.0116

So, this method here, addItemToDepartment, is going to be inserting a row into the depts_items table.0120

What that is going to allow us to do is to associate a particular itemID with a particular deptID, essentially adding that item to the department.0131

Between these two types of INSERT statements we are going to have, we are going to be able to add a department and add items to it.0139

Now, what that means is that the information for a department is going to be spread across two different tables.0146

In our second type of query that we know about, our SELECT query, we are going to have a couple of methods0152

that are going to have to make use of that knowledge that the department information is spread over two tables.0157

For example, we are going to have a getDepartment method, which is going to have to run two queries to load a Department object.0163

It is going to have to run a query on the Departments table to be able to load the name and department ID of a particular department.0170

And then, it is also going to have to run a query on depts_items table to be able to load all of the item ID's of the items in that particular department.0177

getDepartments is going to work in the same way, except it is going to create Department objects for all of the departments in array.0186

So, it is going to be running multiple sets of SELECT queries.0192

isItemInDepartment is a method we are going to be using that is a query we are going to run on the depts_items table.0196

And what it is going to allow us to do is to see whether an item is in a particular department.0206

We will be providing this method a deptID and an itemID, which are rows in our depts_items table.0212

And it is just going to check to see if a pair like this exists in that table.0226

And if it does, that means that the item is in the department.0230

We are also, like for our Item object that we have in our department in the store...we are going to create a deptExists function,0232

which is going to run a SELECT query; in this particular case, it is just going to run it on the Departments table.0240

And it is just going to check to see if a department with a particular department ID exists in our store.0245

Let's take a look at the DatabaseAccess class and the updates that we have made to that,0252

in order to see these new functions that we talked about.0258

I'll blow this up a little bit.0261

First of all, in our last version of DatabaseAccess, besides the constructor and destructor, we had four other public methods.0264

We had a getItem and getItems method, which were used to retrieve an item from the database; they called a single SELECT query on the Items table.0273

And that is because all of the information about an item is stored in that single table,0284

whereas for our departments, it is stored across two tables.0287

We have the insertItem method, which performs an INSERT query on our Items table,0291

and then itemExists is the method that would test if an item exists in our database, simply by searching0296

to see if a row exists with a particular item ID in the Items table.0303

In our new version of DatabaseAccess, we have a number of additional methods.0308

We have two 'get' methods, as we had just mentioned on the slides.0313

We have a getDepartment method and a getDepartments method that are going to load,0317

respectively, a single department from the database, or an array of all of the departments in the database.0321

And the way that they are going to do that is: they are both going to have to run two SELECT queries per department.0327

You are going to have to run, as mentioned, one SELECT query on the Departments table to get the name and the ID of the table,0331

and then another on the depts_items linking table to be able to build up an array0336

of all of the item ID's that are contained in a particular department.0342

Those are the analogs of getItem and getItems.0346

In the same way, we have an insertDepartment function, which is an analog for the insertItem function.0350

We have deptExists, which is just like itemExists.0357

We have isItemInDepartment, which, as we just mentioned, is something that doesn't really have an analog for an item.0361

But it, as mentioned, just tests if an item is in a department.0368

And then, we have also added this addItemToDepartment method, which is going to create a pair in that linking table0371

that is going to link an item to a particular department.0377

First, let's look at the insertDept method.0381

And what this does is takes a Department object, and then it is going to extract the information from that Department object,0387

create an INSERT query, and insert a row into the Departments table.0395

The only thing that is really in our Departments table...and actually, if I load up the MySQL Monitor,0399

and I describe our Departments table, we can see that it has two columns; it has a name column and a deptID.0415

So, when we are inserting a row or department into this Departments table, we are going to be just providing it a name,0426

because it is going to auto-generate this deptID, because it is an AUTO_INCREMENT column.0433

When we call this insertDepartment function, we pass it a department object.0438

What it is going to do is build up an INSERT query that is going to insert into Departments.0442

It is going to provide null for our deptID, and that is because the table generates that0447

automatically whenever you provide a null or a 0 value for an AUTO_INCREMENT column.0452

And then, we are also going to provide the name of the department that was provided as part of the Department object supplied to this method.0457

Just as we did for inserting an item, we logged the query; we run the query.0465

We are able to get the number of rows that were affected, which, for a successful insert, in this particular case, is going to be 1.0470

And then, we also are able to extract, using the insert_id property of that MySQLi object class--0476

we are able to get the insert_id of the department that was inserted.0484

As long as one row is returned, and the insert_id was generated (meaning it wasn't equal to 0), we are not going to output an error message.0489

And instead, we are going to update that Department object, providing it that new insert_id that was generated for it.0497

So now, we have our Department object that is going to have a name and its ID.0504

And then, we return the Department object to the calling script.0509

That is how we add a row to the Departments table.0513

Now, if we want to add items to a particular department, we have a script that we have been using in the past, called addItemToDept.php,0519

where you have a dropdown box where you can select an item and a department, and you can put the two together.0526

Well, now we have a method specifically for doing that, called addItemToDepartment.0532

And what that is going to do is: because it is linking an item to a particular department,0537

and we know that that information is stored in that linking table depts_items, we are going to be creating an INSERT statement.0542

But it is going to be an INSERT statement in that depts_items linking table.0549

This method takes a deptID and an itemID as parameters.0554

It checks to make sure that both of those items exist in the database.0559

It checks to make sure that the item isn't already in a department, using this function that we had mentioned0563

that was developed in this version of the web application, which just tests if that pair already exists in the depts_items table.0568

If it already exists, it is just going to return true.0575

And then, if it doesn't already exist, and both the item and the department exist in the database, it is going to create this INSERT query.0578

And as you can see here, our INSERT query is saying, "Insert a row into the depts_items linking table0586

that is comprised of the deptID and item pair that was provided to this function."0595

And we go ahead and, because it is an INSERT query, we run the query using the query method.0602

We don't need to find out the number of rows that were returned.0609

We can just test this query method, which will return just true or false on whether the insert was successful or not,0613

because there is no insert_id that we are interested in; there is no AUTO_INCREMENT column.0618

We can just test that query returned true; if it returned true, then we would return true from this method,0622

saying that it was successfully added to the department.0629

If not, we are going to log an error, and then return false, saying that the item couldn't be added to the department.0631

That is how we add a department to our database.0637

And then also, that is how we add items to our department.0642

Now that we have a department in our database, one of the things that we want to do is check if the department exists.0650

And simply, the way that we do that is the same way that we do for an item.0657

We just write a query on the Departments table, asking if a row exists where the deptID is equal to a particular deptID.0662

If it returns a row, that means that the department does exist in the database, and we can return true or false.0671

And so, that is how that works.0678

Now, getDepartment is a little more complicated, because as mentioned, we have the data for a department spread across two different tables.0681

It is spread across the Departments table, and then it is also spread across the linking table depts_items.0689

We know a Department object has three properties: it has a deptID; it has a name; and it has an array called Items0694

that is an indexed array of all of the item ID's of all of the items in that particular department.0702

In order to get a department from the database, we need to build up that department object,0707

and that is what this getDepartment function is going to return.0711

It is going to return a built-up object that is going to include the deptID, the name of the department,0714

and then also that array of all of the items in the department.0719

We are going to have to run two SELECT queries.0723

The first SELECT query we run is on the Departments table.0727

And we are saying, "Give us a row in the table where the deptID matches the department we are trying to load."0730

We run the query, and then just test to make sure that one row is returned, because our deptID is a primary key, so we should only be getting one row.0738

Assuming that is true, then we have to take another step.0748

We know that the department exists in the Departments table; now, we need to find out if there are any items already in the department.0751

What we do is create another SELECT query; and in this query, we are just saying,0759

"Give us all of the values in the itemID column from the depts_items linking table0766

that have the deptID of the department we are searching for."0775

So, we are saying...for example, if we want to find all of the items in department 1...0778

"Give us the value of the itemID column for any rows that have the deptID equal to 1."0782

For example, if we take a look at the data in depts_items, we can see that our deptID 1 has two item ID's associated with it: 1001 and 1004.0789

What our query here that we were just looking at was: SELECT itemID from our linking table for department 1, for example.0808

So, we are saying, "Give us all the item ID's in a result set from this linking table where the column deptID equals 1."0823

So, we have two rows that are here, so we are going to get a result set that should have 1001 and 1004 in it.0833

And sure enough, when we run the query, that is what is going to happen.0837

That is what this query is going to do in our getDepartment function.0840

We are going to run the query, and then it is going to return a result set.0846

And that result set is going to be that MySQLi_Result object that allows you to call the fetch_object or fetch_array method on it,0849

that allows you to loop over each row returned in the result set.0858

What we are going to be able to do is loop over each row, and then pull out all the item ID's that are associated with that particular department.0863

Well, like for our getItem method, we have a private helper function within the DatabaseAccess class that we created, called buildDeptFromRows.0870

And what it does is builds up a Department object from the row returned when we ran this first query,0880

that gives us the name and department ID of the department.0887

And then, also, when we run the second query that is going to get the item ID's of all the items in the department,0891

that returns a result set object; we are going to pass that to this method, as well.0897

So, what is going to happen is: this private helper function is going to be able to extract information from the first query,0904

and then also extract all the item ID's from the result set of the second query.0910

If we go and look at the source code for this file, so we can see the private method,0915

we can see that buildDeptFromRows--what it does is tests to see if any rows exist0923

in that Items result set--meaning, "Were there any actual items in the department?"0929

If not, it just goes ahead and returns a Department object that contains the ID obtained from the row0934

that was returned from the Departments table query, and the name that was returned from the Departments table query,0940

and then an empty array for the Items, because no items are in it.0946

If that is not true, then what it does is: it is going to loop over the items result set that contains all of the itemID's of all the items in the department.0950

And what it is going to do is create this Items array, in which it is going to add the itemID of each row in that result set.0958

So, each row is going to have an itemID for each item in the department.0966

So, items will be built up; for department 1, for example, Items is going to contain two values.0970

It is going to contain 1001 and 1004.0975

And then now, we return a Department object that has the ID and name that were extracted from the Department row query results.0978

And then also, it is going to have set as its Items property that Items array.0989

We are going to have it set to this built-up array that we were able to build from our second query on the depts_items table.0994

getDepartments works the same way as our getItems does.1008

It goes through and runs a query (instead of on the Items table) on the Departments table.1012

And it is able to load a row for each department in the store.1018

And all we are going to do is (assuming that Departments exists in the database): we are going to create an array of Department objects,1023

and then replicate what we did in the getDepartment method that we just talked about.1032

For each row in the Departments table, we are going to go ahead and build up a Department object.1037

We are going to call the buildDeptFromRows helper function again.1046

We are going to pass it the row from the Departments table for each department.1050

And then, we are also going to pass it the result set, again, from that second query on our depts_items table.1055

It is going to build up this Items array and then return it.1062

And we are going to use that in a number of different spots--for example, in our left-hand navigation bar, we call it.1064

In our header.phtml, that calls this getDepartments method to load up all the Department objects to be able to list a link to each department.1069

It is also called on store.php: in the middle of the page, we have a list of all the departments.1081

And then also, in our admin site, when we have all of these dropdown boxes that list all of the departments,1085

that is where it is going to be used, as well.1090

Those are all of the different methods that we have added to move this department information into these two tables in our database,1093

to be able to insert department information, and then to be able to extract it.1101

So now, we are going to talk about how the script for this is used.1106

First, there is a script created called addDepartment.php; it is a new admin page that we have created1112

that is going to allow us to add a new department to the store database.1117

And as mentioned, it is going to be calling this insertDepartment function, which, as we learned, doesn't add any items to a particular department.1120

It just adds a row to the Departments table, so it is only going to take one value; it is going to take a name.1128

So, if we go and look at our new administrator site, there is a new link for addDepartment.1135

And it allows us to enter the name of the new department that we want.1143

For example, if we look at the store, and we refresh the homepage, the current status of the store1146

is that there are three different departments in the store.1152

And if we run a SELECT query on the Departments table, we can see that there are three departments in the store right now.1155

Well, with our new addDepartment page that is going to call this addDepartment function,1167

we can create a new department (we will call it Shoes), and we can add the department...or let's say Winter Clothes.1172

When we add the department to the store, it is going to let us know that it was added; it was assigned department 4.1186

Now, when we go back and run our Departments query again, we are going to see that it has been added to our Departments table.1192

When we go to view the store homepage again, we are going to see that it has been added to our homepage, and then also to the left navigation bar.1197

And then, when we click on it, it will take us to that department page.1206

And right now, it is going to show a table with no items in it; and that is because we haven't added any items to the store.1209

All we have done is created that row in the Departments table.1214

Well, we have updated our addItemToDepartment function to be able to add a row into that depts_items linking table.1217

So, let's add, for example, the winter jacket to the new Winter Clothes department (which, you can see, shows up in this dropdown box).1227

And when we add it to the department, it is going to let us know that it was successfully added.1234

If we go back to the store, we refresh the Winter Clothes page; we can see that it is now in there.1238

What is happening is: our addDepartment script is calling the insertDepartment method.1244

Our addItemToDepartment script is calling the addItemToDepartment method, which inserts a deptID/itemID pair within that linking table.1251

Our store pages are going to be calling the getDepartments method, which is going to load all the departments from the database up.1265

It is going to allow them to output links for them.1273

And then, we are also going to be able to...when we go to, for example, Department.php for deptID=4...1276

load a department; it is going to call the getDepartment function that we just created to load a department from the database,1285

which is going to get its name from the Departments table, and then all of the items in it,1291

which, in this particular case, is one, which is Winter Jacket.1294

And if we go and look at our depts_items linking table, we can see that we have a match here between deptID 4 and item 1004 (which is the Winter Jacket).1298

So, it has added that item to that department; it has added that pair to our linking table.1315

We saw about addItemToDepartment, which is that script that has those dropdown boxes, allows us to add the item to store, and calls that addItemToDepartment function.1326

Department.php is going to make use of, for example, the getDepartment function,1336

which is going to allow us to...when we click on our Winter Clothes department, that is what is going to allow us to pull up a department,1341

and then be able to output a row for each item in the department.1352

That is going to make use of the getDepartment function.1356

Our store.php page is going to use the getDepartments function, which allows us to get an array of all the departments in the store.1364

And then, our admin header.phtml has also been updated, so that it calls this new getDepartments method,1371

so that it can output the links on the side.1376

Additionally, our outputDeptSelector static method in the Output class, which is used to output the dropdown box1379

to select a department on our admin store, has been updated to use this new getDepartments method1386

to load all of the departments from the database.1392

And then, I just want to mention that, because all of our data now, as far as the store is concerned right now,1394

has been moved into a MySQL database, it is out of our flat file system.1400

We can get rid of departments.txt, and then we can also get rid of fileLIB.php, which...all that that contained,1404

in our last version, was functions related to getting a department, seeing if a department exists, and adding an item to a department.1413

Those have all been moved out, so fileLIB.php is gone.1420

So now, we have moved all of our item and department content into our database.1423

And our store is just running solely on MySQL database right now, as opposed to a combination of flat files and database.1430

Thank you for watching today's lesson; I look forward to seeing you next time.1436