具有多个查询的 php/mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10610675/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
php/mysql with multiple queries
提问by Naim
<?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'];
}
?>
All the queries work independently but am really struggling putting all the pieces together in one single result so I can use it with mysql_fetch_array.
所有的查询都是独立工作的，但我真的很难将所有的部分放在一个单一的结果中，所以我可以将它与 mysql_fetch_array 一起使用。
I've tried to create views as well as temporary tables but each time it either says table does not exist or return an empty fetch array loop...logic is there but syntax is messed up I think as it's the 1st time I had to deal with multiple queries I need to merge all together. Looking forward to some support. Many thanks.
我试图创建视图以及临时表，但每次它要么说表不存在，要么返回一个空的获取数组循环......逻辑在那里，但语法混乱，我认为这是我第一次不得不这样做处理多个查询我需要合并在一起。期待一些支持。非常感谢。
回答by Naim
Thanks to php.net I've come up with a solution : you have to use (mysqli_multi_query($link, $query))to run multiple concatenated queries.
感谢 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));
}
回答by Okonomiyaki3000
It seems you are not executing $query1 - $query3. You have just skipped to $query4 which won't work if the others have not been executed first.
您似乎没有执行 $query1 - $query3。您刚刚跳到 $query4 ，如果其他人没有先执行，它将不起作用。
Also
还
$query4 = "SELECT *, @rank_change = prev_rank - current_rank as rank_change from final_output";
should probably be
应该是
$query4 = "SELECT *, @rank_change := prev_rank - current_rank as rank_change from final_output";
or else the value of rank_change will just be a boolean, true if @rank_change is equal to (prev_rank - current_rank), false if it is not. But do you need @rank_change at all? Will you use it in a subsequent query? Maybe you can remove it altogether.
否则 rank_change 的值将只是一个布尔值，如果@rank_change 等于 (prev_rank - current_rank)，则为 true，否则为 false。但是你真的需要@rank_change 吗？您会在后续查询中使用它吗？也许你可以完全删除它。
Even better, you could just combine all the queries into one like this:
更好的是，您可以将所有查询合并为一个，如下所示：
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)
回答by Paul Dessert
You should concatenate them:
你应该连接它们：
<?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'];
}
?>
