Web Database Applications with PHP and MySQL, 2nd Edition

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 ...