Advanced PHP SQL Command: UPDATE

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

In today's lesson, we are going to be learning about a new SQL command known as the UPDATE command.0005

We are going to be going over what an UPDATE command, or an update statement, looks like,0011

which is used, as the name implies, to update information in the database.0015

What it does is updates information for a particular set of rows in the database.0019

We are going to talk about how to run an UPDATE query from PHP script.0026

And then, we are going to go over two built-in functions in PHP: htmlspecialchars and addslashes,0031

which are two functions that are used to be able to effectively retrieve information from the database,0040

to output it, and then also to be able to appropriately add information to a database.0047

As mentioned, an UPDATE statement is used to update data values for specific rows in a table in the database.0056

It has the following syntax here: it is the keyword UPDATE, followed by the name of the table that you are trying to update rows on.0065

This is saying, "Update rows in the Items table."0074

And then, it has the SET keyword; and then, what you do is have a comma-separated list of column names with the values you want to set them to.0077

For example, this is saying, "Update the rows in the Items table, and set the column name to have the value Blue T-shirt,0087

and set the price value equal to $8.99, for any row WHERE (and again, it makes use of a WHERE clause) itemID equals 1001."0100

What this entire query here is saying is, "Update the item 1001 row in the Items table, so that its name is Blue T-shirt, and its price is $8.99."0111

Again, just to point out, notice that our strings are enclosed in single quotation marks.0123

The WHERE clause operates just like for SELECT statements, and also as we have done in our DELETE statements.0127

So, it can use comparison and logical operators.0136

So, you can actually update multiple rows at the same time in a database.0140

For example, maybe instead of saying "where itemID equals 1001," you could say, using a comparison operator, "where itemID is less than 1004."0144

Now, this may not be something you want to do; but just as an example, this would say,0156

"Update all the rows in the Items table; set their name value equal to Blue T-Shirt; set the price equal to $8.990159

for all of the items where the itemID is less than 1004," so you would update multiple rows.0167

If you want to update column values for all of the rows in a table--for example,0172

let's say we have switched over our website so all of our item image files are jpg, and they have a jpg extension:0177

well, you could run this UPDATE statement here, and you just leave out the WHERE clause.0187

And that is going to update every row in the table.0191

So, this is going to say, "Update all of the rows in Items, and set the column value imageFileExt for each row equal to the string jpg."0193

And you notice that there is no WHERE statement here; and what that is going to do is make that UPDATE statement apply to all the rows in the table.0203

Let's take a look at...I have logged into our MySQL Monitor, so we can play with the database.0214

And if we take a look at what we have in our Items table, for example: this is the information we have about the particular items.0221

Let's say we want to update our Baseball Bat row to just be a baseball; let's say the item represents a baseball now.0235

Well, what we can do is say, "Update the Items table; we want to set the name column equal to just Baseball."0244

"And we want to do that for all rows where the item ID equals 1003."0253

Because that item ID is a primary key, we know that this UPDATE statement is only going to apply to one row.0261

And when we run it, it is going to say, "Query was OK; one row was affected; one row was matched; one row was changed."0266

If we run our SELECT query again, we can see that now, item 1003 is called Baseball.0274

One thing to note is: let's say we try to run the same query again, where we are trying to update the name of it.0280

It is going to say that one row matched: it found one row that matched this WHERE clause.0287

But it wasn't changed, because nothing changed in it, so it is going to say "0 rows affected."0291

And that is going to come up when we talk about how to use these UPDATE statements from PHP,0296

because we use the affected_rows property of our MySQLi object to sometimes determine whether a query was successful or not.0302

So, even though a query may be successful, in that it assures that the data is updated to how you want it,0309

it may not affect any rows if the data actually did not change.0317

The way we run an UPDATE query from PHP is the same as we do for our INSERT and DELETE queries.0323

They don't return a result set; we can test the return value of query--it is a truth value to see whether it was successful or not.0330

It returns true if the update was successful, false if it wasn't.0338

We can also check the affected_rows property of our MySQLi object, and what that allows us to do is to see the number of rows that were updated.0343

Now, as mentioned, the first time you run the query, if you are changing information, it will let you know the number of rows that were changed.0353

If you run it again, affected_rows is going to say 0 if you update the same data.0358

So, that is something you have to consider when you decide how to test if an update was successful or not.0363

