問題描述

我以前從未使用過 mysqli_multi_query，這讓我很困惑，我在網上找到的任何例子都無法幫助我弄清楚我想要做什么.

I've never used mysqli_multi_query before and it's boggling my brain, any examples I find on the net aren't helping me to figure out exactly what it is I want to do.

這是我的代碼:

<?php

    $link = mysqli_connect("server", "user", "pass", "db");

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s
", mysqli_connect_error());
        exit();
    }

    $agentsquery = "CREATE TEMPORARY TABLE LeaderBoard (
        `agent_name` varchar(20) NOT NULL,
        `job_number` int(5) NOT NULL,
        `job_value` decimal(3,1) NOT NULL,
        `points_value` decimal(8,2) NOT NULL
    );";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`, `job_number`, `job_value`, `points_value`) SELECT agent_name, job_number, job_value, points_value FROM jobs WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`) SELECT DISTINCT agent_name FROM apps WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "SELECT agent_name, SUM(job_value), SUM(points_value) FROM leaderboard GROUP BY agent_name ORDER BY SUM(points_value) DESC";

    $i = 0;
    $agentsresult = mysqli_multi_query($link, $agentsquery);

    while ($row = mysqli_fetch_array($agentsresult)){
        $number_of_apps = getAgentAppsWeek($row['agent_name'],$weeknum,$current_year);
        $i++;
?>

            <tr class="tr<?php echo ($i & 1) ?>">
                <td style="font-weight: bold;"><?php echo $row['agent_name'] ?></td>
                <td><?php echo $row['SUM(job_value)'] ?></td>
                <td><?php echo $row['SUM(points_value)'] ?></td>
                <td><?php echo $number_of_apps; ?></td>
            </tr>

<?php

    }
?>

我要做的就是運行多個查詢，然后使用這 4 個查詢的最終結果并將它們放入我的表中.

All I'm trying to do is run a multiple query and then use the final results from those 4 queries and put them into my tables.

上面的代碼根本不起作用，我只是收到以下錯誤:

the code above really doesn't work at all, I just get the following error:

警告:mysqli_fetch_array() 期望參數 1 為 mysqli_result，給出的布爾值C:xampphtdocshydroboardhydro_reporting_2010.php在線 391

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:xampphtdocshydroboardhydro_reporting_2010.php on line 391

有什么幫助嗎?

推薦答案

好的，經過一些擺弄、反復試驗并參考了我在 Google 搜索中遇到的另一篇文章，我已經設法解決了我的問題！

Okay after some fiddling around, trial and error and taking reference from another post that I came across in a Google search I've managed to solve my problem!

這是新代碼:

<?php

    $link = mysqli_connect("server", "user", "pass", "db");

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s
", mysqli_connect_error());
        exit();
    }

    $agentsquery = "CREATE TEMPORARY TABLE LeaderBoard (
        `agent_name` varchar(20) NOT NULL,
        `job_number` int(5) NOT NULL,
        `job_value` decimal(3,1) NOT NULL,
        `points_value` decimal(8,2) NOT NULL
    );";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`, `job_number`, `job_value`, `points_value`) SELECT agent_name, job_number, job_value, points_value FROM jobs WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`) SELECT DISTINCT agent_name FROM apps WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "SELECT agent_name, SUM(job_value), SUM(points_value) FROM leaderboard GROUP BY agent_name ORDER BY SUM(points_value) DESC";

    mysqli_multi_query($link, $agentsquery) or die("MySQL Error: " . mysqli_error($link) . "<hr>
Query: $agentsquery");
    mysqli_next_result($link);
    mysqli_next_result($link);
    mysqli_next_result($link);

    if ($result = mysqli_store_result($link)) {
        $i = 0;
        while ($row = mysqli_fetch_array($result)){
            $number_of_apps = getAgentAppsWeek($row['agent_name'],$weeknum,$current_year);
            $i++;
?>

            <tr class="tr<?php echo ($i & 1) ?>">
                <td style="font-weight: bold;"><?php echo $row['agent_name'] ?></td>
                <td><?php echo $row['SUM(job_value)'] ?></td>
                <td><?php echo $row['SUM(points_value)'] ?></td>
                <td><?php echo $number_of_apps; ?></td>
            </tr>

<?php

        }
    }
?>

在為每個查詢多次粘貼 mysqli_next_result 之后，它神奇地工作了！耶！我理解它為什么有效，因為我告訴它跳到下一個結果 3 次，所以它跳到查詢 #4 的結果，這是我想要使用的結果.

after sticking mysqli_next_result in there multiple times for each query it magically worked! yay! I understand why it works, because i'm telling it to skip to the next result 3 times, so it skips to the result for query #4 which is the one i want to use.

不過對我來說似乎有點笨拙，如果你問我，應該只需要一個類似 mysqli_last_result($link) 之類的命令......

Seems a bit clunky to me though, there should just be a command for something like mysqli_last_result($link) or something if you ask me...

感謝 rik 和 f00 的幫助，我終于到了 :)

Thanks for the help rik and f00, I got there eventually :)

這篇關于無法弄清楚如何運行 mysqli_multi_query 并使用上次查詢的結果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！