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?

`"INSERT INTO tbluser(userId,username,password,possition,user_status) VALUES('','".$userName."','".$pass."','',1)"` there wasn't any space before VALUES` — sas, Jan 06 '14 at 03:30
You're using sessions, and there is no mention of `session_start();` --- Is it included in all your files? If it isn't then you need to include it on top, otherwise your code won't work. Plus I do not see any DB connection code, make sure you are connected to it. — Funk Forty Niner, Jan 06 '14 at 03:33
My problem is that: If the username and password don have in database it will create a new one by insert data to database but in my code it insert multi record into database so I don want like this I want insert only one time , but I don know what I need to do. — user3024562, Jan 06 '14 at 03:39
It's inserting multiple records because you have your `INSERT` inside your `while` loop. You need to restructure your code, or end your `while` loop right after `$_SESSION['uid'] = $user_id;` — Funk Forty Niner, Jan 06 '14 at 04:04
**By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Jan 06 '14 at 04:08

Ast Derek · Accepted Answer · 2014-01-08T06:45:46.120

All your logic is wrong:

there's no need to retrieve ALL the users to check if the user exists,
tbluser should restrict username to be UNIQUE to avoid duplicated entries,
passwords should be hashed,
the INSERT query uses unescaped variables,
inserting non matching user names will lead to have typos stored at the db,
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>";
    }
}

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

1 Answers1

Using PDO

Login user

Register user

Using mysql_query (deprecated)

Using `mysql_query` (deprecated)