Report card table

I have a list of e-mails that we test every day and give it a grade from 0 to 7 depending if it works or not - 0 means it work, 7 means it does not and several points in between. I would like to produce a list (in PHP) with the report card of the last 30 days, for each of the e-mails, organized by Country -> Domain -> e-mails, in that order. That is, we will list all the countries, and then all the domains (that occur in the list) and then all the e-mails within that domain. So the list will look like: Autralia - au Australian National University john@[login to view URL] mary@[login to view URL] La Trobe University miriam@[login to view URL] ...... Austria - at Karl-Franzens-Universit?t Graz (Uni Graz) joe@[login to view URL] TU-Wien mary@[login to view URL] Universit?t Wien joe@[login to view URL] ...... Then each e-mail will have a list of grades (from 0 to 7) colored as follows: green for 0, red for 1 or 7 and yellow for anything from 2 to 6. An example would be: joe@[login to view URL] G G G G R R Y Y G Y R R R Y R R Y G R Y R G Y G You will need to deal with 4 tables: countries - that lists all countries and their top-level domain(s). institutions - listing all universities and their domains. emails - with a list of all the emails. email_checker - with the report card on the emails. I'll provide them, SQL and test data. Details of table and steps will be forthcoming in an attachment. Successful delivery should work on a MySQL server running Apache and PHP. Paulo Ney ## Deliverables **Details** The 4 tables are: countries institutions emails email_checker and the first one contain the list of all countries and their top-level domains - not all of them present in the list of emails. Only the ones present in the email should be listed. The list of all top-level domains in the list of e-mail can be obtained by: select distinct substring_index(email, '.', -1) from emails where order_bit > 0; Observe that only e-mails with order_bit > 0 will be used in throught this program. And that out reading from the DB should use UTF-8 as the character set. The list of Universities should come from the second table institution and one can obtain the list of Australian universities (for example) by: select name, domain_1, domain_2, domain_3 from institutions where substring_index(domain_1, '.', -1) = 'au' or substring_index(domain_2, '.', -1) = 'au' or substring_index(domain_3, '.', -1) = 'au' order by name; One can then find the e-mails of one particular institution, by one of two methods: 1- Picking out each institutional domain name (there may be more than one domain per institution) and then picking each e-mail that the ending string-matches that given domain - when split at the dots (.). 2- Using a small program I have that I have that given an e-mail extracts the part of the domain from that e-mail, and then matching it to the given domain in question. This program will be sent to the succesful bidder on the project. The e-mails should be listed by order of the second piece after the @-sign, that is, alphabetical by substring_index(email, '@', -1) and then alphabetical on the user_name that is give by: substring_index(email, '@', 1) Then each e-mail will have a list of grades (from 0 to 7) colored as follows: green for 0, red for 1 or 7 and yellow for anything from 2 to 6. An example would be: joe@[login to view URL] G G G G R R Y Y G Y G Y R R R Y R R Y G R Y R G the letters here are representing a small colored rectangle. A link should be supplied to the e-mail addresses pointing to: http://localhost/[login to view URL] where people_id is the id of the person associated with the email. A button at the end of each line should allow one to "retire" an e-mail which is to move it to order_bit=0. **The tables** The SQL-schema is below, I am listing only the columns used by this program, if you need all other columns in the tables, please specify. DROP TABLE IF EXISTS `msp3t`.`countries`; CREATE TABLE `msp3t`.`countries` ( `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `country` varchar(50) DEFAULT NULL, `tld` varchar(5) DEFAULT NULL, PRIMARY KEY (`id`), KEY `country` (`country`) ) ENGINE=InnoDB AUTO_INCREMENT=254 DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `msp3t`.`emails`; CREATE TABLE `msp3t`.`emails` ( `id` int(11) NOT NULL AUTO_INCREMENT, `people_id` int(11) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `order_bit` tinyint(1) DEFAULT NULL, `first_stopped` date DEFAULT NULL, `last_stopped` date DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `people_id_2` (`people_id`,`email`), KEY `email` (`email`), KEY `people_id` (`people_id`) USING BTREE, CONSTRAINT `emails_ibfk_1` FOREIGN KEY (`people_id`) REFERENCES `people` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=92233 DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `msp3t`.`email_checker`; CREATE TABLE `msp3t`.`email_checker` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `email_id` int(11) DEFAULT NULL, `error_code` tinyint(1) DEFAULT NULL, `date_checked` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `email_id` (`email_id`), CONSTRAINT `email_checker_ibfk_1` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4434923 DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `msp3t`.`institutions`; CREATE TABLE `msp3t`.`institutions` ( `s_id` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(70) DEFAULT NULL, `alt_name_1` varchar(70) DEFAULT NULL, `alt_name_2` varchar(70) DEFAULT NULL, `domain_1` varchar(50) DEFAULT NULL, `domain_2` varchar(50) DEFAULT NULL, `domain_3` varchar(50) DEFAULT NULL, PRIMARY KEY (`s_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5547 DEFAULT CHARSET=utf8; **Installation and Delivery** A succesful installation and delivery will be made on a system running LAMP (Lynux, Apache, MySQL and PHP). Please have a set of configuration variable upfront, like in: $dbhost = "localhost"; $dbuser = "wft"; $dbpass = "xptoy3"; $dbname = "test"; ?> and the whole package self-contained in a single directory, if possible. Paulo Ney