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 on DELETE queries.0008

Today's lesson is going to cover a new version of the web application, called 17.0.0013

And what we are going to be doing is updating our administrative interface,0019

so that we can delete both items and departments from the store,0024

as well as add or remove items to and from different departments--particularly removing items from the departments.0029

To do this, these three new scripts, which we are going to be making, which are going to be called0040

deleteDepartment, deleteItem, and removeItemFromDepartment, are going to have corresponding methods0045

that we are going to be creating in DatabaseAccess that are going to make use of DELETE queries.0054

So, we are going to have a deleteDepartment method that is going to delete a department from the database.0058

We are going to have a deleteItem method that is going to delete an item from the database.0066

And then, we are going to have removeItemFromDepartment, which is going to allow us to just, as the name implies, remove an item from a department.0071

These first two DELETE operations are going to require that two DELETE queries be run.0078

For example, for the deleteDepartment function...we are going to have to run a DELETE statement on the Departments table.0088

And that is because we know that our information about a department is contained in two tables.0097

It is contained in the Departments table, and then also in our linking table.0102

It is going to have to have one query that is going to delete information from Departments.0105

It is also going to have to have a second query to delete any associations between items in that particular department,0109

because if we delete a department, it doesn't make sense that we would have items still associated with a nonexistent department.0116

Likewise, when we are trying to delete an item, we are going to be deleting a row from our Items table.0122

And at the same time, we are also going to be needing to delete rows from this depts_items linking table.0131

And the reason for that is that that item that we are deleting might be in a particular department.0138

And it doesn't make sense to have an item that is no longer existent associated with a department.0143

Both of those are going to run two separate DELETE queries.0149

And I am going to look at the code for that.0153

removeItemFromDepartment, on the other hand, is just going to have one query.0155

Removing an item from a department--the way that we do that is simply deleting a row from our linking table that links an item ID and a department ID.0158

So, it is just going to run a DELETE query on our depts_items table.0166

Let's take a look at our new DatabaseAccess class and the updates that we have made.0172

In version 17.0, we have three new methods: deleteDepartment, deleteItem, and removeItemFromDepartment.0178

If you look at our deleteDepartment method, it takes a department ID of the department we want to delete from the database.0185

And the first thing it does is runs a DELETE query to delete any associations that any items associated with that department in our depts_items linking table.0194

As you can see, we have a DELETE query here created that says, "Delete all the rows0207

from our linking table that have the department ID equal to the department ID passed in."0211

For example, let me reset the database...0218

If we look at the contents of our linking table with our default database set up,0235

we can see, for example, that for department 1, it has two rows in this table:0246

one linking item 1001 to the department, and one linking item 1004 to the department.0252

In order to delete all those associations, we would run this DELETE query here.0259

And that would get rid of both of those rows, because what is going to happen is:0262

this WHERE clause is going to match both of those rows in that table, because it says,0267

"Delete any rows in that table where deptID equals that," so it is going to delete both of those.0272

That is how we delete the associations for a particular department.0277

If we go ahead and run that query, and then we have a statement that is going to output,0280

"We have deleted x number of items from that department," then the second part of our delete process is:0285

we actually have to delete the item from the Departments table, because that contains the name and the deptID of our particular item.0291

We have a second query called DELETE FROM Departments WHERE deptID equals (deptID passed in).0298

And again, it is the same thing; it is going to locate that one row--in this case, the one row in the Departments table,0304

because deptID is a primary key of our Departments table; and it is going to delete that from the database.0309

We run the query, check to make sure that one row was affected (meaning that that one department row was deleted),0314

return true if it was, and return false otherwise.0320

The same thing happens with our deleteItem method.0325

We pass an itemID to the method of the item we want to delete from our database.0328

We go ahead and the first thing we do is delete any associations between that item and the particular department,0333

because once we delete an item, we don't want to have it linked to departments,0339

