Solution
If you see the solution, having the labels field as an array is not possible. Its because, it is very tedious both for the programmer as well as the server. Instead, we can do one thing. We can use JOINS and join two tables, and proceed.
What two tables?
Have one table for the mails and another separate table for the labels. Join them by referring the Mail ID for which the label is assigned, in the labels table with the ID of mail in the mails table. To do this, follow the steps.
Step 1: Create a Database named mailserver.
CREATE DATABASE `mailserver`;
Step 2: Create a Table named mails, with the following options:
CREATE TABLE `mailserver`.`mails` (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Subject` VARCHAR( 50 ) NOT NULL ,
`Content` VARCHAR( 500 ) NOT NULL
) ENGINE = InnoDB;
Step 3: Create a Table named maillabel, with the following options:
CREATE TABLE `mailserver`.`maillabel` (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`LabelName` VARCHAR( 50 ) NOT NULL ,
`MailID` INT NOT NULL
) ENGINE = InnoDB;
Step 4: Insert a few sample mails under the table mails.
INSERT INTO `mailserver`.`mails` (
`ID` ,
`Subject` ,
`Content`
)
VALUES (
NULL , 'Welcome Home', 'Hey man, Welcome to your new house.'
), (
NULL , 'Hi', 'Hey there, wanna see what you doing at home!'
), (
NULL , 'Your promotion', 'This is to say about your promotion!'
), (
NULL , 'What the hell?', 'College is really bad!'
), (
NULL , 'My Project Work!', 'I have hereby attached my project work!'
);
Step 5: Assign the mails with the mail labels as shown here.
INSERT INTO `mailserver`.`maillabel` (
`ID` ,
`LabelName` ,
`MailID`
)
VALUES (
NULL , 'inbox', '1'
), (
NULL , 'inbox', '3'
), (
NULL , 'inbox', '5'
), (
NULL , 'personal', '1'
), (
NULL , 'friends', '2'
), (
NULL , 'office', '3'
), (
NULL , 'personal', '4'
), (
NULL , 'college', '5'
);
Now lets see the contents of the tables.
mails
mysql> select * from mailserver.mails;
+----+------------------+----------------------------------------------+
| ID | Subject | Content |
+----+------------------+----------------------------------------------+
| 1 | Welcome Home | Hey man, Welcome to your new house. |
| 2 | Hi | Hey there, wanna see what you doing at home! |
| 3 | Your promotion | This is to say about your promotion! |
| 4 | What the hell? | College is really bad! |
| 5 | My Project Work! | I have hereby attached my project work! |
+----+------------------+----------------------------------------------+
5 rows in set (0.00 sec)
maillabel
mysql> select * from mailserver.maillabel;
+----+-----------+--------+
| ID | LabelName | MailID |
+----+-----------+--------+
| 1 | inbox | 1 |
| 2 | inbox | 3 |
| 3 | inbox | 5 |
| 4 | personal | 1 |
| 5 | friends | 2 |
| 6 | office | 3 |
| 7 | personal | 4 |
| 8 | college | 5 |
+----+-----------+--------+
8 rows in set (0.02 sec)
Step 6: Using JOIN, join both the tables, mails and maillabel, with the following command and see the output.
mysql> select * from mailserver.mails join mailserver.maillabel
on mailserver.mails.id=mailserver.maillabel.mailid;+----+------------------+----------------------------------------------+----+-----------+--------+
| ID | Subject | Content | ID | LabelName | MailID |
+----+------------------+----------------------------------------------+----+-----------+--------+
| 1 | Welcome Home | Hey man, Welcome to your new house. | 1 | inbox | 1 |
| 1 | Welcome Home | Hey man, Welcome to your new house. | 4 | personal | 1 |
| 2 | Hi | Hey there, wanna see what you doing at home! | 5 | friends | 2 |
| 3 | Your promotion | This is to say about your promotion! | 2 | inbox | 3 |
| 3 | Your promotion | This is to say about your promotion! | 6 | office | 3 |
| 4 | What the hell? | College is really bad! | 7 | personal | 4 |
| 5 | My Project Work! | I have hereby attached my project work! | 3 | inbox | 5 |
| 5 | My Project Work! | I have hereby attached my project work! | 8 | college | 5 |
+----+------------------+----------------------------------------------+----+-----------+--------+
8 rows in set (0.00 sec)
Step 7: Using a condition, we can get the contents of the mails from the particular label, inbox.
mysql> select * from mailserver.mails join mailserver.maillabel
on mailserver.mails.id=mailserver.maillabel.mailid
where mailserver.maillabel.labelname="inbox";+----+------------------+-----------------------------------------+----+-----------+--------+
| ID | Subject | Content | ID | LabelName | MailID |
+----+------------------+-----------------------------------------+----+-----------+--------+
| 1 | Welcome Home | Hey man, Welcome to your new house. | 1 | inbox | 1 |
| 3 | Your promotion | This is to say about your promotion! | 2 | inbox | 3 |
| 5 | My Project Work! | I have hereby attached my project work! | 3 | inbox | 5 |
+----+------------------+-----------------------------------------+----+-----------+--------+
3 rows in set (0.00 sec)
Step 8: The same way, we can get the mails labeled personal using this command.
mysql> select * from mailserver.mails join mailserver.maillabel
on mailserver.mails.id=mailserver.maillabel.mailid
where mailserver.maillabel.labelname="personal";+----+----------------+-------------------------------------+----+-----------+--------+
| ID | Subject | Content | ID | LabelName | MailID |
+----+----------------+-------------------------------------+----+-----------+--------+
| 1 | Welcome Home | Hey man, Welcome to your new house. | 4 | personal | 1 |
| 4 | What the hell? | College is really bad! | 7 | personal | 4 |
+----+----------------+-------------------------------------+----+-----------+--------+
2 rows in set (0.00 sec)
Step 9: If we need to get, what all the labels assigned to a particular mail, we can use this command, giving the ID of the mail.
mysql> select * from mailserver.maillabel join mailserver.mails
on mailserver.mails.id=mailserver.maillabel.mailid
where mailserver.mails.id=3;+----+-----------+--------+----+----------------+--------------------------------------+
| ID | LabelName | MailID | ID | Subject | Content |
+----+-----------+--------+----+----------------+--------------------------------------+
| 2 | inbox | 3 | 3 | Your promotion | This is to say about your promotion! |
| 6 | office | 3 | 3 | Your promotion | This is to say about your promotion! |
+----+-----------+--------+----+----------------+--------------------------------------+
2 rows in set (0.00 sec)
Step 10: This is for scripting, getting only the labels. The command is:
mysql> select labelname from mailserver.maillabel
join mailserver.mails
on mailserver.mails.id=mailserver.maillabel.mailid
where mailserver.mails.id=3;+-----------+
| labelname |
+-----------+
| inbox |
| office |
+-----------+
2 rows in set (0.00 sec)
This implementation is given using MySQL Server 5.1. This concept can be applied to Oracle, Microsoft SQL Server, IBM DB2, and so on. Even we can apply this to MS Access. Just the syntax of the queries differ in each database.
Source: #-Link-Snipped-#
