Advanced PHP SQL Command: DELETE

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 called DELETE,0005

which is going to allow us to delete rows from tables in our database.0009

We are going to learn about the syntax of the DELETE statement.0014

And then, we are going to learn about how to use our DELETE statement from PHP.0017

So, one part is going to be learning the SQL part of it; and then, how to transform that, or move that, into PHP code.0022

As mentioned, a DELETE statement is a SQL command that is used to just delete rows from a table, based on specified criteria.0032

We have learned how to insert rows into a table; we have learned how to retrieve rows from a table;0041

now, we are going to learn how to delete them.0044

It has the syntax shown here: it starts with the keywords DELETE FROM, and then you specify the name of the table that you want to delete rows from.0046

And then, like our SELECT statement, it has a WHERE clause.0056

And that is how you define which rows you want to delete.0060

This statement here is saying, "Delete all the rows from the Items table where the itemID equals 1001."0064

Well, we know from our Items table that our itemID is a primary key.0071

So, there is only going to be one row that is going to be deleted here; so this is going to delete one row from the table, where the itemID is 1001.0075

Now, the WHERE clause operates just like it does for SELECT statements for selecting rows from the table.0083

in that you can use comparison operators (as we did here--we tested if they are equal) and you can use logical operators.0089

For example, we used AND and OR when demonstrating WHERE conditions when we talked about SELECT statements.0096

You can use all of those same operators and operations in order to define which rows you want to delete from a particular table.0105

If we go and (I have MySQL Monitor loaded up) look at all of the items in our table0113

(is that an error in the syntax?)--I have just pulled up the ID, name, and price for all of the item rows in our Items table;0124

we can see that we have the default setup for our database, which is our 6 items.0134

And so, if we want to delete item 1001, let's go ahead and run that DELETE command we just saw.0139

So, we write DELETE FROM, the table we want to delete from (which is Items), WHERE itemID equals 1001.0145

And if we run this, we are going to get query OK.0159

We notice that it is going to say one row is affected; we know that that was a primary key we specified, so it is only going to signify one row.0162

If we run the SELECT query on our Items table again, we are going to see now that item 1001 is no longer in the database.0167

One other thing to note is that, if you want to delete all of the items, or all of the rows, from the table,0178

you can simply just omit the WHERE clause, and you would just say DELETE FROM Items.0183

In this particular case, that is going to delete every row from the Items table.0188

So, if we were to go ahead and run that command, it should tell us that 5 rows have been deleted.0192

It is going to say 5 rows were affected.0200

If we run our SELECT command again, it is going to produce an empty set, because no rows were returned.0202

I'm going to go ahead and repopulate all of the tables, to get them back to their default state.0208

And now, just to verify that the data is back in the table (because we are going to be using it in our next slide)...0229

We can see that all of the data is back.0241

So now, we are going to talk about how to run DELETE commands from PHP.0245

Running a DELETE query works in the same way that we ran an INSERT query.0251

And the reason for that is that, when you run a DELETE query, it doesn't return a result set.0255

We saw that there was a difference between our INSERT query and running a SELECT query from PHP,0260

because the INSERT query...you could get the number of affected rows, and you could get the insert_id that was generated.0265

And that was the information you got after running the query.0272

On a SELECT query, we would actually get a result set object returned that we could loop over and extract information from all the different rows.0277

Well, DELETE doesn't return any data; all it does is--we can find out whether the query was successful,0285

and then also how many rows were affected.0290

One thing is that the query method...let's say we were to include a DELETE statement within the query method,0295

which, again, is a method that is part of our MySQLi object (so this is assuming we have already opened a connection to the database;0304

now we are going to run a DELETE query); it is going to return true or false, to let you know whether the DELETE query was successful.0311

That is one way that you can test.0319

Additionally, we learned, on the INSERT statement, about the property of this MySQLi class,0321

which is a class that we instantiate to connect to a MySQL database.0329

It has a property, affected_rows, which tells you the number of rows that were affected by the last operation that occurred.0332

So, we can access this after we run a DELETE statement to see how many rows were deleted.0338

For example, assuming our database variable is an instance of a MySQLi object, what we can do is:0343

we can create a query string that is going to say, "Delete all the rows from Items," for example, which is a query that we just ran.0359

What we can do is then call on this MySQLi object; we can run the query method and pass it the DELETE query,0366

and then, if we wanted to, we could test the result to see whether it was true or false, to see whether the query succeeded.0372

And then, what we are going to do here is: we are also going to output the number of rows that were deleted.0377

And we can do that by accessing, from this MySQLi object, its property affected_rows,0381

which is going to tell you the number of rows that were affected by the last operation,0388

which, assuming we run this in sequential order like this, would be this DELETE FROM Items operation.0392

Let's take a look at how this looks in actual PHP script.0398

We have a script here called deleteItem.php, and it follows the four-step...0402

The other thing to mention about using a DELETE query is: because we are running a MySQL query,0407

we are going to follow the same steps that we normally do for running a MySQL query.0410

We performed step 1, which is opening a connection to the MySQL database.0415

And this is actually the same code that we used in some of our other lecture examples.0419

We just connect to our advanced_php database as our phpuser; we test for a connection error.0423

Then, here is where we run our SQL statement.0430

In this case, we are going to be deleting from Items where itemID equals 1001, so we are just going to be deleting that first item in our database.0433

We are going to run the query, and then what we are going to do is test if the number of affected rows was 1.0441

We know that that is a primary key; it should only delete one row from the table.0447

So, if affected_rows is not equal to 1, it means an error has occurred; we can output an error message.0450

Otherwise, we are going to output, "The number of items deleted was," and in this case,0456

we are going to access the affected_rows property, even though we know it is 1.0459

And that is just going to demonstrate how to use that affected_rows property, again, from our MySQLi object.0463

After all that is done, we close things up, and we call the close method on our MySQLi object.0468

If we look at our current state of our database (again, I will just run a SELECT query),0476

you can see that we have all 6 default items in our database, including item 1001.0480

Well, if we go to our lecture example and run this script deleteItem.php, we can see a message that says, "The number of items deleted was 1."0487

If we go back and we run a SELECT query on the table again, we can see that item 1001 is no longer in the table.0499

That is how you run a DELETE query from PHP.0508

So again, you do the four basic steps for connecting to and running a query from a MySQL database.0513

The DELETE query is similar to running an INSERT query, in that you don't have a result set object that you process.0519

You can test for the number of affected rows, and then test whether it was successful or not.0525

For the homework challenge, I am going to have you do something similar.0535

I am going to have you create a script called deletePerson.php that is going to accept a GET query parameter called personID0537

that is going to be set to the ID of a person in the People table.0545

And we have created this People table in previous homework challenges, so if you don't have that set up,0550

you are going to have to have it set up, and then also populate it with some data.0555

We know that personID is the primary key in that table.0561

What we are going to do: an example URL for how to use this script would be deletePerson.php.0565

And then, you provide the query string, which is the ID of the person that you want to delete from the People table.0570

So then, what you are going to do is have your script extract the personID from this GET query string,0579

and then build up a DELETE statement that is going to appropriately delete that corresponding person from the People table.0584

That means that you are going to have to make use of a WHERE clause to specifically specify the row that you want to delete,0591

which is going to refer to this particular person.0597

Go ahead and run the DELETE query and output the number of rows that were affected, using the affected_rows property of our MySQLi object.0600

And it should be 1, because we have one row--a primary key row--we are deleting.0610

And then, just remember to follow the four basic steps for accessing a MySQL database from PHP:0615

opening the connection, running your DELETE query, testing the number of affected rows, and then closing the database connection.0620

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