PHP Fundamentals: Edit and delete data from a database
In the last article, I showed how you to build a web form that would allow you to add data to a database, thus freeing you of having to use phpMyAdmin for the task. However, there will of course be times when you'll want to edit that data, or even delete it.
Today's lesson in PHP Fundamentals is twofold. I'll show you how to build a simple PHP page that will allow you to delete entries or serve as an access point to edit data, and how to build a form similar to the add form that will allow you to pull data and edit it to your desire.
Of course, like the other articles in this series, I've included files for you to use and learn from. Click here or scroll to the bottom to download them.
Pulling data with query strings
The way I have things set up here is how I like to do it. Always remember nothing is set in stone or "must" be done this way. So if you ever happen to come up with a better way, please go with it, and even share it with me so I can learn new things.
Take a look at the adminForm.php file. This is how I usually handle edit and deletion tasks. The page simply pulls the entries from the database and serves up a series of entries with the textInput data as the "representation" of an entry. In many ways, imagine this as the titles of blogs, headlines for news, or names of people...depending on what you choose to make.
The entries are all set up to be links that lead to another file, editForm.php. I'll go into that file later, but take note how I have a query string containing the ID number I use as a primary key in my database table.
If you've never seen a query string before, then here's your introduction. I'm sure you've seen them plenty of times all over the internet. It's how we can pass small amounts of data into a page through a link. This is also how you'll see data fed to a page from a form that uses the GET method (as opposed to POST).
I used the number 1 as an example, but the entries will variate based on anything you desire to use as a means to later pull the data. Let's say you were using a date and a title instead, your link might look like this, with ampersands separating the data points:
Deleting data entries
Now we move on to actually deleting. From the front end, you would click on any checkboxes of entries you wish gone, then press the "Delete Entries" button.
In the back end, the ID numbers of all the entries are sent to the PHP script. You might notice I set up that entry display with a variable $counter so you get a separation of entries. I used concatenation to create the individual variables attached to each entry.
When the data is sent through the script that processes the deletion, a for loop is used with the final number of $counter (as $rowCount) as the limit on the loop. $delVar and $idVar serve as temporary placeholders for when I create each of the individual variables again so I can pull them from the POST method. The reason why I had to do this was because I could not put a concatenated variable in the $_POST function that pulls the data.
So I'll illustrate what happens. The temporary placeholders are created:
$i = 1
$delVar = "del" . $i
$delVar = del1
Then the $_POST function is fed this temporary variable:
$delete = $_POST[delVar]
$delete = $_POST[del1]
$delete = "yes" (or a NULL value if the box was not checked)
At this point, a simple if statement will tell us either to delete the entry or not. This statement is setup to run through every entry of the for loop it is in. So any entries not checked simply won't have anything happen. Any that have been will be placed into the MySQL statement and run into the database:
DELETE FROM phpDemo WHERE id='$id'
Plain and simple.
Editing data entries
Open the file editForm.php. When you give it a first examination, you'll see much similarity with this file and the addForm.php file used in the other article. The first major difference is the added PHP code used to pull the data.
This is really no different than when we pulled data and displayed it on a page, but now you're placing that data into a copy of the form used on the add form. Just like how we use the $_POST function to pull data for the form processing, we start the form area with the $_GET function to pull the ID number. Bear in mind if you use multiple pieces of data in a query string, you would need a separate line with a $_GET function for each piece of data you want pulled.
You'll also notice I placed in a HTML hidden field that will only contain the key, in this case the ID number. This is an important practice so when you submit this form, the processing script will know what entry is being updated in the database.
So now let's look at the processing area. You'll see that just like the add form, this one will pull each piece of data respectively using the $_POST function. Again, I used htmlspecialchars() to fix those pesky quotation marks and apostrophes from two of the pieces of data. The major difference now is in the MySQL statement used:
$Result = mysql_query("UPDATE dbTableName SET dbVar01='$phpVar01', dbVar02='$phpVar02', dbVar03='$phpVar03' WHERE id='$id'");
Now you'll see why I had to pass the ID number as a piece of data. It's necessary so you can identify which piece of data is being altered. You'll also notice the difference between this statement and the INSERT statement used in the previous article. Rather than separating all the column names from the data, you instead now make a long statement with each column name corresponding to its new data.
Now you don't have to put every single column in this. If you wanted to make three forms to change three parts of a table, then you would simply build the form and MySQL statement to only alter those pieces of data.
I know this article is a lot to take in, but if you mastered pulling and displaying data, and then adding data, you'll get this. Down the road I'll post an entry on you can add the means to adminForm.php so can alter the numerical order of the entries.
Questions? Ask here in comments or drop me an email.