My PID is Auto-Incrementing even if the users doesn't get registered on the SignUp Form

Question

I have created a signup form and it is working perfectly but now the issue is even if the user are not able to register due to common username the PID still increases.

Here is the HTML form:

<form class="form-register" method="post" action="php/reg.php">

            <div class="form-register-with-email">

                <div class="form-white-background">

                    <div class="form-title-row">
                        <h1>Create an account</h1>
                    </div>

                    <div class="form-row">
                        <label>
                            <span>First Name</span>
                            <input type="text" name="first_name">
                        </label>
                    </div>

                    <div class="form-row">
                        <label>
                            <span>Last Name</span>
                            <input type="text" name="last_name">
                        </label>
                    </div>

                    <div class="form-row">
                        <label>
                            <span>Username</span>
                            <input type="text" name="username">
                        </label>
                    </div>

                    <div class="form-row">
                        <label>
                            <span>Email</span>
                            <input type="email" name="email">
                        </label>
                    </div>

                    <div class="form-row">
                        <label>
                            <span>Password</span>
                            <input type="password" name="password">
                        </label>
                    </div>

                    <div class="form-row">
                        <label class="form-checkbox">
                            <input type="checkbox" name="checkbox" checked>
                            <span>I agree to the <a href="#">terms and conditions</a></span>
                        </label>
                    </div>

                    <div class="form-row">
                        <button type="submit" name="submit">Register</button>
                    </div>

                </div>

                <a href="form-login.html" class="form-log-in-with-existing">Already have an account? Login here &rarr;</a>

            </div>

            <div class="form-sign-in-with-social">

                <div class="form-row form-title-row">
                    <span class="form-title">Sign in with</span>
                </div>

                <a href="#" class="form-google-button">Google</a>
                <a href="#" class="form-facebook-button">Facebook</a>
                <a href="#" class="form-twitter-button">Twitter</a>

            </div>

</form>

Here is the php code:

 <?php    
    include('connect.php');

    if(isset($_REQUEST['submit'])) {
        if($_REQUEST['first_name'] == '' || $_REQUEST['last_name'] == '' || $_REQUEST['username'] == ''|| $_REQUEST['email'] == '' || $_REQUEST['password'] === '' ) {
            echo "please fill the empty field.";
        } 
        else {
            $sql="SELECT pid FROM players WHERE username = '".$_REQUEST['username']."'";
            $res=mysql_query($sql);
            if (mysql_num_rows($res) == 0) { 
                $sql="insert into players(first_name,last_name,username,email,password) values('".$_REQUEST['first_name']."', '".$_REQUEST['last_name']."', '".$_REQUEST['username']."', '".$_REQUEST['email']."', '".$_REQUEST['password']."')";
            $res=mysql_query($sql);
            } else{
                echo "username was already used"; 
            }
            if($res) {
               echo "Record successfully inserted";
            }
            else {
               echo "There is some problem in inserting record";
            }
         }
    }

?>

Here is the table I created:

Here is the properties of the column of table:

score 1 · Accepted Answer · edited May 23 '17 at 12:30

1

This is a feature of MySQL INNODB. See Why does MySQL autoincrement increase on failed inserts? for details. To avoid this problem, you should ask the db if it's ok to insert the record before sending the INSERT command. Just try to SELECT that record from the players table and only if you did not receive any records, send the INSERT.

A side note: You are wide open to a SQL Injection attack. You need to use a prepared statement instead of using the $_REQUEST object in your query.

edited May 23 '17 at 12:30

Community

1
1

answered Oct 31 '15 at 13:55

Kelly Keller-Heikkila

2,544
6
23
35

I tried what you said, I tried to to select the recods and did not receive any records from any PID (except from PID=1 & PID=7). I also read the link you sent me but it still doesn't solve my problem. Is there any other way to rectify this? – Aman Tewary Oct 31 '15 at 14:08
Do you get the message `There is some problem in inserting record`? If not, then there is something else going on. If you are and you're sure the username is unique, then there is a different SQL error going on. You need to find out what that error is. My solution only solves the problem if it's caused by a duplicate username. – Kelly Keller-Heikkila Oct 31 '15 at 14:10
Yes I do get that message when registering with the username that already exists in the database. Also I tried this query for checking the various records 'select * from players where PID=2;' – Aman Tewary Oct 31 '15 at 14:13
Can you update your question with the code that is doing the SELECT? – Kelly Keller-Heikkila Oct 31 '15 at 14:14
I have no idea how will I add select with the PID in it as I am not using PID in the PHP code. – Aman Tewary Oct 31 '15 at 14:17
Try this: `"SELECT pid FROM players WHERE username = '" . $_REQUEST['username'] . "'"`. If that returns any records then don't INSERT. If no records are returned, then do the INSERT. – Kelly Keller-Heikkila Oct 31 '15 at 14:19
Will changing the Engine from INNODB solve this issue? – Aman Tewary Oct 31 '15 at 14:19
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/93872/discussion-between-devlin-and-brian-keller-heikkila). – Aman Tewary Oct 31 '15 at 14:30

score -1 · Answer 2 · answered Oct 31 '15 at 13:37

-1

It seems like you've deleted some records. However you can set next increment number by executing this query.

ALTER TABLE players AUTO_INCREMENT=2;

answered Oct 31 '15 at 13:37

Rizwan Khan

474
2
7
21

i haven't deleted any records. It was stored like that. The only thing i did @hi5place was trying to use same username which I made unique to see if it was working. You can think of it in this way that i tried to add the next user 6 times before it got registered and all the 6 times I imputed username as "thehellblazer7" which was already present in the database. – Aman Tewary Oct 31 '15 at 13:42
1

@Devlin Please share your signup form markup in your question. – Rizwan Khan Oct 31 '15 at 13:44

My PID is Auto-Incrementing even if the users doesn't get registered on the SignUp Form

2 Answers2