无法弄清楚如何运行mysqli

无法弄清楚如何运行mysqli

本文介绍了无法弄清楚如何运行mysqli_multi_query并使用上一次查询的结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

    }
?>

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

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

有什么帮助吗?

解决方案

好吧,经过反复试验,反复试验,并参考了我在Google搜索中遇到的另一篇文章的参考文献,我已经设法解决了我的问题! /p>

这是新代码:

<?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的帮助,我终于到了:)

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.

Here is my code:

<?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

    }
?>

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:

any help?

解决方案

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!

Here's the new code:

<?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

        }
    }
?>

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.

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...

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

这篇关于无法弄清楚如何运行mysqli_multi_query并使用上一次查询的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-02 20:33