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 UPDATE statements.0008

This version of the web application is going to be new version 18.0.0013

And what we are going to be doing is creating two new admin pages that are going to allow us0018

to update the information for both items and departments in our store.0022

We are going to create a page called updateDepartment.php for departments, and updateItem.php for updating items.0027

Each of those pages is going to make use of some new functions (or methods) in our DatabaseAccess class.0034

In particular, the updateDepartment page is going to make use of the updateDepartment function.0042

And likewise, updateItem.php is going to use the updateItem function.0046

Each of these functions are going to run UPDATE queries that are going to update a specific item or department within the database.0052

And they are going to use, as we saw before, the SET format to set the values of the different columns for a particular row.0064

And the WHERE clause is going to be used to specify a department ID or an item ID,0071

so that we are uniquely identifying the single row that we want to update, because we are going to be updating one department or one item.0078

Let's take a look at what these new functions look like.0085

In our DatabaseAccess class, if we look at updateDepartment, what this function does is: as a parameter, it takes a Department object.0089

And what it is: it is a populated Department object that has the ID and the name specified.0099

And this function is used to update a row in the Departments table.0105

We already have functions addItemToDepartment and removeItemFromDepartment,0109

for adding and removing items from a department, that update that linking table.0113

This function is specifically just for updating the row in the Departments table;0117

our Departments table only has two columns, deptID and name, so this is going to be for updating the name of the department.0123

You pass it a Department object with the ID of the department that is already set--has its properties set.0128

So, its department ID is going to be set to the department you want to update,0134

and its name is going to be set to the new value that you want to set for its name.0139

Then, what it does is creates an UPDATE statement, using the sprintf function again, to make things easier to create.0143

So, it is going to say, "Update the Departments table where the deptID equals,"0152

and then what we are going to be doing is setting deptID equal to the value that is specified in the object that was passed to the function.0156

We are going to set its name--notice, it is in quotation marks, because it is a string--0165

equal to whatever name was passed as part of that Department object to this function.0169

We go ahead and run the query; we check to make sure that one row was updated,0178

because it is a primary key identifier, so it should only update one row.0182

And if everything works out, we return true; if not, we return false.0186

The updateItem function works very similarly, except, instead of a Department object, it takes an Item object as its parameter.0191

And the same thing happens: it builds up an UPDATE statement from that Item object.0198

So, the Item object is going to specify an itemID as its property.0202

And that is going to use it to specify which item row in the Items table we want to update.0206

And then, it is going to have all the other properties of the item set: the name, price, description, and image file extension.0211

And what it is going to do is: we are going to create an UPDATE statement that is going to reset all of those values0217

to whatever values were passed in as part of this Item object.0224

Here, we are creating a rather large UPDATE statement.0228

We are saying, "Update the Items table," and we are going to set the name, price, description, and image file extension columns for a particular item.0231

And so, what we are doing is just setting all of the properties for a particular item in the table; we are setting all of the column values.0241

Even though maybe we have only updated the name or the price,0251

it is a little bit easier than having logic that would have to go through and test,0254

"OK, has this item just been updated, as far as...only its name was updated? Was its price updated?"0257

"Was its description updated, and its name?"0264

So instead, we just go ahead and update all of them at the same time to make it a simpler update statement.0266

We are going to update it for the row where itemID equals the ID of the item that we passed in, so that is going to signify one row.0272

And then, we use sprintf to generate the query, and we pass it all of this information0280

that is encapsulated in that Item object that is passed to the function as its parameter.0285

We go ahead and re-run the query, test to make sure that one row was updated, and then return true on success (false on error).0291

If we look at what the new administrator site looks like, we can see that we have two new links.0299

We have an Update Item link and an Update Department link.0307

And if, for example, we click on Update Department, we can select which department we want to update.0309

Let's say we want to update the Apparel department.0315

And if we select that, let's say we want to change it to a Men's Apparel department;0317

we go ahead and...actually, just so you know, if we look at the store, we can see that the current store has three departments:0323

Apparel, Electronics, and Sporting Goods--our usual three; after we run this UPDATE statement,0329

we are now going to have department 3 updated to Men's Apparel.0334

It is going to let us know it was successfully updated.0338

Now, when we go back to our store, we can see that this first department, our department 1, is equal to Men's Apparel.0340

And it contains all of the same items that were in the Apparel department, because the depts_items table wasn't changed at all.0346

We only made an update to the Departments table.0354

Similarly, for our updateItem script, first, we were presented with a dropdown box to select an item that we want to update--for example, item 1002.0357

