How to check if username already exist using PDO?

Question

am currently working on a project and i have the script for insertion.my table is called survey and the fields are id,username,password,province. the username is set to unique key. the insertion process is working fine without any duplicate entry but when i try to insert a duplicate entry at always shows me this error

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'bluff' for key 'username'

I know what this error mean, my problem is that how can i can if username already exist or not i want an alert message to pop up..

here is my code

class.user.php

 public function username($username){
 $stmt = $this->db->prepare("SELECT count(*) FROM tish_images WHERE username = :username");
 $stmt->execute(array($username));
 $number_of_rows = $result->fetchColumn();
 if($number_of_rows >= 1) {
     echo 'username does exist'; // or return so you get the value
 } else {
     echo 'username does not exist'; //also return?
 }
}
public function create($username,$password,$province)
{
try
{
  $stmt = $this->db->prepare("INSERT INTO tish_images(username,password,province) VALUES(:username, :password, :province)");
  $stmt->bindparam(":username",$username);
  $stmt->bindparam(":password",$password);
  $stmt->bindparam(":province",$province);
  $stmt->execute();
  return true;
}
catch(PDOException $e)
{
  echo $e->getMessage();  
  return false;
}
}

index.php

<?php
include_once 'DB.php';

$username = isset($_GET['username']) ? $_GET['username'] : '';
$password = isset($_GET['password']) ? $_GET['password'] : '';
$province = isset($_GET['province']) ? $_GET['province'] : '';

if(isset($_FILES['files'])){
$id = $_GET['id'];
$username = $_POST['username'];
$password = $_POST['password'];
$province = $_POST['province'];

if($crud->upload($id,$FILE_NAME,$FILE_SIZE,$FILE_TYPE,$username,$password,$province))
{
    echo "<script type='text/javascript'>alert('Successfully Updated!');</script>";
}
else
{
    echo "<script type='text/javascript'>alert('Updating Failed!');</script>";
}

}
if(isset($_GET['id']))
    {
    $id = $_GET['id'];
    extract($crud->getID($id));
    }

Run a select before the insert. If you have a value prompt the user to select another name. If not insert. Also you shouldn't store passwords in plaintext. — chris85, Aug 19 '15 at 00:21
Another option is to simply catch the exception and show / trigger the alert there. See these answers for an example ~ http://stackoverflow.com/a/21869475/283366 and http://stackoverflow.com/a/21618269/283366 — Phil, Aug 19 '15 at 00:22
@chris85 let me update my code because i have try to do that but still not working — kier, Aug 19 '15 at 00:33
You have a typo is that your exact code? `if(username == $row['username'])` You don't even need to do that though. If you get a result you know the username is already in the DB. Also you are executing twice, `$stmt->execute(array($username)); $stmt->execute();`...and binding twice... `$stmt->bindparam(":username", $username); $stmt->execute(array($username));` — chris85, Aug 19 '15 at 00:35
@Phil can you help me with this beacause i'm just new with PDO. — kier, Aug 19 '15 at 00:41
@chris85 of course your comment is very help and i even change it..can please help me to edit it? — kier, Aug 19 '15 at 00:56

score 1 · Answer 1 · edited May 23 '17 at 12:29

You should run a SELECT before performing the query to see if the username exists.

// count how many rows with user name exists
$checkUserStmt = $this->db->prepare("
    SELECT count(1) 
    FROM tish_images 
    WHERE username = :username
");
$checkUserStmt->execute(array(":username" => $username));

// fetch the count result
if ($checkUserStmt->fetchColumn() > 0) {
    // username already exists
} else {
    // username available 
} //if

A few notes.

You still might get a duplicate entry error if you have two users trying to register the same username at close interval.
You should hash the password see Secure hash and salt for PHP passwords

Where do i exactly put it?because i'm just new with PDO and how do i call it in my index.php — kier, Aug 19 '15 at 00:53

Daniel Bell · Answer 2 · 2015-08-19T01:12:35.277

0

To check if username or email already exists. I added email in there as this is also useful. You don't want two users with the same email address. Well I wouldn't see the need for it. :)

Complete code added and up to date.

        $query_check_user_name = $this->db_connection->prepare('SELECT user_name, user_email FROM users WHERE user_name=:user_name OR user_email=:user_email');
        $query_check_user_name->bindValue(':user_name', $user_name, PDO::PARAM_STR);
        $query_check_user_name->bindValue(':user_email', $user_email, PDO::PARAM_STR);
        $query_check_user_name->execute();
        $result = $query_check_user_name->fetchAll();
        if ($result > 0) {
           echo "Someone with that username/email already exists.";
        } else {
           //Continue with proccessing the form
        }

OR

            $query_check_user_name = $this->db_connection->prepare('SELECT user_name, user_email FROM users WHERE user_name=:user_name OR user_email=:user_email');
        $query_check_user_name->bindValue(':user_name', $user_name, PDO::PARAM_STR);
        $query_check_user_name->bindValue(':user_email', $user_email, PDO::PARAM_STR);
        $query_check_user_name->execute();
        $result = $query_check_user_name->fetchAll();
        if ($result > 0) {
           return true;
        } else {
           return false;
        }

edited Aug 19 '15 at 01:12

answered Aug 19 '15 at 00:38

Daniel Bell

9
4

1

`if (count($result) > 0) {` --- this check is redundant – zerkms Aug 19 '15 at 00:41
I've always been a fan of using a `SELECT 1...` query and then using `$stmt->fetchColumn()` – Phil Aug 19 '15 at 00:44
@Daniel Where do i exactly put it? – kier Aug 19 '15 at 00:50
Zerk you are correct. I left it like that so it would make sense. I'll quickly update it to better form in a moment. @kier Sounds like your new to user registration. What you need to do is first run isset on the posted data. Then a trim. This removes all white spaces. Then check if it's empty, strlen and preg_match. After that you want to add that code. To be more advanced you could have a separate function to mitigate server overhead sanitising / validating all other posted data but best to keep it simple for starting out. – Daniel Bell Aug 19 '15 at 01:02
@DanielBell how can i call that in index.php – kier Aug 19 '15 at 01:16
@kier to call it as you've put it. You have the reg form (index.php) in your case which is posted to process.php (example name). This code belongs in the process.php. So if your following: #1: Isset the data Belongs in process.php not index.php. Keep it clean. #2: Trim the data #3: Check if empty / strlen / preg_match #4: Add the above code or Chris85 example. His is a cleaner example. #5: Hash / Salt password #6: Insert said data if all the above is sweet. Hopefully that helps and gives you a little list to work through. – Daniel Bell Aug 19 '15 at 01:21

How to check if username already exist using PDO?

2 Answers2