PHP/MySQL的多个查询 [英] php/mysql with multiple queries

问题描述

<?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一起使用.

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.

我试图创建视图以及临时表，但是每次它说表不存在或返回一个空的获取数组循环...逻辑存在，但语法混乱了，我认为这是第一次我不得不处理多个查询，我需要将所有查询合并在一起.希望得到一些支持.非常感谢.

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.

推荐答案

感谢php.net，我想出了一个解决方案:您必须使用(mysqli_multi_query($link, $query))来运行多个串联的查询.

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.

 /* 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));
}

这篇关于PHP/MySQL的多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！