PHP —

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 |
+-----------+
1 row in set (0.00 sec)

We can now update the new wine row to set the winery_id=221. However, which row to update? An easy way to find the wine_id of the new wine row is to use the built-in function last_insert_id( ). As discussed in the earlier section Section 3.5, this function returns the number created by the most recent auto_increment modifier:

 SELECT last_insert_id(  );

This returns the wine_id of the inserted row:

+------------------+
| last_insert_id(  ) |
+------------------+
|             1029 |
+------------------+
1 row in set (0.00 sec)

You can now issue the UPDATE statement:

UPDATE wine SET winery_id = 221
  WHERE wine_id = 1029;

The third step is to set the variety information for the new wine. We need the variety_id values for Cabernet and Merlot. These can be found with a simple query:

SELECT * FROM grape_variety;

In part, the following results are produced:

+------------+------------+
| variety_id | variety    |
+------------+------------+
|          1 | Riesling   |
|          2 | Chardonnay |
|          3 | Sauvignon  |
|          4 | Blanc      |
|          5 | Semillon   |
|          6 | Pinot      |
|          7 | Gris       |
|          8 | Verdelho   |
|          9 | Grenache   |
|         10 | Noir       |
|         11 | Cabernet   |
|         12 | Shiraz     |
|         13 | Merlot     |

Cabernet has a variety_id=11 and Merlot a variety_id=13. We can now insert two rows into the wine_variety table. Because Cabernet is the first variety, set its ID=1, and ID=2 for Merlot:

INSERT INTO wine_variety
  SET wine_id=1029, variety_id=11, id=1;
INSERT INTO wine_variety
  SET wine_id=1029, variety_id=13, id=2;

The final step is to insert the first inventory row into the inventory table for this wine. There are 24 bottles, with a per-bottle price of $14.95 and per-case price of $171.99:

INSERT INTO inventory VALUES (1029, 1, 24, 14.95, 171.99);

We've now completed the process of inserting rows into other tables in the winestore is similar. Adding data to the winery, region, inventory, and orders tables follows the same approach. Insertion of rows into the customer and grape_variety tables is simpler because there are no attributes that require lookups in other tables.