在数据库中查找现有的电子邮件和用户名 [英] Finding Existing Email and username in database

问题描述

如何检查电子邮件或用户名是否已经存在于我的 mysql数据库中，而另一个用户正在注册，以便我可以停止注册进程并且可能建议新用户的数据库中不存在另外2/3个新的用户名。

How can i check if email or username already exist in my mysql database while another user is registering so i could stop the registration process and possible suggest another 2/3 new username that does not exist in the database for the new user.

所以我尝试了，我只能得到一个检查如果数据库中已经存在电子邮件，那么我需要一个在数据库中检查用户名和电子邮件的文件，并单独打印 $ error 。我的意思是，如果电子邮件存在，用户名不存在，它将只打印错误电子邮件和用户名相同。

so i tried working around and i could only get the one that check if email already exist in database meanwhile i need the one that will check for both username and email in database and print both $error separately. I mean if email exist and username does not exist it will only print the Error email and same for username.

我希望有人了解我。

    <?php
if(isset($_POST['register'])) {
  $username = $_POST['username'];
  $full_name = ucwords($_POST['full_name']);
  $email = $_POST['email'];
  $password = trim($_POST['password']);
  $time = time();
  $age = $_POST['age'];
  $gender = $_POST['gender'];

  // Geolocation
  $longitude = $_SESSION['longitude'];
  $latitude = $_SESSION['latitude'];

  $geo_info = $geo->getInfo($latitude,$longitude);
  $city = $geo_info['geonames'][0]['name'];
  $country = $geo_info['geonames'][0]['countryName'];

  $check_d = $db->query("SELECT username, email from users WHERE username = '$username' OR email = '$email'");
  $check_d = $check_d->num_rows;
  if($check_d == 0) {
    $db->query("INSERT INTO users (profile_picture,username,full_name,email,password,registered,credits,age,gender,ip,country,city,longitude,latitude) VALUES ('default_avatar.png','$username','$full_name','$email','".$auth->hashPassword($password)."','$time','100','$age','$gender','$ip','".$country."','".$city."','".$longitude."','".$latitude."')");
    setcookie('justRegistered', 'true', time()+6);
    setcookie('mm-email',$email,time()+60*60*24*30,'/');
    header('Location: '.$domain.'/people');
}
else { $error = 'Username or password already exist, Try Another';

  }
  }
if($auth->isLogged()) {
  $first_name = $system->getFirstName($_SESSION['full_name']);
  $logged_in_user = header('Location: '.$domain.'/people');
}

$users = $db->query("SELECT * FROM users ORDER BY RAND() LIMIT 7");

?>

推荐答案

您可以通过检查用户名和电子邮件分开的查询。

You can do it, by checking username and email in separated queries.

在我发布的实现中，我首先检查邮件，然后检查数据库中的用户名。如果这两个都已经在db中，则稍后的错误消息（对于用户名）将覆盖该邮件的消息。您应该更改这一点，以便为用户提供关于为什么无法将数据集插入表格的更准确的反馈。

In the implemenation I am posting I first check the mail, then check for the username in the database. If both are already in the db, the later errormessage (for the username) will overwrite the message for the email. You should change that, to give the user a more precise feedback about why the dataset could not be inserted into the table.

在进行任何检查之前，我正在初始化变量 $ error AS null ，因为如果其中一个约束检查失败，我可以简单地检查如果在确定是否执行 insert 之后仍然为空。

Before doing any of the checks, I am initializing the variable $error AS null, as it would get overwritten if one of the constraint checks fails, I can simply check if it is still null after the checks to decide whether the insert should be executed or not.

注意：你不应该使用这个脚本，因为它严重受到sql注入的影响。看到这个线程来了解你应该改变什么：如何防止SQL注入在PHP中？

NOTE: You should not use this script, as it is seriously vulnerable to sql injections. See this thread to learn what you should change: How can I prevent SQL injection in PHP?

$error = null;
$check_mail = $db->query("SELECT id FROM users WHERE email='" . $email . "'");
if ($check_mail->num_rows > 0) {
    $error = 'Email already exists';
}
$check_username = $db->query("SELECT id FROM users WHERE username='" . $username . "'");
if ($check_username->num_rows > 0) {
    $error = 'username already exists';
}
if($error === null){
    $db->query("INSERT INTO users (profile_picture,full_name,email,password,registered,credits,age,gender,ip,country,city,longitude,latitude) VALUES ('default_avatar.png','$full_name','$email','" . $auth->hashPassword($password) . "','$time','100','$age','$gender','$ip','" . $country . "','" . $city . "','" . $longitude . "','" . $latitude . "')");
    setcookie('justRegistered', 'true', time() + 6);
    setcookie('mm-email', $email, time() + 60 * 60 * 24 * 30, '/');
    header('Location: ' . $domain . '/people');
}

这篇关于在数据库中查找现有的电子邮件和用户名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！