because then, when you load a department up, a department page is going to try to load an item that doesn't exist.0343

So, we run the same query as before, except this time you specify, "Delete all the rows where itemID equals the itemID specified."0348

So, if a particular item is only in one department, then it is only going to delete one row.0356

But maybe as our store grows, we might have an item spread over multiple departments.0361

Then, it might delete more than one row.0364

We simple have a log statement that is going to output the number of rows that we have deleted from that table.0367

And then, we go ahead, and just as we had run a DELETE query on our Departments table to get rid of a department,0372

we run a DELETE query on the Items table, and we are saying,0379

"Delete the row in Items that is associated with the itemID that is passed into this function."0382

Because that is a primary key ID, it is going to uniquely identify that row and be able to delete it.0387

We test to make sure that it was successfully deleted, that one row was deleted, and return true.0392

Otherwise, we return false and log an error message.0397

The other method that we have added that is going to make use of DELETE queries is removeItemFromDepartment.0408

What that does is takes an item ID and a department ID, and it is going to use those two to run a DELETE query0414

on our linking table that is going to delete that pair, if it exists in the table.0422

And what that essentially does is effectively deletes that item from the department, because that is how the two were linked together.0427

We have some simple error checking that it does.0433

It just checks to see if the itemID and the deptID are valid, before it even tries to delete their association.0435

It checks if the item is already in the department; if it is already in the department, we don't need to add a row to it.0445

So, it will return true if the item is already in the department.0449

And if not--the item is not in the department (both the item and the department exist), then it is going to run a DELETE query.0452

In this case, it only needs to run one DELETE query, because it is just deleting an association in our linking table.0459

It is going to delete a row from depts_items, and it is going to say, "Delete the row where the deptID0466

matches the one we provided to the method, and the itemID matches the one we provided to the method."0472

Because our deptID and our itemID columns together in our linking table provide a primary key for that table,0480

if that combination exists, this is going to delete one row in the table.0487

So, we test to see if one row was affected (one row was deleted), and then return true if it was, and false otherwise.0490

Let's take a look at how these function in our web application.0498

In our admin site, we have updated it: we have created three new scripts--0506

a deleteItem script, a deleteDepartment script, and a removeItemFromDepartment script.0510

And let's look first at removeItemFromDepartment.0515

For example, it has two dropdown boxes: one to select an item and one to select a department.0519

And you can select to remove an item from a particular department.0526

And if we run this query again to see...these are all the associations we have in our depts_items table.0531

We have items 1001 and 1004 within department number 1.0538

And if we go look at our regular store site of the store--the non-admin site--and we look at department 1,0543

which is Apparel, we can see that it has two items in it, 1001 and 1004, which is a T-shirt and a winter jacket.0549

If we want to remove one of those from the store--let's say we want to remove the winter jacket from the Apparel department--0556

we just click on removeItemFromDepartment, and it is going to let us know that it was successfully deleted.0563

We will notice two things: 1) when we refresh our Departments page, that is no longer going to show up;0567

and then also, if we look at our depts_items linking table, we can see that that row linking item 1004 with department #1 was deleted.0573

So, we can see that the removeItemFromDepartment operation has one DELETE query, and it operates on the linking table.0584

Now, if we want to delete a department, for example, we simply select the department that we want to delete, and it is going to do two things.0591

It is going to need to run two DELETE queries, as we saw.0599

It is going to need to delete any links to items in the linking table, and then also delete its row in the Departments table.0601

For example, here we have department 2: it has two items in it, 1002 and 1004.0609

It also has a row in our Departments table that gives it the name Electronics.0615

So, when we go to delete this department, we are going to delete this row in the Departments table.0624

And then, we also want to delete both of these rows in the linking table that link the items to that department.0629

And that is what is going to happen when we run this script.0635

For example, if we delete the Electronics department, department #2, it is going to let us know that the department was deleted.0638

Now, when we refresh our page, we can see, it disappears from the left-hand navigation bar,0644

