- Created on Saturday, 01 June 2013 14:44
- Last Updated on Friday, 28 June 2013 05:13
So far in the series it has been demonstrated how to create a database table, ttd, a list of things to do and a notes table, used to store comments on things to do. A table ttdnotes relates the two activities and provides things to do with notes. In this example set a series of basic queries will be performed, using MYSQL's SELECT statement, to extract information from the ttd table.
To cement the ideas learnt previously the examples below will demonstrate the following:
- The Loading of table data into a database.
- Query the database to do the following:
- List all the data in the things to do, ttd, table showing each items id, description and urgency.
- List all items in the ttd table by their description and urgency.
- List all items in the ttd table with a high urgency by their description and urgency .
- List all items with a high urgency and the words 'ARTICLE' in the description by their description and urgency.
In the examples that follow the test database in MYSQL has been used (mysql> use test).
Firstly, in the code Listing below we again load the tables into our database, note the modifications to the tables compared to the those presented in installments 1 and 2 of the series.
SELECT '<INFO_TO_DISPLAY>' AS ' '; DROP TABLE IF EXISTS ttdnotes, notes, ttd;
We find it helpful, to avoid any drama, to use line 2 (DROP TABLE IF EXISTS ttdnotes, notes, ttd;) to delete tables in the reverse order that they have been created. Why?
The tables are created in the next three statements firstly, the ttd table now has an urgency field with 'low' being the least urgent and 'high' the most urgent.
CREATE TABLE IF NOT EXISTS ttd: ( id INT(11) NOT NULL AUTO_INCREMENT, description VARCHAR(255) NOT NULL, urgency enum ('low', 'medium', 'high'), status BOOLEAN NOT NULL DEFAULT 0, insertdate TIMESTAMP NOT NULL DEFAULT NOW(), PRIMARY KEY(id) ) ENGINE=InnoDB;
Then the notes table:
CREATE TABLE IF NOT EXISTS notes ( id INT(11) NOT NULL AUTO_INCREMENT, comment VARCHAR(255) NOT NULL, insertdate TIMESTAMP NOT NULL DEFAULT NOW(), PRIMARY KEY(id) ) ENGINE=InnoDB;
The then ttdnotes table:
CREATE TABLE IF NOT EXISTS ttdnotes ( ttd INT(11) NOT NULL, notes INT(11) NOT NULL, PRIMARY KEY(ttd,notes), FOREIGN KEY(ttd) REFERENCES ttd(id) ON DELETE CASCADE, FOREIGN KEY(notes) REFERENCES notes(id) ON DELETE CASCADE )ENGINE=InnoDB;
The SHOW TABLES statement can be used to verify the fields of the tables. Now lets load some data into the tables.
load data infile './ttd_entries.txt' into table test.ttd fields terminated by ';' lines terminated by '\n' ignore 1 lines;
When using MYSQL in a terminal window on a LINUX system, if you change directory to your project directory, you can load a file by using . to specify the current directory.
Right, lets roll up our sleeves and get to work. Firstly, lets list all the items in the things to do table and display their id, description and urgency fields.
SELECT id, description,urgency FROM ttd;
The result of displaying all items in the ttd table is shown below.
In our current schema there is no provision to categorise the list of thngs to do, hence we have added a sub-description between square brackets to perform such a function. After using the database for a few weeks we should have a good idea as to what categories are likely to be needed and whether to alter the ttd table to add a category field. Alternatively, we could create a category table that references the ttd table.
Next, for each item, display the description and urgency of each item, listing the items from most urgent to least urgent.
SELECT description,urgency FROM ttd ORDER BY CASE urgency WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 END;
Now, only display the items that are of highest priority.
SELECT description,urgency FROM ttd WHERE urgency = 'high';
The result can be seen below.
Finally, as a taster of what's to come display a list of the urgent tasks to do associated with articles.
SELECT description,urgency FROM ttd WHERE urgency = 'high' AND description LIKE '[ARTICLE]%';
Thats all for now, you good folk. The series will continue with part 3, which will focus on adding projects to the database.