Assuming we already have a connection to our database, it has the db variable, it is a MySQLi object, and0373

it is already connected to our advanced_php database: if we have this query here, UPDATE Items, SET price equal to 15,0378

there is no WHERE clause on this query, so this is going to update all of the rows in our Items table.0385

And it is going to say, "Set the price of every item in the table to 15."0389

We are going to run the query; this Result variable can be tested for true or false, to see whether the update succeeded or not.0393

And then, we can also output the number of rows that were affected by the query.0402

In this case, if we had 6 items in our table, affected_rows would say 6 the first time it was run.0407

The second time it was run, it would report 0.0411

Let's go take a look at a script we have created that shows how to use this in PHP.0415

It is just called updateItem.php: it follows the four...as with any SQL queries, you have the four steps that you go through when running a MySQL query.0421

You connect to the database; you run the query, process the results, and close the connection.0433

We have, in the beginning of this script, our connection to the database.0438

Then, we run our SQL statement; in this case, we are running a static statement that is just saying,0441

"Update the Items table; set the price equal to $0 where the itemID equals 1001."0447

So basically, we are saying, "Update item 1001 so that its price is 0."0454

We run the query; we test that only one row was updated, because the WHERE clause specifies a primary key value.0460

We know it should only update one row.0468

The first time we run this, if the query was successful, it is going to return that 1 row is affected.0470

And then, we are going to be able to output, "The number of rows affected was..."0476

Now, if we run it a second time, as you will see, it is going to show that may be considered an error--0479

"There was an error updating the item"--and that is because it was trying0486

to update the same information again, and it doesn't affect any of the rows.0490

Then, we simply close our database connection.0493

If we look at the state of our Items table again, we can see that item 1001 currently has a price of $15.0496

If we go ahead and run this first example, it is going to run that query.0505

We can see that the number of rows updated was 1; it lets us know that the query was successful.0509

If we go and look at our Items table again, we can see now that the price of the item 1001 has been set to 0, so the UPDATE query was successful.0514

If we try to refresh the page, which is going to try to run that query again, we are going to get that error message, "There was an error updating the item."0522

And so, that is not necessarily an error, but it is because no rows were affected, and we were testing on the affected_rows property.0528

So, that is just something you need to consider.0535

And if we look at our Items table, nothing has changed; so that is why it shows up as 0 rows affected.0537

One of the things PHP provides is a special function called htmlspecialchars.0546

And what that does is encodes any characters that have special meaning in HTML into the proper HTML entities.0553

For example, if we have data stored in our database that contains a double quote or a less than sign,0564

or a greater than sign or an and sign, those are special characters in HTML.0570

If we were to extract the data--let's say, load the information about an item from the database and output it on a page,0575

if it contains a quote character, that might affect how our HTML is going to look.0584

For example, let's say we have a text box, and we want to set the value of it.0589

