title: handling double username in mysql and php
body: <p>I'm writing my own implementation of an API. One of the resources of this API is <code>/user</code>, I can <code>POST</code> a <code>JSON</code> string to this, parse it and send it to a <code>UserService</code> which has an <code>addUser</code> method. Now obviously I can't allow two users to have the same username. This is what I have to so far.</p>
  
  <pre><code>protected function handlePost() {
      $user = json_decode($this-&gt;getRequest()-&gt;getRequestData(), true);
  
      try {
          $createdUserId = $this-&gt;userService-&gt;addUser(
              $user['username'],
              $user['password'],
              $user['typeId'],
              $user['companyId']
          );
      } catch (PDOException $e) {
          $this-&gt;getResponse()-&gt;setStatus(102);
      }
  
      if ($createdUserId)
          $this-&gt;getResponse()-&gt;setStatus(101);
  
      $this-&gt;getResponse()-&gt;sendResponse();
  }
  </code></pre>
  
  <p>This function is called after the HTTP request has been parsed. As you can see, I get the <code>$user</code> object in an associative array. I then use these values as parameters to the <code>$this-&gt;userService-&gt;addUser</code> method.</p>
  
  <p>This is the <code>addUser()</code> method:</p>
  
  <pre><code>public function addUser($username, $password, $type, $companyId) {
      $sth = $this-&gt;dbh-&gt;prepare('INSERT INTO app_user
                                  (username, password, typeId, companyId)
                                  VALUES (?, ?, ?, ?)');
      $userAdded = $sth-&gt;execute(array($username, md5($password), $type, $companyId));
  
      return ($userAdded)
          ? $this-&gt;dbh-&gt;lastInsertId()
          : null;
  }
  </code></pre>
  
  <p>Now if the <code>$sth-&gt;execute</code> fails for some reason, I return null (and neither status code 101 or 102 will be returned, I'm aware of this). But if a username already exists PDO throws an exception, which I catch in my <code>handlePost()</code> method above.</p>
  
  <p>My problem with this is of course that a username which has already been taken is not exceptional and should not be handled this way but I don't really know how I should handle it?</p>
  
  <p>It must remain possible for me to be able to distinguish between a unique constraint being triggered and some other (maybe concurrency issue) being triggered so I can send the appropriate status code back to the consumer of the API.</p>
  
  <p>An obvious solution would be to check if the username exists in a a separate query but for obvious reasons I would prefer to keep the calls to the database limited.</p>
  
  <p>How can I solve this elegantly? If I do have to use exceptions, is there a list of specific PDO exceptions so that I don't have to use the generic <code>PDOException</code>?</p>