Checking if user & email exists on MYSQL with PHP

Question

I've searched around but I could not find something that works correctly. I have the inputs: username, password and email.

I want to check both username and email and know if they exists on database or not.

If it does, it gives na error, if it doesn't, it creates a new account.

So this is my code for now:

Forms

    <?php 
    function formulary() {
    ?>

    <section class="container">
        <div class="register">
            <h1>Create an account</h1>

            <form method="post">
                <p><input type="text" name="username" value="" placeholder="Username" maxlength="25"></p>
                <p><input type="password" name="password" value="" placeholder="Password" maxlength="25"></p>
                <p><input type="email" name="email" value="" placeholder="E-Mail" maxlength="50"></p>
                <div class="buttons">
                <input type="submit" name="register" value="Register">
                <input type="button" name="cancel" value="Cancel" onClick="window.location='login.php';">
                </div>
            </form>
        </div>
    </section>

    <?php
    }
    ?>

Check

        <?php
        function registNew() {
        $con = mysqli_connect("localhost","root","","work");
        $username = mysqli_real_escape_string($con , $_POST["username"]);
        $password = mysqli_real_escape_string($con , $_POST["password"]);
        $email = mysqli_real_escape_string($con , $_POST["email"]);

        if(mysqli_connect_errno())
        {
            echo "Error MySQL: " .mysqli_connect_errno();
        }

        $sqlUser = "SELECT * FROM users WHERE username = '".$username."'";
        $rs = mysqli_query($con ,$sqlUser);
        $numUsers = mysqli_num_rows($rs);

        if($numUsers > 0) {
            echo "User already exists<br/>";
        }
        else
        {
            $sqlEmail = "SELECT * FROM utilizadores WHERE email = '".$email."'";
            $rs = mysqli_query($con ,$sqlEmail);
            $numEmails = mysqli_num_rows($rs);

            if($numEmails > 0) {
                echo "E-Mail already exists<br/>";
            }
            else 
            {
                $newUser= "INSERT INTO utilizadores(username,password,email) VALUES('$username','$password','$email')";
                if(mysqli_query($con ,$newUser))
                {
                    echo "Account has been created!<br/>";
                    mysqli_close($con);
                    header('Location: login.php');
                }
                else
                {
                    echo "Error at adding user<br/>";
                    header("refresh:5;url=register.php");
                }
            }
        }
    }
    ?>

End

    <?php
    if(!isset($_SESSION)) {
        session_start();
    }

    if(!isset($_POST["register"]))
    {
        formulary();  
    }
    else
    {
        registNew();
    }
    ?>

The output of creating a non-existant account is:

Error at adding user

I'm quite new at PHP so I'm not sure what's wrong. Is it because I'm using the same variables for both username and e-mail (check/sql/etc variables) or I'm just doing this wrong?

Any idea why this is not working?

MatejG · Accepted Answer · 2015-03-11T11:37:37.953

0

I edited first part of your registNew function to check if there are any results. Everything is of course escaped so you are safe.

function registNew() {
        $con = mysqli_connect("localhost","root","","work");
        $username = mysqli_real_escape_string($con, $_POST["username"]);
        $password = mysqli_real_escape_string($con, $_POST["password"]);
        $email = mysqli_real_escape_string($con, $_POST["email"]);

        if(mysqli_connect_errno())
        {
            echo "Error MySQL: " .mysqli_connect_errno();
        }

        $rsUsers = mysqli_query($con,"SELECT * FROM users WHERE username = '".$username."'");
        $rsEmails = mysqli_query($con,"SELECT * FROM users WHERE email = '".$email."'");
        $numUsers = mysqli_num_rows($rsUsers);
        $numEmails = mysqli_num_rows($rsEmails);

        if($numUsers > 0 || $numEmails > 0) {
            echo "User already exists";
        }
        else
        {

                $newUser= "INSERT INTO users(username,password,email) VALUES('$username','$password','$email')";
                if(mysqli_query($con,$newUser))
                {
                    echo "Account has been created<br/>";
                    /* header('Location: login.php'); */
                }
                else
                {
                    echo "Error at adding user<br/>";
                    header("refresh:5;url=register.php");
                }

        }
    }

edited Mar 11 '15 at 11:37

answered Mar 11 '15 at 11:21

MatejG

1,393
1
17
26

May I ask, why "db_name" again? "work" is the dbname, also, what does real_Escape_string means? what does it do? I'll try to edit my code with this too and check it out. Thank you. – Ran Mar 11 '15 at 11:24
I'm srry. Overlooked empty string in parameter. Edited answer. rel_escape_string escapes special characters from strings so you are safe against SQL Injection. – MatejG Mar 11 '15 at 11:27
I edited the code to be like your but he checks only users and then emails (as you can see after the else i was trying to check the users), but in this case changed to your idea, but it gives me the same answer and says aswell: Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, string given in C:\xampp\htdocs\Publicidades\register.php on line 59 – Ran Mar 11 '15 at 11:31
Than there has to be some kind of error in query. I suggest to echo out SQL query and try to run it in phpmyadmin or any kind of software for MySQL you are using and see what's the error. I have edited your code again to get rid of unnecesarry second email check. – MatejG Mar 11 '15 at 11:35
Must be because of my database, will try to check it out. ty – Ran Mar 11 '15 at 11:44
It now adds, but it doesn't check if the username or email exists. I edited the post to my current code, may you check if you know what's wrong? Thank you – Ran Mar 11 '15 at 11:58
You are not checking number of emails from result set : $numEmails = mysqli_num_rows($rs); – MatejG Mar 11 '15 at 12:03

Checking if user & email exists on MYSQL with PHP

1 Answers1