CODEWITHSUNDEEP
phpmysqlmysqli
Maelish
Maelish

Reputation: 1629

Prepared statment to test which field is wrong with username or password

Checking to see if a record has matches with a WHERE is pretty common. But I was wondering this morning if it would be possible to discover if you had a partial match. If so, which one is right and which is wrong? So the user_name might be correct but not the email address.

Is there a way to do that without doing two different query statements? This is my starting code. Suggestions?

  $query = "SELECT user_name, user_email FROM user_list WHERE (user_name = ?) AND (user_email = ?)";
  $stmt = mysqli_prepare($link, $query);
  mysqli_stmt_bind_param($stmt, 'ss', $username, $email);

  if ($result = mysqli_stmt_execute($stmt)); 
    {
      mysqli_stmt_bind_result($stmt, $db_username, $db_email);
      $got_match = mysqli_stmt_fetch($stmt);    
      }

I'd appreciate if the answers were in a procedural method. :-)

Upvotes: 0

Views: 58

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

Well first of all, you would need to use an OR condition in the where clause for partial matching:

WHERE user_name = ? OR user_email = ?

Second, you would like need to add some CASE statements (or IF statements if you like) in the select to determine which items match:

SELECT
  user_name,
  (CASE WHEN user_name = ? THEN 1 ELSE 0 END CASE) AS user_name_match,
  user_email,
  (CASE WHEN user_email = ? THEN 1 ELSE 0 END CASE) AS user_email_match

So putting it all together

SELECT
  user_name,
  (CASE WHEN user_name = ? THEN 1 ELSE 0 END CASE) AS user_name_match,
  user_email,
  (CASE WHEN user_email = ? THEN 1 ELSE 0 END CASE) AS user_email_match
FROM user_list
WHERE user_name = ? OR user_email = ?

Note that you would need to bind to all 4 parameters.

As others have pointed out in the comments above, if your intent here is to tell the end user if only user_name or user_email match, this is probably not a good idea from a security standpoint.

Upvotes: 3

Related Questions