Web Database Applications with PHP, and MySQL

Modifying the Database

In this section, we consider simple examples of writing data to databases. Multiple users writing data, how to manage locking of databases, and more complex transactions with the MySQL DBMS are discussed in Chapter 6.

Adding a New Wine to the Winestore

To illustrate a write transaction with the winestore database, consider an example of inserting a new wine. This process can be performed with the MySQL command-line interpreter. Only one user is interacting with the DBMS in this example.

Let’s suppose that 24 bottles of a new wine, a Curry Hill Cabernet Merlot 1996 made by De Morton Hill wineries, have arrived, and you wish to add a row to the database for the new wine.

The addition has several steps, the first of which is an INSERT INTO statement to create the basic row for the wine in the wine table:

INSERT INTO wine 
  SET wine_name='Curry Hill', 
  type='Red', 
  year=1996, 
  description='A beautiful mature wine. Smooth to taste
               Ideal with red meat.';

This creates a new row and sets the basic attributes. The wine_id is set to the next available value because of the auto_increment and DEFAULT modifiers. The remaining attributes to insert require further querying and then subsequent updates.

The second step is to set the winery_id for the new wine. We need to search for the De Morton Hill winery to identify the winery_id:

SELECT winery_id FROM winery 
  WHERE winery_name='De Morton Hill';

The result returned is:

+-----------+ | winery_id | +-----------+ | 221 | +-----------+ ...