Because mail
is a UNIQUE table key (PRIMARY works the same), you can accomplish your intended logic with just one query. In accordance with best coding practices, you should always perform as few queries as possible.
As you will see, there is no reason to call a SELECT query. If there is no error/exception caught, then your INSERT was successful. If a duplicate email address is submitted, then a very informative exception will be generated. You only need to listen for the potential error code: 1062
.
Code:
$jinput = JFactory::getApplication()->input;
$name = $jinput->getString('name');
$mail = $jinput->getString('mail');
if (!filter_var($mail, FILTER_VALIDATE_EMAIL)) {
JFactory::getApplication()->enqueueMessage('Invalid Email. Please Check Email & Try Again.', 'notice');
} else {
$db = JFactory::getDBO();
try {
$query = $db->getQuery(true)
->insert($db->qn('league'))
->columns($db->qn(array('name', 'mail')))
->values($db->q($name) . ',' . $db->q($mail));
$db->setQuery($query);
$db->execute();
JFactory::getApplication()->enqueueMessage('Success!', 'message'); // is only reached if no errors
} catch (Exception $e) {
if ($e->getCode() == 1062) {
JFactory::getApplication()->enqueueMessage('Duplicate Email. Please Try Another Email.', 'notice');
} else {
JFactory::getApplication()->enqueueMessage('Syntax Error. Please Contact Developer.', 'error');
}
}
}
Some other small notes:
- I've baked in a validation step for the
mail
input as an auxiliary suggestion.
- Because your
$columns
and $values
array are "single-use", I've decided against declaring them, and simply added the data directly into the respective query methods.
- I am using
q()
as shorthand for quote()
and qn()
for quoteName()
for brevity. For the record, your query will be just as safe/successful without any of the qn()
calls because you are using single static words and none are MYSQL RESERVED words.
- To demonstrate the rule that I am leveraging, see this SQLFiddle Demo that tries to INSERT two rows with the same email value. It doesn't show the error code, just the error message.