具有多个查询的php/mysql
具有多个查询的php/mysql
提问于 2012-05-16 08:44:49
<?php
$query1 = "CREATE VIEW current_rankings AS SELECT * FROM main_table WHERE date = X";
$query2 = "CREATE VIEW previous_rankings AS SELECT rank FROM main_table WHERE date = date_sub('X', INTERVAL 1 MONTH)";
$query3 = "CREATE VIEW final_output AS SELECT current_rankings.player, current_rankings.rank as current_rank LEFT JOIN previous_rankings.rank as prev_rank
ON (current_rankings.player = previous_rankings.player)";
$query4 = "SELECT *, @rank_change = prev_rank - current_rank as rank_change from final_output";
$result = mysql_query($query4) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
echo $row['player']. $row['current_rank']. $row['prev_rank']. $row['rank_change'];
}
?>所有的查询都是独立工作的，但我真的很难把所有的部分放在一个单一的结果中，这样我就可以在mysql_fetch_array中使用它了。
我尝试过创建视图和临时表，但每次它要么显示table不存在，要么返回一个空的fetch数组，loop...logic在那里，但语法混乱，我想这是我第一次不得不处理多个查询，我需要将所有查询合并在一起。期待着一些支持。非常感谢。
回答 3
Stack Overflow用户
回答已采纳
发布于 2012-05-19 18:16:06
多亏了php.net，我想出了一个解决方案:您必须使用(mysqli_multi_query($link, $query))来运行多个连接查询。
/* create sql connection*/
$link = mysqli_connect("server", "user", "password", "database");
$query = "SQL STATEMENTS;"; /* first query : Notice the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS"; /* last query : Notice the dot before = at the end ! */
/* Execute queries */
if (mysqli_multi_query($link, $query)) {
do {
/* store first result set */
if ($result = mysqli_store_result($link)) {
while ($row = mysqli_fetch_array($result))
/* print your results */
{
echo $row['column1'];
echo $row['column2'];
}
mysqli_free_result($result);
}
} while (mysqli_next_result($link));
}编辑-如果你真的想执行一个大的查询，上面的解决方案是有效的，但也可以执行你想执行的任意多个查询，并分别执行它们。
$query1 = "Create temporary table A select c1 from t1";
$result1 = mysqli_query($link, $query1) or die(mysqli_error());
$query2 = "select c1 from A";
$result2 = mysqli_query($link, $query2) or die(mysqli_error());
while($row = mysqli_fetch_array($result2)) {
echo $row['c1'];
} Stack Overflow用户
发布于 2012-05-16 08:49:20
您似乎没有执行$query1 - $query3。您刚刚跳到了$query4，如果没有先执行其他代码，它就不会工作。
也是
$query4 = "SELECT *, @rank_change = prev_rank - current_rank as rank_change from final_output";可能应该是
$query4 = "SELECT *, @rank_change := prev_rank - current_rank as rank_change from final_output";否则，rank_change的值将只是一个布尔值，如果@rank_change等于(prev_rank - current_rank)，则为true，否则为false。但是你真的需要@rank_change吗？您将在后续查询中使用它吗？也许你可以把它完全删除。
更好的是，您可以将所有查询合并为一个查询，如下所示：
SELECT
curr.player,
curr.rank AS current_rank,
@rank_change := prev.rank - curr.rank AS rank_change
FROM
main_table AS curr
LEFT JOIN main_table AS prev
ON curr.player = prev.player
WHERE
curr.date = X
AND prev.date = date_sub('X', INTERVAL 1 MONTH)Stack Overflow用户
发布于 2012-05-16 08:56:34
您应该将它们连接起来：
<?php
$query = "CREATE VIEW current_rankings AS SELECT * FROM main_table WHERE date = X";
$query .= " CREATE VIEW previous_rankings AS SELECT rank FROM main_table WHERE date = date_sub('X', INTERVAL 1 MONTH)";
$query .= " CREATE VIEW final_output AS SELECT current_rankings.player, current_rankings.rank as current_rank LEFT JOIN previous_rankings.rank as prev_rank
ON (current_rankings.player = previous_rankings.player)";
$query .= " SELECT *, @rank_change = prev_rank - current_rank as rank_change from final_output";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
echo $row['player']. $row['current_rank']. $row['prev_rank']. $row['rank_change'];
}
?>页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/10610675
复制相关文章
相似问题
腾讯云开发者
