Finding Existing Email and username in database

Question

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.

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.

I hope someone understand me.

    <?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");

?>

score 0 · Answer 1 · answered Jul 21 '16 at 19:46

Generally, you want constraints like this handled at the database level. With MySQL, you can add a unique index to the username and email columns. When a duplicate gets entered, the query will fail.

You can then check to see if a duplicate occurred and handle it accordingly.

try {

   // Attempt to insert user here

} catch (Exception $e) {

   // It failed! Check if username/email exists so we can confirm the exception is this
   // The following is pseudocode -- you will need to write your own
   if (username_exists() || email_exists() {

      // Generate alternative names
      $names = generateNames($username);

      // Send this to your view or however you are displaying info
      echo("Name is taken!<br>You can try one of these:<br>");
      echo("<ul>");

      foreach ($names as $name) {
         echo("<li>$name</li>");
      }

      echo("</ul>");

   }
}

Please can you show how make this easier for me, i'm still a newbie in php — Oluwatobiloba, Jul 24 '16 at 18:05

score -1 · Accepted Answer · edited May 23 '17 at 11:51

-1

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

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.

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.

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');
}

edited May 23 '17 at 11:51

Community

1
1

answered Jul 21 '16 at 19:46

Philipp

2,792
2
25
27

This site is not a free coding service. This snippet shows you, HOW you can do what you want to do. Though, if you give me an error message or describe how it did not work I can help you debug it. – Philipp Jul 22 '16 at 18:01
No error ,The duplicate user details got written into the database – Oluwatobiloba Jul 22 '16 at 19:29
Can you please edit your question and add the code you are currently using? – Philipp Jul 22 '16 at 19:34
I have updated the code to the new one I'm currently using , which search for both username and password , but i want to print the error out **separately** – Oluwatobiloba Jul 22 '16 at 19:51
Well, I need to see how you implemented my snippet to find the error – Philipp Jul 22 '16 at 19:52
Check the txt file here [link](http://etechchef.tk/code.txt) ,Thanks for your help – Oluwatobiloba Jul 24 '16 at 18:42
Oh damn I am stupid. `if($error !== null){` has to be `if($error === null){` – Philipp Jul 24 '16 at 19:06
Can you please show me how i can use mysqli prepared statements with this code?? – Oluwatobiloba Jul 28 '16 at 14:27
Hey, yes of course! But could you first see this answer: http://stackoverflow.com/a/38444951/3595565 this shows how to use prepared statements to check if the email already exists. I documented every step, but I used the procedural syntax in this example, you would have to change it a little to use the object syntax. – Philipp Jul 28 '16 at 20:00
Just see if you can do it yourself if you try to follow the example, if it doesn't work I can help you tomorrow to show how it works in the object syntax – Philipp Jul 28 '16 at 20:01

Finding Existing Email and username in database

2 Answers2

Linked