Email validation through MySQL triggers and signals

Did some research and found out that MySQL provides triggers and signals. Triggers allow you to run some code when an certain event occurs, and Signals allow you to raise exceptions in your code.

Here is a SQL script to show you how it is done:

CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE `test`;

CREATE TABLE IF NOT EXISTS `test`.`entity_email` (

    `emailID` INT NOT NULL,

    `email` VARCHAR(64) NOT NULL,

    PRIMARY KEY (`emailID`) )

ENGINE = InnoDB

COMMENT = "Generic Email table";

DELIMITER $$

USE `test`$$

CREATE TRIGGER `trg_entity_email_insert` BEFORE INSERT ON `test`.`entity_email` FOR EACH ROW

BEGIN

    IF NOT (SELECT NEW.email REGEXP '$[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$') THEN

        -- bad data

        SIGNAL SQLSTATE '40000';

    END IF;

END$$

CREATE TRIGGER `trg_entity_email_update` BEFORE UPDATE ON `test`.`entity_email` FOR EACH ROW

BEGIN

    IF NOT (SELECT NEW.email REGEXP '$[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$') THEN

        -- bad data

        SIGNAL SQLSTATE '40000';

    END IF;

END$$

DELIMITER ;

Now MySQL will perform e-mail validation EVERY time you insert or edit the record. If it doesn't match, it will fail (MySQL outputs an "Unhandled user-defined exception condition" as the error message).

Hope this helps!

Ubuntu 12.04, LAMP and CodeIgniter 2

This guide aims to install and use CodeIgniter on an Ubuntu machine. We will assume you have the default Ubuntu installation.

1. Install Apache 2:

   sudo apt-get install apache2

2. Install MySQL:
   

   sudo apt-get install mysql-server mysql-client

3. Install PHP5 and the necessary libraries:
   

   sudo apt-get install php5-cli php5-mysql libapache2-mod-php5

4. Download the CodeIgniter framework:
   

   wget http://codeigniter.com/download.php -O ~/CodeIgniter.zip

5. Extract the framework to the default Apache2 web directory:
   

   sudo unzip ~/CodeIgniter.zip /var/www/

6. You should now be able to navigate to your extracted folder from your web-browser. Hint: you will need to open firefox to http://localhost/<name-of-folder> which you can get by executing the following command:
   

   ls /var/www

References

• 'Installing the LAMP stack for Apache2' from www.howtoforge.com
•  Another LAMP stack for Ubuntu tutorial from smashingweb.info
• Settin up LAMP and CodeIgniter from caravaggisto.wordpress.com