Chapter 5 ■ MySQL priMer
column in the table, we must first insert the data about the plant and then query the table for its ID. The
following shows an example:
mysql> INSERT INTO plant_monitoring.plants VALUES (NULL, 'fern', 'beside picnic table', 2);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 9 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM plant_monitoring.plants WHERE id = 9;
+----+------+---------------------+---------+
| id | name | location | climate |
+----+------+---------------------+---------+
| 9 | fern | beside picnic table | outside |
+----+------+---------------------+---------+
1 row in set (0.00 sec)
But notice I did not have to actually query the plants table. Instead, I used the LAST_INSERT_ID()
function, which returns the value of the last AUTO_INCREMENT value generated. Cool!
Once we have this information, we can program the sensor reader to send this information to the
database to populate the readings table and therefore make the relationship to the plants table. That means
when we query for all the sensor readings for a particular plant, the join will return the correct information.
Thus, if I wanted to store data about the plant with id = 9, I would use an INSERT statement like the
following:
INSERT INTO plant_monitoring.readings VALUES (9, 233, 38.4, NULL, NULL);
DataBaSe DeSIGN FIrSt? What aBOUt the appLICatION?
i once worked for an organization that felt the application and user interface were more important than
the database design. i guess that’s why they eventually hired more DBas than developers. they had to
reengineer the database several times during the development of the application, which caused major
delays in deliveries and some interesting and serious bugs.always design your database including the questions you want to ask after you’ve defined the high-
level requirements but before you implement the source code. this way, your application will be based
on a fully functional database system with well-defined queries rather than struggling to make queries
fit some code implementation.Now let’s see how to get information out of the database.