Manually Deleting items from a SQL Database
Most script writers who write successful scripts that interact with databases do an excellent job of screening the data that is written to the database. The scripts won't become popular if they are prone to cracker attacks.
Most also anticipate and protect site owners from the more common "stupid user" mistakes. Unfortunately it is almost impossible to provide 100% protection from stupid users. There are just too many people in much too big of hurry submitting stuff to sites around the web for any practical method to ever be successful.
The net result is that sooner or later you may have to edit data in a database to remove one or more offending entries that are creating an erroneous display or causing a script fault.
There are two courses of action that one can use when this happens. You can manually delete the offending entry from the database or you can edit the data in the tables to correct the problem. Deletion is far faster than editing and is the subject of this article.
If you have never done it before, manually opening the database and deleting a row or rows of data is a very scary process. The objective here is to make the process as simple as possible. Always CYA is the first rule of any computer related project whether you have done it before or not.
Back up the existing database
The very first step is to make a back-up copy of the existing database before you start editing the tables.
Our How to Back-up a Database article will show you how to backup a database. Please don't skip this step.
There is no way for a generic article like this to help you conclusively identify which table or items you need to delete. Those choices are dictated by the database in use and the problem you are experiencing. You need to select the table where you need to delete items. In this example we know we have some mal-formed entries in the table of links and that we are going to delete from that table.
When we click on the database name link on the left nav panel we get a screen that shows all the tables in the database in the main content portion. Under the action column you will see six icons. From left to right they are: browse, see structure, search, insert, empty and drop. A big word of caution here; empty and drop are not the same thing and both should be used with extreme caution. Empty will erase all the data from a table and drop will delete the table. We will not be using either of those options today.
We are assuming you are new at this so we are going to do this the slow hard way using browse first even though search will speed up the process greatly. Click on the browse icon and you will get a display of all the records in the table sorted by the primary key for the table in ascending order with a display of 30 items per page (which is a good number on shared hosting). In this example phpMyAdmin tells us we are viewing records 0-29 of 24955 total records. The dropdown box on the right hand side will allow us to jump to any of the 832 pages (30 items * 832 = 24, 955 +-).
Now you simply go through and find the offending item, click on the pencil icon to edit it or click on the red X icon to delete the item. The check boxes and the " with all do" section at the bottom of the page can handle multiple items.
Search is faster.
In this example we have a field named approved which is not shown in the screenshot above as it is far to the right. The allowed values for that field are yes and no. Let's assume we want to find only the items that are not approved so that we can delete them from the database. Click on that old search icon at the top of the page, select the operator = for the approved category, enter the value no and click GO.
Only the 23,672 records of the total 24,955 which have the value of no for approved will be shown when this search is completed. They can be reviewed, edited or deleted item by item or page by page.
Now you know how to go into a database, find and correct or delete unwanted or corrupt data. If everything is working fine you are done and if not you have your back-up. You did make the back-up?
Right now! Before you forget!
Delete that temporary back-up and make a new one.