PHP and MySQL - Check if username is already taken

Question

The problem now is, the code couldn't check if the username is already taken, is there any code that can check the username is already taken in the database?

I am experimenting with some of my codes then probably searched it also in Stack Overflow about this issue. I've tried this solution but apparently it gives me errors. When I've tried it, the warning:

mysql_query() expects parameter 2 to be resource, object given in C:... on line ...

and the problem

mysql_num_rows() expects parameter 1 to be resource, null given in C:... on line ...

I am actually looking for a solution for this problem or a tutorial how to solve this problem.

My code:

<?php
    require_once("functions.php");
    require_once("db-const.php");
    session_start();
    if (logged_in() == true) {
        redirect_to("profile.php");
    }
?>
<?php
 ?>
<html>
<head>
    <title>Prospekt Member Area</title>
</head>
<body>
<h1> Register Here </h1>
<h2>&copy; Kirk Niverba</h2>
<hr />
<!-- The HTML registration form -->
<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
    Username: <input type="text" name="username" /><br />
    Password: <input type="password" name="password" /><br />
    First name: <input type="text" name="first_name" /><br />
    Last name: <input type="text" name="last_name" /><br />
    Email: <input type="type" name="email" /><br />

    <input type="submit" name="submit" value="Register" />
    <a href="login.php">Already have an account?</a>
</form>
<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    if (empty($_POST['username']) || empty($_POST['password']) || empty($_POST['first_name']) || empty($_POST['last_name']) || empty($_POST['email'])) {
        echo "Please fill all the fields!";
    }
elseif (isset($_POST['submit'])) {
## connect mysql server
    $mysqli = new mysqli(localhost, root, "", loginsecure);
    # check connection
    if ($mysqli->connect_errno) {
        echo "<p>MySQL error no {$mysqli->connect_errno} : {$mysqli->connect_error}</p>";
        exit();
    }
## query database
    # prepare data for insertion
    $username   = $_POST['username'];
    $mainpass = $_POST['password'];
    $password   = hash('sha256', $mainpass);
    $first_name = $_POST['first_name'];
    $last_name  = $_POST['last_name'];
    $email      = $_POST['email'];

    # check if username and email exist else insert
    // u = username, e = emai, ue = both username and email already exists
    $user = $_POST['username'];
  $usernamecheck=mysql_query("SELECT username FROM users WHERE username='$user'", $mysqli);
        if (mysql_num_rows($usernamecheck)>=1){
    echo $user." is already taken";
 }
    else{
        # insert data into mysql database
        $sql = "INSERT  INTO `users` (`id`, `username`, `password`, `first_name`, `last_name`, `email`)
                VALUES (NULL, '{$username}', '{$password}', '{$first_name}', '{$last_name}', '{$email}')";

        if ($mysqli->query($sql)) {
            header("Location: checklogin.php?msg=Registered Successfully!");
        } else {
            echo "<p>MySQL error no {$mysqli->errno} : {$mysqli->error}</p>";
            exit();
        }
    }
}
}
?>
<hr />
</body>
</html>

