无法弄清楚如何运行mysqli_multi_query并使用上一次查询的结果 [英] Cannot figure out how to run a mysqli_multi_query and use the results from the last 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
}
?>
我要做的就是运行一个多重查询，然后使用这四个查询的最终结果并将它们放入我的表中.
上面的代码真的根本不起作用，我只收到以下错误:
警告:mysqli_fetch_array()期望 参数1为mysqli_result， 布尔值 C:\ xampp \ htdocs \ hydroboard \ hydro_reporting_2010.php 在第391行
有什么帮助吗?
好吧，经过反复试验，反复试验，并参考了我在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:
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\hydroboard\hydro_reporting_2010.php on line 391
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并使用上一次查询的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