And then, what we might have here would be a PHP statement (let's just say it was down here)0601

that is going to echo, for example, the name variable.0607

Let's say the name variable contains a double quote.0612

Well, what is going to happen is: then, what we are going to get output from PHP (this is leaving out the rest of the attributes,0621

just so it is less to write)--let's say name was equal to...the name "Joe" in double quotation marks.0629

What is going to happen is: it is going to output the first double quote, because that is just part of what is included as our HTML.0654

When it echoes the name, it is going to output the name string, which is going to be "Joe" enclosed in double quotes.0662

So, it is just going to output this; we are going to have the ending quotation mark here, and then the closing script.0667

So, what is going to happen is: when this gets interpreted by your browser, when it gets sent to your browser,0676

it is going to see that the value input actually has no value supplied for it,0680

because this quotation mark that was included as part of the string we pulled from the database contains a quotation mark.0686

So, that is a special character in HTML, and then it closes attributes; so, that is going to mess up how our HTML is interpreted.0693

The same thing, for example, if we had a less than or greater than sign: if that was part of our code--0701

for example, maybe it was included in here--that might cause problems with HTML thinking, "OK, this input tag has been closed."0704

What HTML has, for those of you that don't know: they have special entities that are called,0712

which are these little sequences, like this, that you can use0717

to represent those characters without them being interpreted as HTML characters.0722

For example, if you wanted to output "Joe" as an actual value, we could do it like this.0729

What that is going to do is: when your browser interprets this HTML, it knows how to interpret these HTML entities.0757

And it knows that, when it encounters & , followed by quot, followed by a semicolon,0763

that it should just output a double quotation character, and it is not meant to be the character that is closing the attribute value.0769

If your string data contains these types of characters, you are going to want to escape them0778

or turn them into these HTML entities, so that they are properly interpreted.0785

And so, the htmlspecialchars function does that.0790

You provide it with a string--maybe it is a string you have read from your database (or that is what we are going to be using it for).0793

And what it does is takes that string, and if it finds any quotation marks in it, or any less than signs,0799

or greater than signs, or ampersand signs, it goes ahead and translates them into these appropriate entities and generates that string.0804

You run this as a filter around data you are pulling from your database, before you output it by an echo statement to your HTML script.0815

If we look at a new version of our updateItem.php, and if we take a look at the script,0822

it is a little bit different, in that it is going to make use of our Item class that we have been using in our database.0829

And the first thing it is going to do is allow us to choose an item to update.0837

And we do that by entering an item ID here.0841

So, it kind of has three different parts to it.0843

I might select item 1002, and we are going to say "show item."0846

And what that is going to do is: in this particular case, we are just going to be updating the name,0850

just for this example, rather than all the other properties of the item.0854

And it is going to output the name in this text box that shows what the current name of our item is, because we want to update that.0857

And so, we want to know what it is, and maybe we want to change it.0867

Let's say 42" LCD...it's kind of implied...Color Television; and we go to update the item.0870

It is going to update that name of that item in our database.0879

So, this script has three different parts to it: it has one where you select the item;0882

it has another one where it outputs what the current value of the name of the item is;0885

and then, it allows you to try and update the item in the database.0891

Well, if we look at our script here, it has three parts based on an action variable that we are going to be using,0897

that is going to be either null, showItem, or updateItem, based on which part of the script we are running through.0903

In our data processing section, we go ahead and connect to the database.0911

If the action is showItem, then what we do is run a SELECT query on the database to load the information about the particular item.0915

In this particular case, we are interested in the name of the item.0924

But we go ahead and build up an Item object that contains all of that information.0926

And then, in our section down here, where it says showItem, we go ahead and are outputting the information about the particular item.0931

Now, the thing that we are going to be doing is: you will notice that, in our text box,0944

we want to output the current value of the name of the item, so that, when we go to the script, we can see what it currently looks like.0949

So, what we have done is: in our text box, in the value attribute,0960

we have outputted an echo statement that outputs the name of the item.0965

And it accesses this Item object that we built up at the beginning of the script.0970

And it runs it through this htmlspecialchars function that is going to escape any special characters, so that we don't run into any problems.0975

One other thing to note is that it also passes, along here, the itemID, because what is going to happen is:0986

when we submit this form, we are going to want to be updating the name of that particular item in our table.0992

We need to be able to identify which item we want to update.0996

We do that by the primary key, which is the item ID.0999

So, we output here a hidden input field, the item ID that we are going to update.1002

When it gets re-submitted with the UPDATE Item, after we have changed the name, and we hit Update Item,1007

it is going to get re-submitted with an action that is going to be called updateItem.1013

So, in our processing section, then what it is going to do is create a query that is going to update the name of that particular item.1016

And that is where we get...this is Process form variables up here,1023

where it processes GET variables...itemID and name, for the name and itemID of our item.1028

And what it does is says, "Set the name of our particular item in the Items table equal to" whatever the name was set to, "where itemID equals itemID."1034

So, that is saying, "Update the name and column of the item row represented by this item ID,1044

and then, if there is no error, it goes ahead and goes down to the Output section, which updates a message1054

that says, "The item was updated successfully," as we saw.1060

If we look at the current status of our database, we can see we have a 42" Color Television, $599.99.1064

Well, let's say we wanted to update the name of that.1075

Actually, let's say, right now, if we look at it...let's take a look at what that script looks like...1077

if we go back and select item 1002 and click Show Item, this has a special character in it: it has a quotation mark.1084

So, that is going to get escaped, or turned into an entity, by our htmlspecialchars.1090

If we look at the HTML, we can see: here is that &, quot, semicolon that is representing that quotation mark1095

that is letting your browser know that it is not a quotation mark to close the value attribute,1103

but it is just a quotation mark to be output as is.1109

And so, that is how the htmlspecialchars works.1114

Well, let's say we were to go and get rid of that htmlspecialchars function call.1117

Now, if we try to load item 1002, and we click Show Item, you are going to see that all it shows is 42.1127

And why is that? Well, if we look at the source, and we blow it up, we can see that this quotation mark,1134

this double quote, was not turned into an entity.1141

So, HTML interpreted it as the closing quotation mark for this value attribute.1145

So, it sees the value of this input box as just being 42.1151

And then, it figures out how it is going to interpret the rest of this malformed HTML.1155

And so, that is why it shows only 42 in the box on our screen.1160

So, that is the importance of where htmlspecialchars comes in.1165

If we go ahead and redo the change, we are going to see that now, the quotation mark is going to show back up.1169

I think I might have just set it to 42; let me check.1193

Yes, that is why: it actually got set to just 42.1195

Let's reset it to LCD Television; if we update it, now, when we look back in our database, we can see that it is back to the way it was,1204

because we have updated and re-added that htmlspecialchars function.1217

When we go ahead and try to show item 1002, and it outputs a value, it is going to successfully output that quotation mark.1224

And we can see that it was turned into that HTML entity, &, quot, semicolon.1233

That is a function for taking information from a database and outputting it to a user, so that it is appropriately seen within HTML.1241

Now, we are going to talk about a function that is built into PHP, called addslashes, which is used1248

when you are taking user input from an HTML form, for example, and you are going to be including that1252

as part of a query to insert or update information in the database.1258

Well, as we know, with our strings, when we insert strings into the database--for example, if we had this INSERT INTO function--1262

let's say we have this test table, for example, and it takes one value.1271

Well, if want to insert the string "can't," we know that, for string data types, we have to enclose them in single quotes.1275

And if we want to include, as part of that string, a single quote, we have to escape it using the backslash, single quote character.1284

Well, that is what the addslashes function does.1291

Let's say we have a form--the same form where we were able to update the name of a particular item;1292

and let's say the user inputs that they want to have the name of the item include some data that is enclosed in single quotation marks.1299

Well, we want those single quotes stored as part of that string in the database.1310

However, when we create our query, we need to escape those, because in our query that we typically generate1314

within, for example, our PHP code, we just enclose it in single quotes.1319

Now, if this data gets inserted as is, it is going to show up like this, and that is going to cause a problem with our SQL query,1324

because now we have a malformed SQL query, because this single quote is part of the string we are trying to insert.1330

It is telling MySQL, "OK, this value ends right here," and then this part right here is going to be malformed, and it's going to cause an error.1341

So, we need to properly escape that.1351

So, what you can do is pass your user input as a string into this addslashes function,1353

and it is going to go ahead and look for any single quotes, and then double quotes as well, and properly escape them with the backslash--1358

replace all of the single quotes with the backslash, single quote, and all of the double quotes with the backslash, double quote.1366

If we look at our new version of updateItem.php...in our old version, the one we had just looked at,1373

in the section at the beginning, where we run the UPDATE query, in the data processing section (let me find it),1389

what we are doing is creating this UPDATE query, and we are just taking this name variable1407

(which is just the GET variable name that was supplied on the form, so it was whatever was supplied in this field right here,1412

which could include single quotes or double quotes), and we just output it as is into this query, without properly escaping it.1419

Well, in our new version, we do the same thing, except we take that name variable1430

and run it through that addslashes function, and then use that properly-escaped variable within our query string.1435

So, the functionality of the script is the same, except it has added that addslashes.1443

So, let's say we wanted to say 42", and then we wanted LCD to be in single quotation marks, then Television.1449

Oops, I'm on the wrong example.1459

Actually, let's update item 1003; I think it is a baseball bat.1469

Let's say, for whatever reason, we want our title to be enclosed in single quotes.1473

Well, first of all, let's verify right now in our database: the name associated with item 1003 is just Baseball, with no quotations around it.1477

If we want to update that, to have it in quotations, we can run our updateItem function.1491

Now, it is going to say it was successfully updated.1497

And if we look in our database, we can see that Baseball is now enclosed in single quotation marks.1499

Let's say, however, that we used the last version, that didn't have addslashes.1505

Going back to example 2, now when we try to update 1003, we are going to be able to successfully see it.1509

Just to change things up, let's put two single quotes at the beginning, with a space in between them.1521

Now, when we go to Update Item, the reason it didn't get an error was because these quotes cancel each other out.1529

Let's go back to example 3, where we can comment out the addslashes function.1540

If we go back to Item.php, item 1003, Baseball, is back to the way it was before, because that query I just ran changed it.1548

And if we go ahead and eliminate this addslashes function, now what is going to happen is:1558

when we try to...let's say we want to name it Baseball's; let's see...yes, we will just call it Baseball's with a single quote in it.1568

And when we go to Update Item, we don't have that addslashes; this isn't properly escaped--1583

it is going to cause a problem with our SQL syntax, and it is going to say there is an error with the UPDATE query.1586

If we go ahead and take a look at what that query looks like by just simply echoing it,1592

we will be able to see the problem that happened without using that addslashes method.1603

We can see that what happened here is: because our string, the new name that we included, Baseball's,1616

had a single quotation mark in it, when we tried to just include that in the query as is,1623

SQL is going to interpret this single quote that is a part of our string as the closing single quote for this name column.1630

And then, that is going to cause this to be a malformed SQL statement, and that is why it is going to throw an error.1639

When we add our addslashes back to it, what it is going to do is escape that.1644

So, if we go back to our script, re-add addslashes, and then take a look at the query that we have generated properly,1649

and we refresh, we are going to see that it was successfully updated.1661

And if we look, we can see that addslashes properly escaped that single quote character, so that the SQL statement was properly formed.1666

For the homework challenge, I want you to mimic what we did in this lecture,1677

except do it on the People database that we have been using in our homework examples.1683

I want you to create a script called updatePerson.php.1687

And it is going to follow the form that we used in the example today.1691

When you first load the form, if it doesn't have an action specified, it is going to present you with a text box1696

where you can enter the personID of the person you want to update.1701

When you submit the form, you are going to submit it with an action that says showPerson, for example.1705

And what that is going to do is: you are going to go ahead and run a SELECT query,1711

load that person from the database, and then output a name text box and an age text box1715

that allow you to update the name and age of that particular person.1721

One thing to note is that, in that name and age box, I want you to also output, as we did in our UPDATE example,1726

for example, item 1002, the current person's name and the current person's age,1734

because that is what you would expect on an UPDATE form.1746

And so, in order to do that, you are going to run a SELECT query that is going to allow you to pull that information out.1749

And then, you are going to output it as the value of those text boxes.1752

Then, when you provide an Update Person button, the person can click on that, and then it is going to go ahead1756

and run an UPDATE query that is going to update the name and age of the person to whatever the user entered on the form.1763

And so, the form is going to take a couple of different variables.1771

It is going to take an action variable, which is going to control whether you are initially just putting a text box1776

to say "What personID do you want us to load the person for?"; whether it is showing the person that allows you to edit it;1783

or whether it is actually performing the UPDATE action.1790

It will have a personID form variable, which is what you are going to enter to select a person to update.1794

And then, I want you to have two form variables that are going to be array form variables that are called person, and then name and age,1801

which refer correspondingly to the name and age of the person that you are trying to update.1809

And then, whenever the action form variable is not equal to showPerson or updatePerson, just output that single text box.1816

This is just an explanation of what I was saying is how to process the different parts of the form,1826

where you go from selecting the personID to the form where you can update its information,1833

and then the form where you actually click the button Update, where it is going to go and run the UPDATE query.1837

And so, when you do this, I want you to make sure you use the htmlspecialchars function.1843

You are going to need to use that on the page where you are showing the person,1849

where you are outputting those text boxes that show the current name and age of the person,1852

because maybe their name, for example, might contain a single quote.1856

And that may not be the best example for that, but it is an example of using that--of how to properly output text from a database.1860

I want you to use that on, for example, the part of the script where the action is showItem.1870

And then, on the part of the script where the action is updateItem, I want to make sure you use the addslashes method1876

to properly escape any name or age information that was submitted on the form by the user,1882

so that you don't get errors in your UPDATE statement.1889

And I would like you to, when you go ahead and update the name, for example, of a particular person--1891

go ahead and make up some name that contains a single quotation in it.1897

And that way, you can make sure that your addslashes works.1904

And try it with and without the addslashes function, and see that you get that error;1907

and then see that, when you do the addslashes, it appropriately escapes that query, and then it is able to successfully run.1910

And that should give you practice with running these UPDATE queries, and then how to use htmlspecialchars1916

to properly pull data from the database for output, and use addslashes to properly add data to the database.1924

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