php - 先前插入的行在调用 PHP 脚本时消失

Question

我正在使用 MySQL (5.1.67)、InnoDB 作为存储引擎和 PHP (5.3.3) 开发体育经理应用程序的服务器端部分。

当我尝试对多个包含先前插入行的表执行包含任何类型查询的脚本时，这些行会自动消失。请注意，没有执行 DELETE 查询。

表 users 的示例:

我通过调用 add_user.php 脚本将行插入到 users 中:

<?php

function addUser($link, $username, $password, $email, $country, $president_id, $virtual)
{
    $response = array();

    $stmt = mysqli_stmt_init($link);
    if ($stmt = mysqli_prepare($link, "INSERT INTO users(username, password, email, country, president_id, virtual) VALUES(?, ?, ?, ?, ?, ?)"))
    {
        mysqli_stmt_bind_param($stmt, "ssssii", $username, $password, $email, $country, $president_id, $virtual);

        $result = mysqli_stmt_execute($stmt);

        if($result)
        {
            $response["success"] = 1;
            $response["message"] = "User successfully created";
            $response["id"] = mysqli_insert_id($link);

            echo json_encode($response);
        }
        else
        {
            $response["success"] = 0;
            $response["message"] = "Failed to create user";

            echo json_encode($response);
        }

        mysqli_stmt_close($stmt);
    }
}


require_once __DIR__ . '/db_config.php';

$link = mysqli_connect(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE);


mysqli_report(MYSQLI_REPORT_ALL);

if (isset($_POST['username']) && isset($_POST['password']) && isset($_POST['email']) && isset($_POST['country']) && isset($_POST['president_id']) && isset($_POST['virtual']))
{
    $username = $_POST['username'];
    $password = $_POST['password'];
    $email = $_POST['email'];
    $country = $_POST['country'];
    $president_id = $_POST['president_id'];
    $virtual = $_POST['virtual'];

    addUser($link, $username, $password, $email, $country, $president_id, $virtual);
}
else
{
    $response['script'] = "add_user";
    $response["success"] = 0;
    $response["message"] = "Required field(s) missing";

    echo json_encode($response);
}


mysqli_close($link);

?>

然后我通过调用 start_season.php 脚本对 users 执行一些查询(在下面的代码中我执行了一个通用的 INSERT 查询，因为结果行为是相同的):

<?php

require_once "./add_user.php";
require_once "./add_club.php";
require_once "./add_president.php";
require_once "./add_coach.php";
require_once "./add_stadium.php";
require_once "./compete.php";
require_once "./generate_fixtures.php";
require_once "./remove_manager.php";


require_once __DIR__ . '/db_config.php';

$link = mysqli_connect(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE);


mysqli_query($link, "SELECT * FROM users WHERE username LIKE 'user%'");


mysqli_close($link);

?>

就这样，users 中的所有先前行都消失了。我在这里缺少什么？

Answer 1

如果要将缓冲语句数据永久写入数据库，需要在mysqli_stmt_close()之前调用mysqli_stmt_store_result()。例如:

function addUser($link, $username, $password, $email, $country, $president_id, $virtual)
{
    $response = array();

    $stmt = mysqli_stmt_init($link);
    if ($stmt = mysqli_prepare($link, "INSERT INTO users(username, password, email, country, president_id, virtual) VALUES(?, ?, ?, ?, ?, ?)"))
    {
        mysqli_stmt_bind_param($stmt, "ssssii", $username, $password, $email, $country, $president_id, $virtual);

        $result = mysqli_stmt_execute($stmt);

        if($result)
        {
            $response["success"] = 1;
            $response["message"] = "User successfully created";
            $response["id"] = mysqli_insert_id($link);

            echo json_encode($response);
        }
        else
        {
            $response["success"] = 0;
            $response["message"] = "Failed to create user";

            echo json_encode($response);
        }

        mysqli_stmt_store_result($stmt);
        mysqli_stmt_close($stmt);
    }
}

The PHP manual因为 mysqli_stmt_close 告诉我们......

bool mysqli_stmt_close ( mysqli_stmt $stmt )

Closes a prepared statement. mysqli_stmt_close() also deallocates the statement handle. If the current statement has pending or unread results, this function cancels them so that the next query can be executed.

... 它描述了您所看到的确切行为。