Quick Links
Once the MySQL database has been created, you will likely need to maintain it, i.e., make alterations to it.
There are three basic operations which could be performed:
We will look at these three separate cases, which are listed in the Quick Links above.
When preparing to make modifications to a database, it is often desirable to see the currently stored data sorted in various ways. Instead of writing a separate script to sort each column and display the data, it is usually preferable to create a single script which provides the option of sorting any given column. We will continue to use the table 1960 of the pirates database introduced in my MySQL Introduction.
The code below sets up a query to get all information from the table sorted by the values in any one particular column. It uses the $sortby variable whose value has been passed from an earlier execution of the script.
This code is used in the script to produce the Sortable Complete Player List, which is an extension of the Complete Player List script.
$sortby = $_REQUEST['sortby'];
$sql_qy = "SELECT * FROM `1960` ";
switch ($sortby):
case 'lname':
$sql_qy .= "ORDER BY lname";
break;
case 'fname':
$sql_qy .= "ORDER BY fname";
break;
case 'ab':
$sql_qy .= "ORDER BY ab DESC";
break;
case 'hits':
$sql_qy .= "ORDER BY hits DESC";
break;
case 'ave':
$sql_qy .= "ORDER BY ave DESC, ab DESC";
break;
default:
$sql_qy .= "ORDER BY id";
break;
endswitch;
Typically, data such as names are sorted in ascending (alphabetic) order, while numeric data are sorted in descending order. This is done here for the baseball statistics, but not for the ID field. When there are multiple records with the same value in a certain field, you may wish to control the order of those records. With the baseball data used here, this actually occurs with the average field. Listing the players in descending order based on at bats is the usual choice; this is done here with the phrase "ORDER BY ave DESC, ab DESC".
Note that if you were to have phonebook-type data with numerous people with the same surname, you might wish to sort those people by first name; this can be done using a phrase such as "ORDER BY lname, fname". This is not important here, but in 1963 the three Alou brothers, Felipe, Matty and Jesus, all played for the San Francisco Giants.
The data records are stored in the MySQL table in the order in which they were entered. The first time the script is executed, $sortby will not possess a value; this situation is handled by the default case in the switch construction. Note the wordspace at the end of the string used to initialize the $sql_qy variable; this allows the sort specification to be easliy appended.
To allow the user to specify the column the data is to be sorted on, the following code is used to replace the printing of the column headings.
echo "<th><a href=\"" . $PHP_SELF . "?sortby=id\">ID</a></th>
<th><a href=\"" . $PHP_SELF . "?sortby=fname\">First Name</a>
<a href=\"" . $PHP_SELF . "?sortby=lname\">Last Name</a></th>
<th><a href=\"" . $PHP_SELF . "?sortby=ab\">AB</a></th>
<th><a href=\"" . $PHP_SELF . "?sortby=hits\">Hits</a></th>
<th><a href=\"" . $PHP_SELF . "?sortby=ave\">Ave</a></th>";
The column headings have been turned into links which recursively call the script, whose name is contained in the $PHP_SELF variable.
The script which performs the insertion of the new record is very straightforward. A list of the fields (each delimited by backquotes) for which data is being specified is given in a comma-separated list inside parens. The MySQL term VALUES introduces a comma-separated list inside parens of the data items being inserted. Each value is delimited by single quotes.
$sql_qy = "INSERT INTO `1960` ";
$sql_qy .= "( `lname` , `fname` , `ab` , `hits` , `ave`) ";
$sql_qy .= "VALUES (";
$sql_qy .= " '$lname', ";
$sql_qy .= " '$fname', ";
$sql_qy .= " '$ab', ";
$sql_qy .= " '$hits', ";
$sql_qy .= " '$ave' )";
$rslt = mysql_query( $sql_qy ) or die("Insert failed!!!");
echo "<p>The insert was successful.\n</p>";
The id field was specified as being auto-increment, so it is not given in the "query".
To see how this could be used in a project, see the Add (Insert) Customers in the Outfitters Database Problem.
To update/modify the data in a certain record, the UPDATE command is used. In the code fragment below, the id field is used to ensure that the correct record is in fact the one being updated.
$sql_qy = "UPDATE `1960` SET ";
$sql_qy .= "id = '$id', ";
$sql_qy .= "lname = '$lname', ";
$sql_qy .= "fname = '$fname', ";
$sql_qy .= "ab = '$ab', ";
$sql_qy .= "hits = '$hits', ";
$sql_qy .= "ave = '$ave' ";
$sql_qy .= "WHERE id = '$id' ";
$result = mysql_query( $sql_qy ) or die("Update failed!!!");
echo "<p>The update was successful.\n</p>";
To see how this could be used in project, see the Edit (Update) Customers in the Outfitters Database Problem.
Deleting a record is a very simple matter. The query below immediately deletes the specified record as soon as no other clients are reading from the table.
$sql_qy = "DELETE LOW_PRIORITY FROM `1960` WHERE id = '" . $item['id'] . "' ";
$result = mysql_query( $sql_qy ) or die("Deletion failed!!!");
To see how this could be used in project, see the Remove (Delete) Customers in the Outfitters Database Problem.