时间戳

首页 > 解决方案 > 在 SELECT 查询之前 INSERT 未完成

php - 在 SELECT 查询之前 INSERT 未完成

问题描述

我有一个 PHP 脚本，它被分成两个单独的 PHP 脚本（因为它们每个都有一个目的并且很长）。为简单起见，我们称它们为 1.php 和 2.php。

脚本 1.php 对网站进行 API 调用，将有效负载传递给函数。一旦截断并将新记录插入到表中，它就会包含第二个脚本。这就是问题开始的地方。似乎当我查询 marketPlace 表时，它返回一个空数组，但是如果我在包含 2.php 之前插入一个 sleep(1) ，它就可以工作！我只能总结一下，在调用下一个查询之前，1.php 中的截断和插入查询尚未完成？（我以前从未遇到过这个！）。

只有一个数据库连接，由包含在 1.php 中的数据库类定义：

class Database
{

    // This class allows us to access the database from any function with ease
    // Just call it with Database::$conn

    /** TRUE if static variables have been initialized. FALSE otherwise
    */
    private static $init = FALSE;
    /** The mysqli connection object
    */
    public static $conn;
    /** initializes the static class variables. Only runs initialization once.
    * does not return anything.
    */
    public static function initialize()
    {


        Global $servername;
        Global $username;
        Global $password;
        Global $dbname;

        try {

            if (self::$init===TRUE)return;
            self::$init = TRUE;
            self::$conn = new mysqli($servername, $username, $password, $dbname);

        }

        catch (exception $e) {
            date('Y-m-d H:i:s',time()) . " Cant' connect to MySQL Database - re-trying" . PHP_EOL;
        }
    }

    public static function checkDB()
    {

        if (!mysqli_ping(self::$conn)) {
            self::$init = FALSE;
            self::initialize();
        }

    }

}

截断并插入市场的函数是：

function processMarketplace($marketData) {

    // Decode to JSON
    $outputj = json_decode($marketData, true);
    $marketplaceCounter = 0;

    // Check for success
    if (($outputj['success']==true) && (!stristr($marketData, "error"))) {

        // Create the blank multiple sql statement
        $sql = "TRUNCATE marketplace;";  // Clears down the current marketPlace table ready for new INSERTS

        //Loop through each multicall
        foreach ($outputj['multiCall'] as $orderBook) {
            foreach ($orderBook['marketplace'] as $orderLine) {

                $type = $orderLine['type'];
                $price = $orderLine['amountCurrency'];

                // Add new SQL record (This ignores any duplicate values)
                $sql .="INSERT IGNORE INTO marketplace (type, price) VALUES ('" . $type . "'," . $price . ");";

            }

            $marketplaceCounter++;

        }

        // Now run all the SQL's to update database table
        if (strlen($sql) > 0) {
            if (Database::$conn->multi_query($sql) === TRUE) {
                 echo mysqli_error(Database::$conn);
                //echo "New records created successfully";
            } else {
                echo mysqli_error(Database::$conn);
                echo "Error: " . $sql . "<br>" . Database::$conn->error;
            }
        }

        echo date('Y-m-d H:i:s',time()) . " == Marketplace Orderbook retreived == <BR><BR>" . PHP_EOL;

    } else {

        echo date('Y-m-d H:i:s',time()) . " Failed to get Marketplace data. Output was: " . $marketData . "<BR>" . PHP_EOL;
        die();
    }

}

我已经追了好几个小时了，我真的不明白为什么在调用函数sleep(1)后添加延迟会有所帮助。processMarketplace()我还尝试将 1.php 和 2.php 合并为一个脚本，这会产生相同的结果。2.php 只是做一个SELECT * FROM marketPlace查询，除非我有sleep(1).

我错过了一些简单的事情还是我真的很糟糕？

我应该添加我正在使用 InnoDB 表。

这就是它在 1.php 中的调用方式：

$marketData = getData($user,$api); // Get Marketplace Data
processMarketplace($marketData); // Process marketplace data
sleep(1); // Bizzare sleep needed for the select statement that follows in 2.php to return non-null
include "2.php"; // Include 2nd script to do some select statements on marketPlace table

2.php 包含以下调用：

$typeArray = array('1','2','3');
foreach ($typeArray as $type) {

    initialPopulate($type);

}

函数初始填充（$类型）{

// Reset supplementary prices
mysqli_query(Database::$conn, "UPDATE marketPlace SET price_curr = '999999' WHERE type='" . $type . "'");
echo mysqli_error(Database::$conn);

// Get marketplace data <--- This is the one that is strangely returning Null (after the first loop) unless I place the sleep(1) before including 1.php
$query = "SELECT * FROM marketPlace WHERE type='" . $type . "'";
$result = mysqli_query(Database::$conn, $query);echo mysqli_error(Database::$conn);
$resultNumRows = mysqli_num_rows($result);echo mysqli_error(Database::$conn);

// Create array from mysql data
$rows = array();
while($r = mysqli_fetch_assoc($result)) {
    $rows[] = $r;
}

// Get information from the offertypes table
$query2 = "SELECT offerID FROM queryTypes WHERE type='" . $type . "'";
$result2 = mysqli_query(Database::$conn, $query2);echo mysqli_error(Database::$conn);

// Create array from mysql data
$rows2 = array();
while($r2 = mysqli_fetch_row($result2)) {
    $rows2[] = $r2;
}

// Loop through marketplace data and apply data from the offertypes table
$sql1 = "";     // Create a blank SQL array that we will use to update the database
$i = 0;
foreach ($rows as $row) {

    $sql1 .= "UPDATE marketPlace SET enrichmentType = " . $rows2[$i][0] . " WHERE type='" . $type . "';";
    $i++;
}

// Now run all the SQL's to update database table
if (strlen($sql1) > 0) {
    if (Database::$conn->multi_query($sql1) === TRUE) {
         echo mysqli_error(Database::$conn);
        //echo "New records created successfully";
    } else {
        echo mysqli_error(Database::$conn);
        echo "Error: " . $sql1 . "<br>" . Database::$conn->error;
    }
}

}

标签： phpmysqlmysqli

解决方案

您正在使用mysqli:multi_query. 与 不同query，multi_query 不会立即检索结果。检索结果必须使用mysqli::use_result

文档中的一个示例：



/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->use_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->close();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

您不需要打印结果，但如果您不检索它们，则不能保证 INSERT 已完成。

请注意https://www.php.net/manual/en/mysqli.use-result.phpuse_result的 文档

它指出

“必须在检索查询结果之前调用此函数或 mysqli_store_result() 函数，并且必须调用其中一个或另一个以防止该数据库连接上的下一个查询失败。”

由于未调用 store_result 或 use_result，您将得到不可预测的结果。

推荐阅读