Database Reference
In-Depth Information
Email addresses are critical for communicating with members so we decide to require new
members to enter their email address twice during the registration process to ensure accur-
acy. However, in case the connection is lost in the process or the joining member does not
correct a problem with their email address, we want to keep both addresses until they do.
So we'll add a row to the
humans
table to store whatever information they give us, and
then store both email addresses in another table to compare them. For that comparison, we
could use the
STRCMP()
function in an SQL statement.
This scenario is the kind of situation that you would automate with an API program, a
program you would create to interface with MySQL or MariaDB. It would store the SQL
statements needed for processing the information the new member enters from the web-
site. To start the process related to checking the email, we might create a table that will
store the member's identification number and the two email addresses. We could do that
like so:
CREATE TABLE
possible_duplicate_email
(
human_id
INT
,
email_address1
VARCHAR
(
255
),
email_address2
VARCHAR
(
255
),
entry_date
datetime
);
Now when new members register, after their information has been stored in the
humans
table, our web interface can store conditionally the two email addresses provided in the
possible_duplicate_email
table. It might look like this:
INSERT IGNORE INTO
possible_duplicate_email
(
human_id
,
email_address_1
,
email_address_2
,
entry_date
)
VALUES
(
LAST_INSERT_ID
(),
'bobyfischer@mymail.com'
,
'bobbyfischer@mymail.com'
)
WHERE
ABS
(
STRCMP
(
'bobbyrobin@mymail.com'
,
'bobyrobin@mymail.com'
) )
=
1
;
For the email addresses, I've displayed the plain text. But in a more realistic example, this
SQL statement might be embedded in a PHP script and would refer to variables (e.g.,
$e-
mail_1
and
$email_2
) where the email addresses are here.
Using the
STRCMP()
in the
WHERE
clause, ifthe email addresses match,
STRCMP()
re-
turns 0. If the addresses don't match, it will return 1 or -1. It returns -1 if the first value is
alphabetically before the second. To allow for that possibility, we put it inside of
ABS()
,
which changes the value to the absolute value — it makes negative values positive. So, if
the two email addresses don't match, the statement will insert the addresses into the
pos-
sible_duplicate_email
table for an administrator to review. Incidentally, that
would normally return an error message, but
IGNORE
flag tells MySQL to ignoreerrors.
