Refresh
this page because I am probably still making changes
to it.
In an earlier lesson you learned how to do
INSERT queries and UPDATE queries.
An DELETE query can be used to remove one or more existing records. Running a DELETE
query in a PHP program is almost exactly the same as an INSERT/UPDATE query: the only
thing that's different is the SQL!
Pre-Requisites
Make sure you've completed the lessons on CRUD Operations - Create,
Read, and Update
before doing this lesson.
Deleting or removing records involves a DELETE query,
which has the following syntax
DELETE FROM TableName WHERE columnName=value;
This would delete all data from TableName for records
whose columnName was set to value. For example, to delete all
of the records in your Music table that are by
the artist "Justin Bieber", you'd use the statement:
DELETE FROM Music WHERE artist='Justin Bieber';
Note that the above query will only delete records
where the artist field exactly matches 'Justin Bieber'.
It won't delete records where the artist field
contains the text "Justin Bieber" with other text before/after.
For example, a record with an artist value of To do that, you'd
"Justin Bieber is Baloney" will not be deleted with
the DELETE query above. If you wanted to delete a record
that contains the text "Justin Bieber" with or without other text, you'd
have to use the LIKE operator that you learned in
the lesson on SELECT statements:
DELETE FROM Music WHERE artist LIKE '%Justin Bieber%';
The query above will delete all records where the text
"Justin Bieber" appears in the artist field, so that
would include records where the artist value is equal
to "Justin Bieber", "Justin Bieber is Baloney",
and "Quiet Justin Bieber".
If there is no record that matches the WHERE clause,
or any other error occurs while attempting
to perform the update query, the "rows affected" will be 0.
Practice Delete Queries: use the SQL window to remove
some existing records with DELETE queries. Back up a copy of your
records first, so you can restore them when you're done!
Delete Queries in PHP
Again, you can prepare and execute a DELETE query the same way you would an UPDATE
or INSERT query.
<?php
$sql = "DELETE FROM Inventory WHERE ?=1"; // only one parameter, might as well use unnamed
$statement = $dbConn->prepare($sql);
$params = [$id];
$result = $statement->execute($params);
if ($dbConn->rowCount()) {
// success
}
?>
Exercise
In the a previous lesson you
created a page of Course records with radio buttons. This time
create one with checkboxes. The user will select one or more
records to delete when they press the submit button.
The form can process itself: after submit, perform the delete query
for each record (tip: you can delete several records at once with
one delete query using the IN operator, e.g. DELETE FROM tableName
WHERE IN(id1, id2, id3, .... );). The number of records deleted
should appear above the form.