Insert data (only 1 time) in while loop using PHP

Question

I have the PHP code as below:

<?php
    if(isset($_POST["tbn_submit"])){
        $userName = $_POST["text_username"];
        $pass = $_POST["text_password"];
        //$sql = "SELECT * FROM tbluser WHERE username='".$userName."' AND password='".$pass."'";
        $sql = "SELECT * FROM tbluser";
        $res = mysql_query($sql) or die(mysql_error());
        if(mysql_num_rows($res)>0){
            while($row= mysql_fetch_array($res)){
                $username=$row['username'];
                $userpas = $row['password'];
                $user_id=$row['userId'];
                $user_role=$row['possition'];
                $_SESSION['username'] = $username;
                $_SESSION['uid'] = $user_id;
                if($userName == $username && $pass == $userpas){
                    if($user_role=="Admin"){
                        echo'<script>window.location="admin_project.php?uid='.$user_id.'";</script>';

                    }else{
                        echo'<script>window.location="user_project.php?uid='.$user_id.'";</script>';
                    }
                }
                else if($userName == $username && $pass != $userpas){
                    echo "<span style='text-align:center;color:red;'>Wrong password.</span>";
                }
                else if($userName != $username && $pass != $userpas){
//In this point I got insert multi time so I want it insert only 1 time to database
                    $query = "INSERT INTO tbluser(userId,username,password,possition,user_status) VALUES('','".$userName."','".$pass."','',1)";
                    $result = mysql_query($query) or die(mysql_error());
                    $id = mysql_insert_id();
                    if($result){
                        echo'<script>window.location="user_project.php?uid='.$user_id.'";</script>';
                    }
                }
            }
        }else {
            echo "re";

        }
    }
?>

This is my login page submission. When the user inputs their username and password, if the username and password are already in the database it will go to test some case like in code, but if the username is in the database but the password does not match it should display wrong password..

If the username and password don't exist in the database, the program should create username and password and go to other page. I have an error with this last case - I have inserted a lot of records in the database with the same data. I know it's because I wrote these entries in a while loop in my code but I don't know any other way of doing this. How can I populate my database with individual records and not write duplicate entries in my while loop?

Answer 1

All your logic is wrong:

1. there's no need to retrieve ALL the users to check if the user exists,
2. tbluser should restrict username to be UNIQUE to avoid duplicated entries,
3. passwords should be hashed,
4. the INSERT query uses unescaped variables,
5. inserting non matching user names will lead to have typos stored at the db,
6. mysql_* family of functions are deprecated

Using PDO

Login user

<?php

$dbh = new PDO('mysql:host=localhost;dbname=some_database_name', $user, $pass);

if (isset($_POST["login"])) {
    $user = $_POST["username"];
    $pass = $_POST["password"];

    $statement = $dbh->prepare("SELECT * FROM tbluser WHERE username=:user");
    $statement->bindParam(':user',$user);
    $statement->execute();

    /**
     * Returns FALSE in case nothing is found
     */
    $res = $statement->fetch(PDO::FETCH_ASSOC);

    if ($res) {
        $username = $res['username'];
        $password = $res['password'];
        $user_id = $res['userId'];
        $user_role = $res['possition'];

        if ($pass == $password) {
            $_SESSION['username'] = $username;
            $_SESSION['uid'] = $user_id;

            if ($user_role == "Admin") {
                echo'<script>window.location="admin_project.php?uid='.$user_id.'";</script>';
            }
            else {
                echo'<script>window.location="user_project.php?uid='.$user_id.'";</script>';
            }
        }
        else {
            echo "<span style='text-align:center;color:red;'>Wrong password.</span>";
        }
    }
    else {
        echo "<span style='text-align:center;color:red;'>Wrong username.</span>";
    }
}

Register user

<?php

$dbh = new PDO('mysql:host=localhost;dbname=some_database_name', $user, $pass);

if (isset($_POST["register"])) {
    $user = $_POST["username"];
    $pass = $_POST["password"];
    $check = $_POST["passcheck"];

    $statement = $dbh->prepare("SELECT * FROM tbluser WHERE username=:user");
    $statement->bindParam(':user',$user);
    $statement->execute();

    /**
     * Returns FALSE in case nothing is found
     */
    $res = $statement->fetch(PDO::FETCH_ASSOC);

    if ($res) {
        echo "<span style='text-align:center;color:red;'>Username exists.</span>";
    }
    else if ($pass != $check) {
        echo "<span style='text-align:center;color:red;'>Password check doesn't match.</span>";
    }
    else {
        $statement = $dbh->prepare("INSERT INTO tbluser (userId, username, password, position, user_status) VALUES ('', :user, :pass, '' , 1)");
        $statement->bindParam(':user',$user);
        $statement->bindParam(':pass',$pass);
        $statement->execute();

        echo "<span style='text-align:center;color:red;'>Username registered.</span>";
    }
}

Using mysql_query (deprecated)

To validate any user:

<?php

if (isset($_POST["login"])) {
    $user = $_POST["username"];
    $pass = $_POST["password"];

    /**
     * This line had the right idea!
     */
    $sql = "SELECT * FROM tbluser WHERE username='".mysql_real_escape_string($user)."'";
    $res = mysql_query($sql) or die(mysql_error());

    if (mysql_num_rows($res) > 0) {
        $username = $row['username'];
        $password = $row['password'];
        $user_id = $row['userId'];
        $user_role = $row['possition'];

        if ($pass == $password) {
            $_SESSION['username'] = $username;
            $_SESSION['uid'] = $user_id;

            if ($user_role == "Admin") {
                echo'<script>window.location="admin_project.php?uid='.$user_id.'";</script>';
            }
            else {
                echo'<script>window.location="user_project.php?uid='.$user_id.'";</script>';
            }
        }
        else {
            echo "<span style='text-align:center;color:red;'>Wrong password.</span>";
        }
    }
    else {
        echo "<span style='text-align:center;color:red;'>Wrong username.</span>";
    }
}

To register some user:

<?php

if (isset($_POST["register"])) {
    $user = $_POST["username"];
    $pass = $_POST["password"];

    /**
     * Ask the user to type its password twice
     */
    $check = $_POST["passcheck"];

    $sql = "SELECT * FROM tbluser WHERE username='".mysql_real_escape_string($user)."'";
    $res = mysql_query($sql) or die('The application found a problem and cannot process your request'); // die(mysql_error());

    if (mysql_num_rows($res) > 0) {
        echo "<span style='text-align:center;color:red;'>Username exists.</span>";
    }
    else if ($pass != $check) {
        echo "<span style='text-align:center;color:red;'>Password check doesn't match.</span>";
    }
    else {
        $query = "INSERT INTO tbluser (userId, username, password, possition, user_status) VALUES ('','".mysql_real_escape_string($user)."','".mysql_real_escape_string($pass)."','',1)";
        $res = mysql_query($sql) or die('The application found a problem and cannot process your request'); // die(mysql_error());
        echo "<span style='text-align:center;color:red;'>Username registered.</span>";
    }
}