php - 即使用户没有在注册表单上注册，我的 PID 也会自动递增

Question

我已经创建了一个注册表单，它工作得很好，但现在的问题是，即使用户由于通用用户名而无法注册，PID 仍然会增加。

这是 HTML 表单:

<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>

这是 PHP 代码:

 <?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";
            }
         }
    }
​
?>

这是我创建的表:

这是表的列的属性:

Answer 1

这是MySQL INNODB的一个特性。请参阅Why does MySQL autoincrement increase on failed inserts?了解详情。为了避免这个问题，您应该在发送 INSERT 命令之前询问数据库是否可以插入记录。只需尝试从玩家表中选择该记录，并且仅当您没有收到任何记录时，才发送 INSERT。

旁注:您很容易受到 SQL 注入(inject)攻击。您需要在查询中使用准备好的语句，而不是使用 $_REQUEST 对象。