virtualAnon virtualAnon - 3 months ago 10
MySQL Question

PHP and MySQL - Check if username is already taken

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>

Answer

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>