because that loads all of the departments from the database.0650

If we run our SELECT query on Departments again, we can see that it has been removed from the Departments table.0653

And we can also see, on the linking table, that these two rows up here that had items in the department have been deleted from the table.0659

So, you are not going to run into problems where items are linked to departments that don't exist.0668

Finally, just to show you the deleteItem script, which works the same way, for example:0674

if we wanted to delete our DVD Player: let's see, DVD Player is item 1005, so let's pick one that hasn't been removed--Baseball Bat, 1003:0681

we can see that currently, in our linking table, it has one row.0699

It is linked to the department number 3, which is Sporting Goods.0703

And we can see that Sporting Goods is department #3; we can also see the contents of our Items table for this particular item.0708

I am just going to pull up the name and price.0720

We can see that the Baseball Bat exists in the store; it has item 1003.0725

Go ahead and delete the item; two DELETE queries are going to get run.0730

First of all, let me go to Sporting Goods: it is not going to appear anymore.0733

If we run a SELECT query on our Items table, it doesn't appear in there.0738

And then also, this link that we had to department 3 in our linking table is no longer going to exist.0742

And we can see that that link has been removed, as well.0751

When we delete a department or delete an item, we have two DELETE queries that are operating, one on the Departments table,0754

or the Items table, respectively, and then one on the linking table that links Items to Departments.0763

One quick thing I wanted to mention is a concept known as transactions--a database transaction.0771

What it is: it is a way to link multiple queries together, as if they were to be executed as a single unit.0776

For example, in our deleteDepartment method, we run two DELETE queries.0784

The first query deletes the rows from our depts_items linking table.0791

And the second query deletes a row from the Departments table.0797

Now, what happens if we were to run the first query, and so we have deleted all the items for the particular department,0801

and then something happened where the second query failed--we weren't able to delete the department from the database?0808

Well, essentially, we want to treat deleting a department as the combination of both of those queries.0814

We want both of those queries to exist, so that the database stays consistent, so that if we are deleting a department,0820

not only are we deleting all the rows in the linking table for that department, but we are also deleting the rows from the Department table.0829

Right now, we are running two separate queries in order to do that.0835

And something that happened...well, one might succeed, and the other might fail.0838

Well, the concept of a transaction is to treat both of those queries as a particular unit.0841

So, typically what happens is: it is a SQL thing--you signify the start of a transaction.0847

We are not going to get into the details of it, but you would say, "I am starting a transaction now."0857

You would run the two queries that you want to run as part of the transaction, so we would run our two DELETE statements.0860

Assuming both of those queries are successful, then you would do what is known as a commit.0865

And you commit the results, and say, "Everything is fine; I want both of these queries to stay."0871

Let's say, however, that we run the first query, but the second query causes an error.0876

We don't want the database to be in that inconsistent state, where a department exists, but it doesn't have its items in it anymore.0880

So, in order to do that, what you can do is a rollback.0890

And what that does is rolls back, or resets any queries that were run since the start of the transaction.0893

So, if the first DELETE query was successful and the second DELETE query failed, you could test for that.0901

And then, what you would say is, "I don't want this transaction to occur; I want to roll it back."0907

And it will restore the database to the state it was in before the transaction started, meaning it will erase that DELETE query that you ran.0911

It is not something that we are going to be implementing in our web application here; it is a little bit more of an advanced topic.0921

But I did want to mention it, because it is important when you are running queries that use multiple tables.0926

If you have to run multiple queries, for example, to perform what you want to be a single operation, you can use the concept of transactions.0933

MySQL has support for it; PHP has support for it.0941

And it is a little bit more of an advanced topic; we are not going to get into it,0945

but I will include, in the Quick Notes, a link to some information so,0949

if you are interested, you can find out more about how transactions work.0953

That ends today's lesson; thank you for watching Educator.com, and I look forward to seeing you next time.0957