Как я могу отобразить результаты этих двух запросов в одной строке. Что-то вроде 17/8 (Убийств / Смертей).
У меня есть два запроса, первый:
$result1 = mysql_query("SELECT PlayerTable.Player AS 'Player', COUNT(CASE WHEN PlayerTable.InitiatorGroupCat = 'AIRPLANE' THEN PlayerTable.InitiatorGroupCat END) AS Airplane
FROM
(
SELECT HitTbl.InitiatorPlayer AS 'Player', DeadTbl.InitiatorGroupCat
FROM
(
SELECT *
FROM stats
WHERE (EVENT = 'S_EVENT_PILOT_DEAD')
AND InitiatorGroupCat != ''
) AS DeadTbl
JOIN
(
SELECT *
FROM stats
WHERE EVENT = 'S_EVENT_HIT'
) AS HitTbl
ON DeadTbl.InitiatorID = HitTbl.TargetID
WHERE HitTbl.Time IN
(
SELECT MAX(TIME)
FROM stats
WHERE EVENT = 'S_EVENT_HIT'
GROUP BY TargetID
)
GROUP BY DeadTbl.InitiatorID,DeadTbl.Time
ORDER BY DeadTbl.Time ASC
) AS PlayerTable
GROUP BY PlayerTable.Player
ORDER BY Airplane DESC
");
//return the array and loop through each row
$i = 1;
while ($row2 = mysql_fetch_assoc($result1))
{
?>
<tr valign="top">
<td align=center><?php echo $i ++; ?>.</td>
<td> <?php echo $row2['Player'];?></td>
<td align=center><?php echo $row2['Airplane'];?></td>
</tr>
Второй запрос:
$result2 = mysql_query("SELECT InitiatorPlayer, COUNT(CASE WHEN InitiatorGroupCat = 'AIRPLANE' THEN InitiatorGroupCat END) AS Deaths
FROM stats
WHERE (EVENT = 'S_EVENT_DEAD' OR EVENT = 'S_EVENT_PILOT_DEAD' OR EVENT = 'S_EVENT_EJECTION')
AND InitiatorGroupCat != ''
GROUP BY InitiatorPlayer
ORDER BY Airplane DESC");
Оба запроса работают, но мне нужен результат отображения второго запроса в строке от первого в форме:
1.| Player | 17/8 (8 is result from second query)
Это комбинация (новый столбец в первом запросе делится на «/»):
<?php echo $row2['Airplane']. " / ".$row2['Deaths'];?>
Решено таким образом:
<?php
$result1 = mysql_query("SELECT PlayerTable.Player AS 'Player', COUNT(CASE WHEN PlayerTable.InitiatorGroupCat = 'AIRPLANE' THEN PlayerTable.InitiatorGroupCat END) AS Airplane
FROM
(
SELECT HitTbl.InitiatorPlayer AS 'Player', DeadTbl.InitiatorGroupCat
FROM
(
SELECT *
FROM stats
WHERE (EVENT = 'S_EVENT_PILOT_DEAD' OR EVENT = 'S_EVENT_DEAD' )
AND InitiatorGroupCat != ''
) AS DeadTbl
JOIN
(
SELECT *
FROM stats
WHERE EVENT = 'S_EVENT_HIT'
) AS HitTbl
ON DeadTbl.InitiatorID = HitTbl.TargetID
WHERE HitTbl.Time IN
(
SELECT MAX(TIME)
FROM stats
WHERE EVENT = 'S_EVENT_HIT'
GROUP BY TargetID
)
GROUP BY DeadTbl.InitiatorID,DeadTbl.Time
ORDER BY DeadTbl.Time ASC
) AS PlayerTable
GROUP BY PlayerTable.Player
ORDER BY Airplane DESC
");
$result2 = mysql_query("SELECT InitiatorPlayer, COUNT(CASE WHEN InitiatorGroupCat = 'AIRPLANE' THEN InitiatorGroupCat END) AS Deaths
FROM stats
WHERE (EVENT = 'S_EVENT_PILOT_DEAD' OR EVENT = 'S_EVENT_DEAD')
AND InitiatorGroupCat != ''
GROUP BY InitiatorPlayer");
//return the array and loop through each row
//return the array and loop through each row
$i = 1;
while (($row2 = mysql_fetch_array($result1)) && ($row1 = mysql_fetch_array($result2)))
{
?>
<tr valign="top">
<td align=center><?php echo $i ++; ?>.</td>
<td> <?php echo $row2['Player'];?></td>
<td align=center><?php echo $row2['Airplane']. " / " .$row1['Deaths'];?></td>
</tr>
Это ключ:
while (($row2 = mysql_fetch_array($result1)) && ($row1 = mysql_fetch_array($result2)))
Других решений пока нет …