you are mixing `mysqli` and `mysql` functions - keep to one or the other, prefably `mysqli` and use prepared statements rather than directly embedding variables in the sql. Also I notice that your use of curly braces is a bit "hit or miss" - I suspect the logic isn't working correctly in the code because you do not seem to balance them — Professor Abronsius, Jul 30 '16 at 07:51
Warning: Your code is vulnerable to [SQL injection attacks](https://en.wikipedia.org/wiki/SQL_injection). Please read [this post](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) to learn more on how to prevent it. — Pang, Jul 30 '16 at 07:51
virtualAon please check the answer below and mark and up-vote one answer as accepted. you can up-vote others if they are useful too. thkan — Alive to die - Anant, Jul 30 '16 at 08:49
@Pang Yeah, I pretty knew that this is vulnerable to SQLi attacks, but I — virtualAnon, Jul 30 '16 at 12:36

score 3 · Answer 1 · answered Jul 30 '16 at 07:55

3

In the lines you are getting errors, replace "mysql_query" for "mysqli_query" and "mysql_num_rows" for "mysqli_num_rows". This is because you cannot mix mysql calls with mysli connections.

answered Jul 30 '16 at 07:55

Federico Martinez

417
2
10

I've tried it, but it shows: Warning: mysqli_query() expects parameter 1 to be mysqli, string given in C:\... on line 55 – virtualAnon Jul 30 '16 at 13:11
Because mysqli_query expects the parameters in inverse order. The $mysqli as first parameter and the query string as the second parameter. – Federico Martinez Jul 30 '16 at 18:40

Professor Abronsius · Accepted Answer · 2016-07-30T15:00:43.013

As an example of how to use prepared statements you might be able to make use of the following ( not tested btw )

In the original code you were sending headers after outputting html code - this will cause an error unless you use output buffering so I moved all the relevant PHP code before any html content is generated and if there are any errors output them later.

I noticed also that the parameters for the mysqli connection were not quoted - if these were defined as constants then that would be fine, otherwise that too would have generated errors.

Keep to mysqli or pdo - as you can better protect your sites from malicious users when you adopt prepared statements like I have attempted to show here.

<?php
    require_once("functions.php");
    require_once("db-const.php");
    session_start();

    if (logged_in() == true) {
        redirect_to("profile.php");
    }

    $errors=array();

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

        if( isset( $_POST['username'], $_POST['password'], $_POST['first_name'], $_POST['last_name'], $_POST['email'] ) ) {

            $username   = !empty( $_POST['username'] ) ? $_POST['username'] : false;
            $mainpass   = !empty( $_POST['password'] ) ? $_POST['password'] : false;
            $password   = !empty( $mainpass ) ? hash('sha256', $mainpass) : false;
            $first_name = !empty( $_POST['first_name'] ) ? $_POST['first_name'] : false;
            $last_name  = !empty( $_POST['last_name'] ) ? $_POST['last_name'] : false;
            $email      = !empty( $_POST['email'] ) ? $_POST['email'] : false;

            if( $username && $password ){
                $mysqli = new mysqli( DB_HOST, DB_USER, DB_PASS, DB_NAME );
                if( $mysqli->connect_errno ) {
                    $errors[]=$mysqli->connect_error;
                } else {

                    /* Assume all is ok so far */
                    $sql='select username from users where username=?';
                    $stmt=$mysqli->prepare($sql);
                    $stmt->bind_param('s',$username);
                    $stmt->execute();

                    $stmt->bind_result( $found );
                    $stmt->fetch();

                    if( !$found ){
                        /* username is not alreday taken */

                        $sql='insert into `users` (`username`,`password`,`first_name`,`last_name`,`email`) values (?,?,?,?,?);';
                        $stmt=$mysqli->prepare( $sql );
                        $stmt->bind_param('sssss',$username,$password,$first_name,$last_name,$email);
                        $stmt->execute();

                        header("Location: checklogin.php?msg=Registered Successfully!");
                    } else {
                        /* username is taken */
                        $errors[]='Sorry, that username is already in use.';
                    }
                }   
            }
        } else {
            $errors[]='Please fill in all details';
        }
    }
?>
<html>
    <head>
        <title>Prospekt Member Area</title>
    </head>
    <body>
        <h1> Register Here </h1>
        <h2>&copy; Kirk Niverba</h2>
        <hr />

        <!-- The HTML registration form -->
        <form action="<?=$_SERVER['PHP_SELF']?>" method="post">
            Username: <input type="text" name="username" /><br />
            Password: <input type="password" name="password" /><br />
            First name: <input type="text" name="first_name" /><br />
            Last name: <input type="text" name="last_name" /><br />
            Email: <input type="type" name="email" /><br />

            <input type="submit" name="submit" value="Register" />
            <a href="login.php">Already have an account?</a>
        </form>
        <?php
            if( !empty( $errors ) ){
                echo implode( '<br />', $errors );
            }
        ?>
        <hr />
    </body>
</html>

Thanks for the code, I really appreciate it. but I've encountered problems in it. I couldn't register, the problem is: Fatal error: Call to a member function bind_param() on boolean in C:\... on line 40, and when the fields are not filled out, the error is not showing up ("Please fill all in the fields!") — virtualAnon, Jul 30 '16 at 13:09
I notice there is an included file `db-const.php` ~ have you defined, in that file, constants pertaining to server,user,password and db? In your original code it appears they are constants but I, prehaps wrongly, assumed they had simply been unquoted in the mysqli constructor. If they are defined as constants, remove the quotes from the constructor arguments as you had it originally and try that. — Professor Abronsius, Jul 30 '16 at 13:44
The code is in "constant" as you say. These are the codes: `` — virtualAnon, Jul 30 '16 at 13:48
Your code is now working, but the issue is, how does I implement it into other fields (like email addresses) and how to check if the fields are all filled out? Thanks for the effort tho — virtualAnon, Jul 31 '16 at 00:13

score 1 · Answer 3 · answered Jul 30 '16 at 07:55

1

It can be easily handeld with mysql unique constraint.

username varchar(50) NOT NULL,
//other fields,
UNIQUE (username)

For the php part of it, you can use mysql_query to select the desired username to validate and send form only if mysql_num_rows returns 0.

answered Jul 30 '16 at 07:55

Alexandre Martin

1,472
5
14
27

That's basically good advice, but you shouldn't use the mysql-extension for PHP, because it is deprecated. Use PDO or MySQLi instead. Also, you should use `SELECT COUNT(*)` – Stefan Jul 30 '16 at 08:42

PHP and MySQL - Check if username is already taken

3 Answers3

Linked