Making sure that an username doesn't exit already with PHP

Question

Im trying to making sure than when an user introduce a username in a form, that username is not already in my Database.

When I introduce a username that already exist, I get the error message: "* Username already exist". However, the username it is introduce in the database and therefore I have duplication in my database.

Also, anytime I update(F5) the browser a NULL record gets introduce in my database.

THANK YOU SO MUCH!!! :)

Below is the code I'm using:

<!DOCTYPE HTML>
<html>
<head>
<style>
.error {color: #FF0000;}
</style>
</head>
<body>

<?php
// define variables and set to empty values
$usernameErr = "";
$username = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {

   if (empty($_POST["username"])) {
 $usernameErr = "Username is required";
   } else {
 $username = test_input($_POST["username"]);
// check if name only contains letters and whitespace
if (!preg_match("/^[a-zA-Z0-9 ]*$/",$username)) {
  $usernameErr = "Only letters, numbers and white space allowed";
    }
  }
}

function test_input($data) {
   $data = trim($data);
   $data = stripslashes($data);
   $data = htmlspecialchars($data);
   return $data;
}
?>



<?php

   if ($usernameErr == '') 
    {

    $db = pg_connect('host=localhost dbname=test user=myuser password=mypass');
    $username = pg_escape_string($_POST['username']);
    $query = "SELECT username FROM host";
    $result = pg_query($query);
    if (!$result) {
        echo "Problem with query " . $query . "<br/>";
        echo pg_last_error();
        exit();
    }


while($myrow = pg_fetch_assoc($result)) {
     if ($username == $myrow[username]) {$usernameErr = "Username already exist";}
     else { $query = "INSERT INTO host(username) VALUES('" . $username . "')";}

     printf ("$myrow[username]>");

    }

   $result = pg_query($db, $query);
         if (!$result) {
         $errormessage = pg_last_error();
             echo "Error with query: " . $errormessage;
         exit();
         }

    $username = "";

    pg_close();

 }
?>


<p><span class="error">* required field.</span></p>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
   <input type="text" name="username" placeholder="User Name" value="<?php echo $username;?>">
   <span class="error">* <?php echo $usernameErr;?></span>
   <br><br>
   <input type="submit" name="submit" value="SAVE">
</form>


</body>
</html>

score 3 · Answer 1 · answered Mar 16 '16 at 21:57

3

The problem is in the while statement; when it finds a coincidence in the database, it prints the 'User already exists' message, but instead of stopping the loop, it keeps comparing the username against the other records, which generate the inserted record. Change your while statement code to this:

while($myrow = pg_fetch_assoc($result)) {
    if ($username == $myrow[username]) {
        $usernameErr = "Username already exist";
        break;
    }
    else {
        $query = "INSERT INTO host(username) VALUES('" . $username . "')";

        $result = pg_query($db, $query);
        if (!$result) {
            $errormessage = pg_last_error();
            echo "Error with query: " . $errormessage;
            exit();
        }

        $username = "";
    }

    printf ("$myrow[username]>");
}

This should work for you. You can also define a unique constraint for the username column in your database.

answered Mar 16 '16 at 21:57

Eduardo Galván

962
7
15

"define a unique constraint for the username column" is a very good idea. I might also suggest some sort id field or making the field the PK. – cfnerd Mar 16 '16 at 22:08
@Eduardo Galvan. Thank you so much for your answer. I tested and that will work as far as the username repeated is the one in the first record of my database. However, If I introduce for example username: Eduardo, and it is already in my database as the 2sd or 3rd record, another record will be introduce with username: Eduardo. Thank you again – Samuel Mar 21 '16 at 18:35
@Samuel you're right. Actually a better solution is to include a WHERE statement with the username you're trying to find in the SELECT you use to fetch the usernames from the host tables. Something like `SELECT username FROM host WHERE username=$1`. Check the [pg_prepare php function](http://php.net/manual/en/function.pg-prepare.php). Also check (http://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already). The question was asked for Python but the SQL statement is what I'm looking at here. – Eduardo Galván Mar 21 '16 at 18:53

score 0 · Answer 2 · answered Mar 21 '16 at 18:52

ok, here is the solution that I found ant it is working for me.

Thank you

    $usernamefound = false;

 while($myrow = pg_fetch_assoc($result)) {
     if ($username == $myrow[username]) {
    $usernameErr = "Username already exist";
            $usernamefound = true;
    break;                
    }
     }
    if ($usernamefound == false) { 
             $query = "INSERT INTO host(username) VALUES('" . $username . "')";

             $result = pg_query($db, $query);
         if (!$result) {
           $errormessage = pg_last_error();
               echo "Error with query: " . $errormessage;
           exit();
          }

           $username = "";

       }

     printf ("$myrow[username]>");

Making sure that an username doesn't exit already with PHP

2 Answers2