我需要找到一个多查询的解决方案。
这是我的代码：
<?php
$link = mysqli_connect("localhost", "root", "nlpgroup", "testdb");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s'n", mysqli_connect_error());
exit();
}
//$query .= "";
$query = "insert into EM8
SELECT sourceindex,targetindex,source,target,new_count, new_count / sum_new_count as prob
FROM EM7, (
SELECT SUM(new_count) sum_new_count
FROM EM7
) sq;";
$query .= "insert into EM9
select sourceindex,source,sum(prob) as lundax from EM8 group by sourceindex;";
$query .= "insert into EM10 select `sourceindex`, `source`, `lundax` , pa/lundax alva
from
EM9
cross join (select exp(sum(ln(lundax))) pa
from EM9 ) t;";
$query .= "insert into EM11 select
t1.sourceindex
, t1.targetindex
, t1.source
, t1.target
, t1.prob as EM8_prob
, t1.prob*t2.alva as pa_alva
from EM8 t1
inner join EM10 t2 on t1.sourceindex = t2.sourceindex
;";
$query .= "insert into EM12
select x.sourceindex
,x.targetindex
,x.source
,x.target
,c as new_count
from EM11 x
join (SELECT GREATEST(source,target) g,LEAST(source,target) l,sum(pa_alva) c FROM EM11 GROUP BY g,l ) y
ON (y.g = x.source AND y.l = x.target)
OR (y.g = x.target AND y.l = x.source);";
//===========
/* execute multi query */
if (mysqli_multi_query($link, $query)) {
do {
/* store first result set */
if ($result = mysqli_store_result($link)) {
while ($row = mysqli_fetch_row($result)) {
printf("%s|%s|%s|%s|%s'n", $row[0],$row[1],$row[2],$row[3],$row[4]);
}
mysqli_free_result($result);
}
/* print divider */
if (mysqli_more_results($link)) {
printf("-----------------'n");
}
} while (mysqli_next_result($link));
}
/* close connection */
mysqli_close($link);
?>
从表EM8->EM12开始，我想做下一步。
INSERT INTO EM8
SELECT sourceindex,targetindex,source,target,new_count, new_count / sum_new_count as prob
FROM EM12, (
SELECT SUM(new_count) sum_new_count
FROM EM12
) sq
之后截断EM9->EM12
然后从EM8->EM12 继续
我如何循环浏览？
我不完全确定您希望如何构建查询，但我会尝试提供一个过程。（*顺便说一句，在我看来，你所有的查询都像INSERTS，所以不会有结果集要打印。）
使用php迭代表名以构建所有查询。
for($x=8; $x<13; ++$x){
$y=$x-1;
//dynamic query build:
$queries[]="INSERT INTO `EM$x`
SELECT sourceindex,targetindex,source,target,new_count,new_count/sum_new_count AS prob
FROM `EM$y`,
(SELECT SUM(new_count) sum_new_count FROM `EM$y`) sq";
// ... add all of your queries using $x and $y into the $queries array.
// ... add truncating query.
}
然后，在构建完所有查询之后，可以运行mysqli_multi_query（）
if(mysqli_multi_query($link,implode(';',$queries))){
do{
list($current_key,$current_query)=each($queries); //advances array pointer to first or next element
if(mysqli_affected_rows($link)<1){
$message="Logic Error @ Query:<br>$current_query";
break; // I assume you want to break when insert or delete fails
}
} while(mysqli_more_results($link) && mysqli_next_result($link));
}else{
list($current_key,$current_query)=each($queries); //advances array pointer to first element
}
if($error_mess=mysqli_error($link)){
$message="Syntax Error @ Query:<br>$current_query<br>Error Message:<br>$error_mess";
}
if(!$message){
echo "All queries successful!";
}else{
echo "$message";
}
很有可能我误解了你的部分/全部问题。希望这能有所帮助。如果需要更多帮助，请告诉我。