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

2021-12-25 00:00:00 php mysql mysqli mysqli-multi-query

我以前从未使用过 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 :)

相关文章