Checking if row exists under criteria (PDO, prepare???)

Question

The code below indicates my attempts to try and find out whether a row exists with the criteria gave in the code. It defaults to the else statement, correctly, but doesn't work with the 'if' statement if the if statement appears to be true (there are no emails down as ashfjks@sdhja.com), and instead the code proceeds. The latter part of this code is mostly to expand on the situation. the row can only exist or not exist so I don't understand why it's not strictly doing one or the other. I am converting into PDO for site secuirty, thats why not all is in PDO, yet. I am sorry if this question is too localised?

$stmt = $pdo->prepare("SELECT * FROM table WHERE email = ?");
$stmt->execute(array("$email"));
$row3 = $stmt->fetch(PDO::FETCH_ASSOC);

while($row = $stmt->fetch()) {

  if ( ! $row3) {
    // Row3 doesn't exist -- this means no one in the database has this email, allow the person to join
    $query = "INSERT INTO table (username, email, password, join_date) VALUES ('$username', '$email', SHA('$password1'), NOW())";
    mysqli_query($dbc, $query); 
    $query = "SELECT * FROM table WHERE username = '$username'";
    $data2 = mysqli_query($dbc, $query);
  while ($row = mysqli_fetch_array($data2)) {

  $recipent = '' . $row['user_id'] . '';

    $query = "INSERT INTO messages (recipent, MsgTit, MsgR, MsgA, sender, time, readb, reada, MsgCon) VALUES ('$recipent', '$MsgTit', '$MsgR', '$MsgA', '$sender', NOW(), '$readb', '$reada', '$MsgCon')";
    mysqli_query($dbc, $query);

    // Aftermath.
    echo '<p>Your new account has been successfully created. You\'re now ready to <a href="game2.php" target="_blank">log in</a>. After this you should implement basic character-details on your users profile to begin the game.</p>';

    mysqli_close($dbc);
    exit();
  } }  
  else {
    // An account already exists for this email, so display an error message
    echo '<p class="error">An account already exists for this e-mail.</p>';
    $email = "";
  }
}

Answer 1

+1 to answer from @Geoff_Montee, but here are a few more tips:

• Make sure you check for errors after every prepare() or execute(). Report the error (but don't expose your SQL to the user), and fail gracefully.

• Note that even though you checked for existence of a row matching $email, such a row could be created in the brief moment of time since your check and before you INSERT. This is a race condition. Even if you SELECT for a row matching $email, you should also use a UNIQUE constraint in the database, and catch errors when you execute the INSERT in case the UNIQUE constraint blocks the insert due to conflict.

• SELECT email instead of SELECT *. If you have an index on email, then the query runs more efficiently because it can just check the index for the given value, instead of having to read all the columns of the table when you don't need them. This optimization is called an index-only query.

• Likewise use SELECT user_id instead of SELECT *. Use SELECT * only when you really need to fetch all the columns.

• Bcrypt is more secure than SHA for hashing passwords.

Answer 2

Your if statement will never be executed. You need to check the number of rows returned. This is what you want:

Note: I originally used $stmt->rowCount(), but the OP said that didn't work for him. But I'm pretty sure the cause of that error was coming from somewhere else.

if (!($stmt = $pdo->prepare("SELECT * FROM table WHERE email = ?"))) {
   //error
}

if (!$stmt->execute(array("$email"))) {
    //error
}
//The $row3 var you had was useless. Deleted that.

$count = 0;

while ($row = $stmt->fetch()) {
    $count++;
}

//The query returned 0 rows, so you know the email doesn't exist in the DB
if ($count== 0) {

    $query = "INSERT INTO table (username, email, password, join_date) VALUES ('$username', '$email', SHA('$password1'), NOW())";

    if (!mysqli_query($dbc, $query)) {
        //error
    }

    $query = "SELECT * FROM table WHERE username = '$username'";

    if (!($data2 = mysqli_query($dbc, $query))) {
        //error
    }

    while ($row = mysqli_fetch_array($data2)) {

        $recipent = '' . $row['user_id'] . '';

        $query = "INSERT INTO messages (recipent, MsgTit, MsgR, MsgA, sender, time, readb, reada, MsgCon) VALUES ('$recipent', '$MsgTit', '$MsgR', '$MsgA', '$sender', NOW(), '$readb', '$reada', '$MsgCon')";

        if (!mysqli_query($dbc, $query)) {
            //error
        }

       // Aftermath.
       echo '<p>Your new account has been successfully created. You\'re now ready to <a href="game2.php" target="_blank">log in</a>. After this you should implement basic character-details on your users profile to begin the game.</p>';

       mysqli_close($dbc);
       exit();
   }
}
//The query did not return 0 rows, so it does exist in the DB
else {
    // An account already exists for this email, so display an error message
    echo '<p class="error">An account already exists for this e-mail.</p>';
    $email = "";
}

And you should totally convert the rest of those queries to use PDO.