Data is not static, it changes often. This chapter focuses on editing and deleting data and its supporting structures—tables and databases.
In this section we cover the various ways of editing and deleting data.
When we browse a table or view the results from a search on any single-table query, small icons appear on the left or right of each table row, as shown in the following screenshot:
The row can be edited with the pencil-shaped icon and deleted with the X-shaped icon. The exact form and location of these controls are governed by:
$cfg['PropertiesIconic'] = TRUE; $cfg['ModifyDeleteAtLeft'] = TRUE; $cfg['ModifyDeleteAtRight'] = FALSE;
We can decide whether to display them on the left side, the right side, or on both sides. The $cfg['PropertiesIconic'] parameter can have the values TRUE, FALSE, or both. TRUE displays icons as seen in the previous screenshot, FALSE displays Edit and Delete (or their translated equivalent) as links, and both displays the icon as well as the text.
The small checkbox beside each row is explained in the Multi-row edit and the Deleting many rows sections later in this chapter.
Clicking on the Edit icon or link displays the following panel, which is similar to the data entry panel (except for the lower part):
In this panel, we can change data by typing directly (or by cutting and pasting via the normal operating system mechanisms). We can also revert to the original contents using the Reset button.
By default, the lower drop-down menus are set to Save (so that we make changes to this row) and Go back to previous page (so that we can continue editing another row on the previous results page). We might want to stay on the current page after clicking on Go—in order to save and then continue editing—in which case we can choose Go back to this page. If we want to insert yet another new row after saving the current row, we just have to choose Insert another new row before saving. The Insert as new row choice—below the Save choice—is explained in the Duplicating rows of data section later in this chapter.
People who prefer to use the keyboard can use the Tab key to go to the next field. Normally, the cursor goes from left to right and from top to bottom, so it would travel into the fields in the Function column (more on this in a moment). However, to ease data navigation in phpMyAdmin, the normal order of navigation has been altered. The Tab key first goes through each field in the Value column, and then through each one in the Function column.
Another way of moving between fields is with the Ctrl + arrow keys. This method might be easier than using the Tab key when there are many fields on the screen. For this to work, the $cfg['CtrlArrowsMoving'] parameter must be set to true, which is the default value.
If the table's structure permits a NULL value inside a field, a small checkbox appears in the field's Null column. Selecting this puts a NULL value in the field. Whenever data is typed into the Value column for this field, the Null checkbox is cleared automatically. (This is only possible in JavaScript-enabled browsers.)
Here, we have modified the structure of the phone field in the author table to permit a NULL value (refer to the Editing field attributes section later in this Chapter). The Null checkbox is not selected here:
The data is erased after selecting the Null box, as shown in the following screenshot:
The Edit panel will appear this way if this row is ever displayed on the screen again.
The MySQL language offers some functions that we may apply to data before saving our work. Some of these functions appear in a drop-down menu beside each field, if $cfg['ShowFunctionFields'] is set to TRUE.
The function list is defined in the $cfg['Functions'] array. As usual, the default values for these arrays are located in libraries/config.default.php. We may change them by copying the required section into config.inc.php; if we do so, as these values can change from version to version, we should take care of merging our changes with the values of the new version. The most commonly-used functions for a certain data type are displayed first in the list. Some restrictions are defined in the $cfg['RestrictColumnTypes'] and $cfg['RestrictFunctions'] arrays, to control which functions are displayed first.
Here are the definitions that restrict the function names that are displayed for the VARCHAR type:
$cfg['RestrictColumnTypes'] = array( 'VARCHAR' => 'FUNC_CHAR', [...] $cfg['RestrictFunctions'] = array( 'FUNC_CHAR' => array( 'ASCII', 'CHAR', 'SOUNDEX', 'LCASE', 'UCASE', 'PASSWORD', 'OLD_PASSWORD', 'MD5', 'SHA1', 'ENCRYPT', 'COMPRESS', 'UNCOMPRESS', 'LAST_INSERT_ID', 'USER', 'CONCAT' ), [...]
As depicted in the following screenshot, we apply the UPPER function to the title when saving this row:
To gain some screen space (to be able to see more of the data), this feature may be disabled by setting $cfg['ShowFunctionFields'] to FALSE. Moreover, the Function column header is clickable, so we can disable this feature on the fly.
When this feature is disabled—either by clicking on the header or via the configuration parameter—a Show: Function link appears in order to display this Function column with a single click:
During the course of data maintenance (for permanent duplication or for test purposes), we often have to generate a copy of a row. If this is done in the same table, we must respect the rules of key uniqueness.
Here is an example of row duplication. Our author has written Volume 2 of his book about cinema. Hence, the fields that need a slight change are the ISBN, title, and page count. We bring the existing row on screen, change these three fields, and choose Insert as new row, as shown in the following screenshot:
When we click on Go, another row is created with the modified information, leaving the original row unchanged:
The multi-row edit feature enables us to use checkboxes on the rows that we want to edit, and use the Edit link (or the pencil-shaped icon) in the With selected menu. The Check All / Uncheck All links can also be used to quickly select or deselect all of the boxes. We can also click anywhere on the row's data to activate the corresponding checkbox. To select a range of checkboxes, we can click the first checkbox in the range, and then Shift + click the last checkbox in the range:
This brings up an Edit panel containing all of the chosen rows. The editing process may continue while the data from these rows is seen, compared, and changed.
Note
When we select the checkboxes for some rows, we can also perform two other actions on the rows: delete (see the Deleting many rows section in this chapter) and export (see Chapter 6,Exporting Structure and Data (backup)).
Sequential editing is possible on tables that have a primary key on an integer field. Our author table meets this criteria. Let's see what happens when we start editing the row that has the id value 1:
The editing panel appears, and we can edit author number 1. However, in the drop-down menu, the Edit next row choice is available. If chosen, the next author—the first one whose primary key value is greater than the current primary key value—will be available for edit.
phpMyAdmin's interface enables us to delete the following:
- Single rows of data
- Multiple rows in a table
- All of the rows in a table
- All of the rows in multiple tables
We can use the small X-shaped icon beside each row to delete the row. If the value of $cfg['Confirm'] is set to TRUE, every MySQL DELETE statement has to be confirmed before execution. This is the default, because it might not be prudent to allow a row to be deleted with just one click!
The form of the confirmation varies, depending on the browser's ability to execute JavaScript. A JavaScript-based confirmation pop up would look like the following screenshot:
If JavaScript has been disabled in our browser, a distinct panel appears:
The actual DELETE statement will use whatever information is needed to ensure the deletion of only the intended row. In our case, a primary key had been defined and was used in the WHERE clause. In the absence of a primary key, a longer WHERE clause will be generated based on the value of each field. The generated WHERE clause might even prevent the correct execution of the DELETE operation, especially if there are TEXT or BLOB fields. This is because the HTTP transaction, used to send the query to the web server, may be limited in length by the browser or the server. This is another reason why defining a primary key is strongly recommended.
Let's say we examine a page of rows and decide that some rows have to be destroyed. Instead of deleting them one-by-one with the Delete link or icon—and because sometimes the decision to delete must be made while examining a group of rows—there are checkboxes beside the rows in Table view mode:
These are used with the With selected X-shaped icon. A confirmation screen appears, listing all the rows that are about to be deleted. It is also possible to click anywhere on the row's data to activate the corresponding checkbox.
To completely erase all of the rows in a table (leaving its structure intact), we go to the Database view and click on the database name in the navigation panel. We then click on the trash can icon located on the same line as the table we want to empty:
We get a message confirming the TRUNCATE statement (the MySQL statement used to quickly empty a table). For our exercise, we won't delete this precious data!
Emptying a table can also be done in Table view, via the Empty link located on the top menu:
Deleting a table erases all of the data and also the table's structure. We can delete tables by using the Drop link in Table view:
In the Database view, we can delete a specific table by using the X-shaped icon for that table. The same mechanism also exists for deleting more than one table (with the drop-down menu and the Drop action).
We can delete an entire database (including all of its tables) using the Drop link in the Database view:
By default, $cfg['AllowUserDropDatabase'] is set to FALSE. So this link is hidden from unprivileged users unless this setting is manually changed to TRUE.
To help us think twice, a special message—You are about to DESTROY a complete database!—appears before a database is deleted.