My regex check in my MySQL server doesn't seem to be working or I'm making a really obvious mistake (sorry if it is I just can't spot it if so).
Trigger: trig_check_memebers
Event: INSERT
Table: tbl_members
Statement: BEGIN IF (NEW.member_email REGEXP '^(\w+@\w+.\w+)$') = FALSE THEN SIGNAL SQLSTATE '12345' SET MESSAGE_TEXT = 'invalid email'; END IF; END
Timing: BEFORE
Created: NULL
sql_mode:NO_ENGINE_SUBSTITUTION
So that is my trigger just to show the regex I have, it is highlighted. And this is my proof (may be wrong) that it works for the data I want to input.
I know its a bad regex I just started learning it and wanted to test basic stuff, like I know that if there is a email like xxx.yyy@foo.bar it wont have the same result.
And here is my test data producing an error.
INSERT INTO tbl_members VALUES ('jeff', 'password', 'hardy', 'jeff', 'matt', 'you@you.you');
Error
SQL query:
INSERT INTO tbl_members VALUES ('jeff', 'password', 'hardy', 'jeff', 'matt', 'you@you.you')
MySQL said: Documentation
#1644 - invalid email
^ not really code, error message.
<?php
//Variables
$database_name;
$dbc;
$table_create_queries;
$triggers;
//Initialization
$database_name = 'PolySoft';
$dbc = new mysqli('localhost', 'root', null);
$table_create_queries = array
(
//Members Table
'tbl_members' => 'CREATE TABLE tbl_members(member_username varchar(40) NOT NULL,
member_password varchar(60) NOT NULL,
member_surname varchar(40) NOT NULL,
member_forename varchar(40) NOT NULL,
member_othernames varchar(40),
member_email varchar(254),
UNIQUE(member_email),
PRIMARY KEY(member_username));'
,
//Addresses Table
'tbl_addresses' => 'CREATE TABLE tbl_addresses(address_postcode varchar(10) NOT NULL,
address_country varchar(255) NOT NULL,
address_county varchar(255),
address_city varchar(255) NOT NULL,
address_town varchar(255),
address_street varchar(255) NOT NULL,
address_house_name_or_number varchar(255) NOT NULL,
PRIMARY KEY(address_postcode));'
);
$triggers = array
(
'check_members' =>'CREATE TRIGGER trig_check_memebers
BEFORE INSERT ON tbl_members
FOR EACH ROW
BEGIN
IF (NEW.member_email REGEXP \'^(\\\\w+@\\\\w+\\\\.\\\\w+)$\') = FALSE THEN
SIGNAL SQLSTATE \'12345\'
SET MESSAGE_TEXT = \'invalid email\';
END IF;
END;'
);
$dbc->query("DROP DATABASE IF EXISTS $database_name");
$dbc->query("CREATE DATABASE $database_name");
$dbc->close();
$dbc = new mysqli('localhost', 'root', null, $database_name);
foreach($table_create_queries as $tbl => $query)
{
$dbc->query($query);
}
foreach($triggers as $trig => $query)
{
$dbc->query($query);
}
$dbc->query("INSERT INTO tbl_members VALUES ('jeff', 'password', 'hardy', 'jeff', 'matt', 'you@you.you')");
//$dbc->query();
$dbc->close();
?>
If anyone is wondering why I'm dropping and creating the database: the code would never be actually on a server, its so that for my assignment I can just run the script on any server so I don't have to rely on bringing in my PC to show how it works. The tutor can just run this on his to create it and it is also for my database module too so having all the sql in one location is also good for my reference etc.
Is it Possible to Enforce Data Checking in MySQL using Regular expression
The top answer is what got me the trigger code.
