title: MySQL: Insert into one table if not exists; if it does, drop it and insert it into another table
body: <p>First, my basic structure is as follows.</p>
  
  <p><code>friends</code> table, with some sample relationships added:</p>
  
  <pre><code>-------------------------------
  |user1 | user2 | util1 | util2|
  -------------------------------
  | 123  |  456  |  123  |  456 |
  | 789  |  012  |  012  |  789 |
  | 345  |  678  |  345  |  678 |
  </code></pre>
  
  <p>(The <code>util</code> columns are helper columns associated with a trigger I have--if, say, <code>(user1,user2) = (123,456)</code> exists, then I don't want <code>(456,123)</code> to be able to be created. The trigger inserts <code>(util1,util2)</code> as <code>(least,greatest)</code>; then there's a unique index on that. This is fine, just figured I'd explain that.)</p>
  
  <p><code>friendReq</code> table of pending friend requests:</p>
  
  <pre><code>----------------
  |initBy| reqTo |
  ----------------
  | 901  |  234  |
  | 567  |  890  |
  | 234  |  567  |
  </code></pre>
  
  <p>What works: if, say, user of id <code>901</code> requests <code>234</code>, this request is added to the friend request table if it doesn't already exist; I have a unique index on <code>(initBy,reqTo)</code>, so if <code>901</code> resends a request to <code>234</code> it won't insert and extra row. Cool.</p>
  
  <p>What I need: if <code>234</code> subsequently sends a request to <code>901</code>, I don't want <code>(initBy,reqTo) = (234, 901)</code> to be inserted (note that I already have it set up that when <code>901</code> requests <code>234</code>, <code>234</code> receives a notification; this works fine. Here I am just supposing that <code>234</code> ignores the request and decides to try to add <code>901</code> manually via my simple text field + add friend button). Instead, I want it to automatically accept the friends request--that is, delete <code>(901,234)</code> from <code>friendReq</code> and add that relationship to <code>friends</code>. There are no profile pages here--no Facebook-style "respond to friend request" option. It's a simple follower-based app whereby friendships are requested by simply typing their username and clicking add.</p>
  
  <p>My functioning SQL for simply adding a friend request without checking for the stuff I just described:</p>
  
  <pre><code>INSERT INTO friendReq ( initBy, reqTo)
      VALUES ($userid, (SELECT id FROM users WHERE username = '$addFriend') );
  </code></pre>
  
  <p>(<code>$addFriend</code> is gotten from the username that that a given user types in to send the friend request; please no comments on preventing SQL injection, this is just my simplified sample.)</p>
  
  <p>Pseudo-code, supposing <code>234</code> sends a request to <code>901</code>:</p>
  
  <pre><code>IF (234, 901) DOES NOT EXIST
      INSERT INTO friendReq ( initBy, reqTo)
          VALUES ( 234, 901 )
  IF (901, 234) EXISTS
      INSERT INTO friends (901, 234),
      DELETE FROM friendReq (901, 234);
  </code></pre>
  
  <p>My apologies if my explanation is not very clear. Any suggestions?</p>
  
  <hr>
  
  <p><strong>Solution</strong>:
  I'm not sure if this is SOP around here, but for anyone else looking, below is what I ended up doing and it works. I'm sure there are some efficiency improvements to be done (for example, building the various query strings from other strings instead of retyping many parts of it, as well as using multi_query), but for a simple solution, hopefully someone finds this of use:</p>
  
  <pre><code>$query = "SELECT * FROM friendReq
            WHERE initBy = (SELECT id FROM users WHERE username = '$addFriend')
            AND reqTo = $userid";
  if ( mysqli_num_rows(mysqli_query($conn, $query) ) == 1 ) {
      $query = "DELETE FROM friendReq
                WHERE initBy = (SELECT id FROM users WHERE username = '$addFriend')
                AND reqTo = $userid";
      mysqli_query($conn, $query);
      $query = "INSERT INTO friends (user1, user2)
                VALUES ($userid, (SELECT id FROM users WHERE username = '$addFriend') )";
      mysqli_query($conn, $query);
      die();
  } else {
      $query = "INSERT INTO friendReq (initBy, reqTo)
                VALUES ($userid, (SELECT id FROM users WHERE username = '$addFriend') )";
      mysqli_query($conn, $query);
      die();
  }
  </code></pre>