Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

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.