5.7 Case Study: Adding a New Wine

In this section, we show you an example that combines some of the statements we've discussed in this chapter, and shows you the basics of writing data to databases.

In this example, let's insert a new wine into the database using the MySQL command-line interpreter. Let's suppose that 24 bottles of a new wine, a Curry Cabernet Merlot 1996 made by Rowley Brook Winery, have arrived, and you wish to add a row to the database for the new wine. This new wine costs $14.95 per bottle.

The addition has several steps, the first of which is to find out the next available wine_id. You need to do this because we're not using the MySQL-proprietary auto_increment feature in the winestore database. Here's the query:

SELECT max(wine_id) FROM wine;

This reports:

+--------------+

| max(wine_id) |

+--------------+

|         1048 |

+--------------+

1 row in set (0.00 sec)

Now, we can use an INSERT INTO statement to create the basic row for the wine in the wine table:

INSERT INTO wine SET wine_id=1049, wine_name='Curry Hill', year=1996,

  description='A beautiful mature wine. Ideal with red meat.';

This creates a new row and sets the basic attributes. The wine_id is set to the 1048 + 1 = 1049. The remaining attributes (the wine_type identifier, the winery_id identifier, and the varieties in the wine_variety table) 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 Rowley Brook Winery winery to identify the winery_id:

SELECT winery_id FROM winery WHERE winery_name='Rowley Brook Winery';

The result returned is:

+-----------+

| winery_id |

+-----------+

|       298 |

+-----------+

1 row in set (0.00 sec)

We can now update the new wine row to set the winery_id=298:

UPDATE wine SET winery_id = 298 WHERE wine_id = 1049;

The third step is similar to the second, and is to set the wine_type identifier in the wine table. You can discover the wine_type_id for a Red wine using:

SELECT wine_type_id FROM wine_type WHERE wine_type = "Red";

This reports that:

+--------------+

| wine_type_id |

+--------------+

|            6 |

+--------------+

1 row in set (0.01 sec)

Now, you can set the identifier in the wine table:

UPDATE wine SET wine_type = 6 WHERE wine_id = 1049;

The fourth 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 variety_id=11 and Merlot 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=1049, variety_id=11, id=1;

INSERT INTO wine_variety SET wine_id=1049, 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 cost of $14.95:

INSERT INTO inventory SET wine_id=1049, inventory_id=1, on_hand=24, 

  cost=14.95, date_added="04/03/01";

We've finished inserting the wine into the database. Now, to conclude, let's retrieve the details of the wine to make sure everything is as it should be. We'll retrieve the wine name, its year, the winery, the varieties, the wine type, and its cost. Here's the query:

SELECT year, wine_name, winery_name, variety, wine_type.wine_type, cost

  FROM wine, winery, wine_variety, grape_variety, wine_type, inventory

  WHERE wine.wine_id = 1049 AND

  wine.wine_id = wine_variety.wine_id AND

  wine_variety.variety_id = grape_variety.variety_id AND

  wine.wine_type = wine_type.wine_type_id AND

  wine.winery_id = winery.winery_id AND

  wine.wine_id = inventory.wine_id 

  ORDER BY wine_variety.id;

The WHERE clause looks complicated, but it just joins together all of the tables in the FROM clause by matching up the identifier attributes and specifies we want for wine #1049. Here's the output:

+------+------------+---------------------+----------+-----------+-------+

| year | wine_name  | winery_name         | variety  | wine_type | cost  |

+------+------------+---------------------+----------+-----------+-------+

| 1996 | Curry Hill | Rowley Brook Winery | Cabernet | Red       | 14.95 |

| 1996 | Curry Hill | Rowley Brook Winery | Merlot   | Red       | 14.95 |

+------+------------+---------------------+----------+-----------+-------+

2 rows in set (0.01 sec)

Two rows are returned because there are two varieties for this wine in the wine_variety table.

We've now covered as much complex querying in SQL as we need for you to develop most web database applications. You'll find a discussion of advanced features you can use in Chapter 15. Beginning in the next chapter, we show you how to include SQL statements in PHP scripts to automate querying and build web database applications.