How would I fetch for data for an email and a username?

Question

I have found out how to fetch for a username, however how would I do this with an email? Because, I want to add two separate error messages for an email and a username

$sql = "SELECT uid_users FROM users WHERE uid_users=?";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
header("Location: ../register.php?error=sqlerror");
exit();
}
else {
mysqli_stmt_bind_param($stmt, "s", $username);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$resultCheck = mysqli_stmt_num_rows($stmt);

if ($resultCheck > 0) {
  header("Location: ../register.php?error=usertaken&mail=".$email);
  exit();
}

*"How would I fetch for data on another column?"* - With an additional clause. — Funk Forty Niner, Jul 28 '20 at 21:37
@B001ᛦ What does that have to do with the question about email? — Barmar, Jul 28 '20 at 21:37
@Barmar you are right. I actually was talking about the uid_users _..way to find if a user has entered the same username..._ Okay, I need more coffee — B001ᛦ, Jul 28 '20 at 21:38

score 3 · Accepted Answer · answered Jul 28 '20 at 21:37

Use OR to check another column.

$sql = "SELECT uid_users FROM users WHERE uid_users=? OR email = ?";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
    header("Location: ../register.php?error=sqlerror");
    exit();
}
else {
    mysqli_stmt_bind_param($stmt, "ss", $username, $email);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_store_result($stmt);
    $resultCheck = mysqli_stmt_num_rows($stmt);

    if ($resultCheck > 0) {
        header("Location: ../register.php?error=usertaken&mail=".$email);
        exit();
    }
}

Note that this won't tell them whether it was the username or email that was already taken. If you want that, you should just do two separate queries, one that looks for a duplicate username, another that looks for a duplicate email.

Or you could change the query to SELECT uid_users, email and fetch the results of the query. Then check whether the fetched username or email matches the input, and display an appropriate error.

score 2 · Answer 2 · edited Jul 28 '20 at 22:12

2

You can use the OR operator for this:

   SELECT uid_users 
      FROM users 
     WHERE uid_users=? 
        OR email=?";```

edited Jul 28 '20 at 22:12

Strawberry

33,750
13
40
57

answered Jul 28 '20 at 21:37

Dimich

359
1
9

score 0 · Answer 3 · answered Jul 28 '20 at 21:50

If you want to check if a record exists in the database that matches both username and email then you should use WHERE uid_users=? AND email=?.

If you want to check if a a record exists in the database that matches either username or email, then use WHERE uid_users=? OR email=?.

Small note, you don't need to fetch the data if you only want to check existence of the record in DB. Simply let MySQL tell you the number of matching record. Use COUNT() for this purpose.

$stmt = $conn->prepare("SELECT COUNT(1) FROM users WHERE uid_users=? OR email=?");
$stmt->bind_param('ss', $username, $email);
$stmt->execute();
// Fetch value of COUNT(1) from DB
$resultCheck = $stmt->get_result()->fetch_row()[0];

if ($resultCheck) {
    header("Location: ../register.php?".http_build_query(['error' => 'usertaken', 'mail' => $email]));
    exit();
}

How would I fetch for data for an email and a username?

3 Answers3