mysqli_stmt :: execute()返回false，语句未执行 [英] mysqli_stmt::execute() returning false and statement not executing

问题描述

通过将用户的IP，失败的尝试次数和最后的尝试时间记录到名为bannedusers的MySQL数据库表中，我有一种简单的登录系统保护机制.但是，当我尝试使用以下代码在数据库中插入新条目时，execute()函数将返回false并无法执行.

I have a simple login system protection mechanism by recording the user's IP, failed attempt number and last attempt time to a MySQL database table named bannedusers. However when I attempt to use the following code below to insert a new entry into the database the execute() function returns false and fails to execute.

代码如下:

private $con;

function updateTable($IP, $attempt, $exists){
    $time = time();

    //The following statement is actually in the constructor, moved here for completeness
    $this->con = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME); //All these constants are predefined and verified to be correct.

    if($this->con->connect_error){
        return true; //If there is a connection error, just let the user log in... We'll deal with this later
    }

    //Another function already determines if the entry exists or not.
    if(!$exists){
        //ip, retrycount, attempttime are the name of the fields. IP is a 40-char wide VARHCAR, retrycount is a tinyint and attempttime is a big int.
        $query = "INSERT INTO bannedusers (ip, retrycount, attempttime) VALUES (?,?,?)";

        if($stmt = $this->con->prepare($query)){

            //This following statement executes without throwing errors and returns true.
            $stmt->bind_param('sii', $IP, $attempt, $time);

            $successful = $stmt->execute();
            $stmt->close();

            if(!$successful){
                //Causes a small dialog to appear telling you the query failed.
                echo "<script type='text/javascript'>alert('Failed query!');</script>";
            }
        }
    }else{
        //Unrelated code omitted.
    }
}

我对php和MySQL还是很陌生，通过研究我发现SQL语法显然需要在查询的VALUE部分的字段周围加上引号，例如:

I'm rather new to php and MySQL and through research I have found that the SQL syntax apparently needs quotation marks around the fields for the VALUE section of the query like:

$query = "INSERT INTO bannedusers (ip, retrycount, attempttime) VALUES ('?','?','?')";

但是我发现此处，它实际上停止了从工作中查询(仍然尝试过并在bind_param()上收到错误).我尝试将类型更改为'sii'或'sss'或'ssi'，这都导致查询失败.我尝试在SQL查询的末尾添加分号，但这没有任何改变.在所有情况下，查询失败！"对话框弹出，没有其他错误(除了上面提到的错误之外，我在VALUES字段周围使用了引号.

but I have found here that it actually stops the query from working (still tried it and got a error on bind_param()). I've tried changing the type to 'sii' or 'sss' or 'ssi' all which resulted in the query failing. I've tried adding a semicolon at the end of the SQL query but that changed nothing. In all cases the "failed query!" dialog box pops up with no other error (except the one mentioned above give I use quotation marks around the VALUES fields.

感谢您的帮助.

更新:

事实证明，$ip在传递给该函数之前是某种原因null，如果将MySQL错误级别提高到MYSQLI_REPORT_ALL，该错误只会导致错误.

It turned out that $ip was somehow null before being passed into the function which only causes a error if the MySQL error level is raised to MYSQLI_REPORT_ALL.

推荐答案

将mysqli设置为异常模式

Either set mysqli into Exception mode

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

或始终检查每个mysqli操作的结果并手动引发mysqli错误:

or always check the result of every mysqli operation and throw mysqli error manually:

$result = $stmt->execute();
if (!$result) {
    throw new Exception($mysqli->error);
}

这是唯一的方式，用于了解您的execute()出了什么问题；

this is the only way to know what's wrong with your execute();

我发现SQL语法显然需要在VALUE的字段周围加上引号

I have found that the SQL syntax apparently needs quotation marks around the fields for the VALUE

当然是错误的. SQL语法显然只需要在字符串周围加上引号.

Of course it is wrong. SQL syntax apparently needs quotation marks around strings only.

这篇关于mysqli_stmt :: execute()返回false，语句未执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！