And then, it is going to present us with text boxes to update the name, price, and description.0372

Now, I haven't included here a way to update the image, because it adds a lot of complexity to processing this,0378

because we have to extract information from the image, such as the file extension.0384

And also, you would have to test if any file has been provided.0392

If not, you have to have code to say, "OK, let's keep the old image."0396

And then, you also have to test if there was an new image that is provided, and if so, you have to move the uploaded image.0402

It just adds a lot of complexity to the code that doesn't really illustrate what we are trying to illustrate in this example, which is how to use the UPDATE function.0407

I have eliminated that from the updateItem function, and instead, in this form, we have text boxes for the name, the price, and the description.0415

And then, instead of having a file selector to choose an image file, I have just gone ahead and included,0429

as a hidden field, the image file extension that is already part of this object that is already set.0436

This isn't allowing us to change the image.0442

Now, ideally, you would set it up so that you could do that.0445

But again, it just adds a lot of complexity to the code that is going to obscure what we are trying to learn in this lesson.0447

So, I have just included, as a hidden field, the image file extension.0452

Also, I have included the item ID as a hidden field.0458

So, what is going to happen is: when the user submits this form, it is going to submit any changes we have made to name, price, and description.0462

It is also going to submit the item ID, and it is also going to submit the image file extension.0470

So, what is going to happen is: the part of the form that processes updating of an item is going to build up an Item object0474

from all of that information, and then run that updateItem function that we learned about.0480

And it is going to pass it that Item object that we have built up.0485

For example, if we look at our updateItem script, it has a couple of different sections in it.0488

For example, when we first are presented with the form, we have a dropdown box to select an item.0494

And when we select an item, it passes an action variable back to the form, that says showItem.0499

And so, what we are doing is loading the item from the database.0506

And what that allows us to do is output all of this information you are seeing here.0509

It is going to allow us to output the name, the price, the description, and then also those hidden fields: the itemID and the image file extension.0514

And then, when we click on Update Item (let's say we have made some changes),0524

then we enter the different section here, based on the action form variable set to updateItem.0527

And what we are doing is: we are going to be creating a new Item object, and we are going to be creating it from that data that was supplied on that form.0533

It was supplied...if we look at the beginning of the form here, we have an updatedItem POST variable.0540

And if we look at the source again, we can see: that is the name we have given to all of our input form variables.0548

They are arrays called updatedItem, and then the key of the array is the name of the variable that we are trying to update for the particular item.0557

In this processing section, we are accessing that updatedItem array that was passed as a POST variable.0569

And we are creating a new item, based on the itemID that was submitted by a hidden field, the name, the price, and the description0575

that were supplied on the form, and then the image file extension that was also provided by a hidden field.0581

Now, if you notice, we have added the method addslashes here for both the name and the description,0586

because those are text data that the user could put, for example, single quotes or double quotes within,0592

that might cause problems in our SQL query.0598

So, we are going to run the addslashes method on them, so that they are properly escaped,0601

so that in the step when we do call the updateItem function, and we pass it this Item object to update,0605

it will be able to successfully generate the query.0612

As long as no error happens, then it goes ahead and outputs a message that says that everything was updated OK.0614

For example, to show you how the addslashes method is going to work,0620

I am going to show that we can add single quotes, for example, to the name of our particular item.0628

So, we are going to update it to 42" LCD Television; we are going to have the name be surrounded in single quotes.0635

Maybe we will drop the price to $400; and then, let's go ahead and update the item.0641

And it should say that the item was successfully updated.0648

Now, if we look at our Item, we can see that it is called '42" LCD Television' with the quotation marks successfully around there,0653

which means that they were successfully escaped in the query.0661

And then, we can also see that the price was updated.0664

And so, that is how the updateItem form and the updateDepartment form work.0667

And just again, to note, we just saw how we made use of the addslashes to properly escape any data that was submitted by the user.0673

So, for example, the name of the department, and then the name and description of an item--we run the addslashes method on them.0684

In addition, when we are outputting, after we, for example, select an item to update,0692

we are outputting to the user the current information about that particular item.0702

Well, we need to properly escape this data.0708

So, for example, I should have picked item 1002: now, that has a double quotation mark in it,0710

which is a special character in HTML, so it needs to be properly escaped.0720

So, when we select that, it will properly output it here without causing any problems with the interpretation of the HTML.0723

So, we are making use of the htmlspecialchars function on the part of the UPDATE pages where we are showing the user what the current values are.0731

And so, that labels them to be output appropriately, and the HTML rendered appropriately.0742

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