Oluwatobiloba Oluwatobiloba - 1 year ago 82
SQL Question

Finding Existing Email and username in database

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.

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


Answer Source

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