PDO Username validation if already exists

Question

I have a problem with register form.My form works properly but whenever i try to insert username that already exists it doesn't shows any error.

here is my php register file:

<?php
$servername = "localhost";
$username = "root";
$password = "";

try {
    $conn = new PDO("mysql:host=$servername;dbname=dblogin", $username, $password);

    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    if (isset($_POST['submit'])) {
        $user_name = $_POST['user_name'];
        $user_email = $_POST['user_email'];
        $user_pass = $_POST['user_pass'];
        $hash = password_hash($user_pass, PASSWORD_DEFAULT);

        $stmt = $con->prepare("SELECT user_name FROM users WHERE user_name = :user_name");

        if($stmt->rowCount() > 0){
        echo "exists!";
        }

       else{  
        $insert = $conn->prepare("INSERT INTO users (user_name,user_email,user_pass) values(:user_name,:user_email,:user_pass)");

        $insert->bindparam(':user_name',$user_name);
        $insert->bindparam(':user_email',$user_email);
        $insert->bindparam(':user_pass',$hash);


        $insert->execute();    
      }
    }
 catch(PDOException $e)
    {
    echo "connection failed";
    }


?> 

Thanks for your support

Answer 1

You are not executing the select statement. You need to bind params and execute the select statement, try this after the select statemnt.

$stmt->bindparam(':user_name',$user_name);
$stmt->execute();

Answer 2

 public function usernameCheck($username)
{

    $sql = "SELECT * FROM $this->table where username = :username";
    $query = $this->pdo->prepare($sql);
    $query->bindValue(':username', $username);
    $query->execute();
    if ($query->rowCount() > 0) {
        return true;

    } else {
        return false;
    }

}

use this one in your project hope it will work... :)

Answer 3

missing } in if statement

if (isset($_POST['submit'])) {
        $user_name = $_POST['user_name'];
        $user_email = $_POST['user_email'];
        $user_pass = $_POST['user_pass'];
        $hash = password_hash($user_pass, PASSWORD_DEFAULT);

        $stmt = $con->prepare("SELECT user_name FROM users WHERE user_name = :user_name");

        if($stmt->rowCount() > 0){
        echo "exists!";
        }
}else{

}

Answer 4

I notice 4 things (2 of which have been mentioned by others):

First and smallest is you have a spelling error ($con instead of $conn) - don't worry it happens to the best of us - in you first $stmt query which means your select-results becomes NULL instead of 0 - so you rowCount find that it is not over 0 and moves on without your error message

Second you forgot to bind and execute the parameters in your first $stmt query which gives the same result for your rowCount results

Third always clean your variables even when using prepared statements - at a bare minimum use

$conn->mysql_real_escape_string($variable);

and you can with advantage use

htmlspecialchars($variable);

And fourth since you are not doing anything with the database (other than looking) you could simplify your code by simply writing:

$stmt = $conn->query("SELECT user_name FROM users WHERE user_name = '$user_name' LIMIT 1")->fetch();

as I said - no need to bind or execute in the first query

and as a general rule - don't use rowCount - ever - if you have to know the number of results (and in 99% of cases you don't) use count(); but if you as here just want to know if anything at all was found instead use:

if ( $stmt ) {
    echo "exists!";
} else {
    // insert new user as you did
}

Edit:

Also - as a side note - there are a few things you should consider when you initially create your connection...

Ex:

// Set variables
$servername = "localhost";
$username = "***";
$password = "***";
$database = "***";
$charset = 'utf8'; // It is always a good idea to also set the character-set

// Always create the connection before you create the new PDO
$dsn = "mysql:host=$servername;dbname=$database;charset=$charset"; 

// Set default handlings as you create the new PDO instead of after
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // And add default fetch_mode
    PDO::ATTR_EMULATE_PREPARES   => false, // And ALWAYS set emulate_prepares to false
];

// And now you are ready to create your new PDO
$conn = new PDO($dsn, $username, $password, $opt);

Just a suggestion... happy trails