Check if INSERT query was successful or failed because of a duplicate UNIQUE KEY

Question

I have a db-table called league where the mail column is the unique id:

|name |mail               |
|Bryan|bryan@hotmail.com  |

I have a frontend where users can sign up and then I have this for backend:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$jinput = JFactory::getApplication()->input;

$name = $jinput->getString('name');
$mail = $jinput->getString('mail');

$columns = array('name', 'mail');

$values = array($db->quote($name), $db->quote($mail));

$query
    ->insert($db->quoteName('league'))
    ->columns($db->quoteName($columns))
    ->values(implode(',', $values));

$db->setQuery($query);
$db->execute();

Now, if Bryan tries to sign up with the same mail, an error occurs. How do I write a message instead of the normal error-page? I want something like: "Thanks for signing up" if the query is successful and "You're already signed up" if the query fails.

Answer 1

You will need to perform a select query against your database table, searching for the email address. If a result is returned, you can throw an alert message, else perform the insert query, something like this:

$jinput = JFactory::getApplication()->input;
$name   = $jinput->getString('name');
$mail   = $jinput->getString('mail');

$db = JFactory::getDbo();

$query = $db->getQuery(true)
      ->select($db->quoteName('mail'))
      ->from($db->quoteName('league'))
      ->where($db->quoteName('mail') . ' = '. $db->quote($mail));
$db->setQuery($query);

$result = $db->loadResult();

if (!$result)
{
    $columns = array('name', 'mail');
    $values = array($db->quote($name), $db->quote($mail));

    $query = $db->getQuery(true);

    $query->clear();
    $query->insert($db->quoteName('league'))
        ->columns($db->quoteName($columns))
        ->values(implode(',', $values));

    $db->setQuery($query);
    $db->execute();
}
else
{
    JFactory::getApplication()->enqueueMessage('Email already exists', 'error');
}

Answer 2

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.