无法弄清楚如何运行mysqli_multi_query并使用上一次查询的结果

一尘不染

无法弄清楚如何运行mysqli_multi_query并使用上一次查询的结果

mysql

我以前从未使用过mysqli_multi_query，它使我感到困惑，我在网上发现的任何例子都无法帮助我弄清楚我到底想做什么。

这是我的代码：

<?php

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

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", 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

    }
?>

我要做的就是运行一个多重查询，然后使用这四个查询的最终结果并将它们放入我的表中。

上面的代码真的根本不起作用，我只是收到以下错误：

警告：mysqli_fetch_array（）期望参数1为mysqli_result，在第391行的C：\ xampp \ htdocs \
hydroboard \ hydro_reporting_2010.php中给出的布尔值

有什么帮助吗？

阅读 309

2020-05-17

共1个答案

一尘不染

好吧，经过反复试验，反复尝试，并参考了我在Google搜索中遇到的另一篇文章的参考，我已经设法解决了我的问题！

这是新的代码：

<?php

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

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", 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>\nQuery: $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的结果，这是我要使用的结果。

虽然对我来说似乎有点笨拙，但应该只提供诸如mysqli_last_result（$ link）之类的命令，或者如果您问我一个问题…

感谢rik和f00的帮助，我最终到达了:)

2020-05-17