php - 在 HTML 表中显示 SQL 查询

Question

我这里有一些 SQL 查询代码，如果我将其放入正确数据库中的 PHPMyAdmin 中，它会显示我想要看到的内容，但我希望它显示在 HTML 表中，有什么想法吗？

SELECT  
  tname AS Team, Sum(P) AS P,Sum(W) AS W,Sum(D) AS D,Sum(L) AS L, 
  SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts  
FROM( 
  SELECT  
    hteam Team,  
    1 P, 
    IF(hscore > ascore,1,0) W, 
    IF(hscore = ascore,1,0) D, 
    IF(hscore < ascore,1,0) L, 
    hscore F, 
    ascore A, 
    hscore-ascore GD, 
    CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS 
  FROM games 
  UNION ALL 
  SELECT  
    ateam, 
    1, 
    IF(hscore < ascore,1,0), 
    IF(hscore = ascore,1,0), 
    IF(hscore > ascore,1,0), 
    ascore, 
    hscore, 
    ascore-hscore GD, 
    CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END 
  FROM games 
) as tot 
JOIN teams t ON tot.Team=t.id  
GROUP BY Team  
ORDER BY SUM(Pts) DESC ; 

目前，当我在 PHPMyAdmin 中运行此代码时，这是我得到的结果，这就是我想要在 html 表中输出的内容:

我正在尝试在此网站上创建此内容:

http://www.artfulsoftware.com/infotree/qrytip.php?id=804

到目前为止，我已尝试运行查询，但没有任何结果；

        <?php

    $servername = "-";
    $username = "-";
    $password = "-";
    $dbname = "-";

    // Create connection
$mysqli = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
} 
$query = "SELECT  
  tname AS Team, Sum(P) AS P,Sum(W) AS W,Sum(D) AS D,Sum(L) AS L, 
  SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts  
FROM( 
  SELECT  
    hteam Team,  
    1 P, 
    IF(hscore > ascore,1,0) W, 
    IF(hscore = ascore,1,0) D, 
    IF(hscore < ascore,1,0) L, 
    hscore F, 
    ascore A, 
    hscore-ascore GD, 
    CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS 
  FROM games 
  UNION ALL 
  SELECT  
    ateam, 
    1, 
    IF(hscore < ascore,1,0), 
    IF(hscore = ascore,1,0), 
    IF(hscore > ascore,1,0), 
    ascore, 
    hscore, 
    ascore-hscore GD, 
    CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END 
  FROM games 
) as tot 
JOIN teams t ON tot.Team=t.id  
GROUP BY Team  
ORDER BY SUM(Pts) DESC ; ";

if ($stmt = $mysqli->prepare($query)) {

    /* execute statement */
    $stmt->execute();



    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();
?>

Answer 1

好吧，你可以做这样的事情

<?php

$connection=mysqli_connect(your database parameters);
$query="sql query";
$r=mysqli_query($connection,$query);
$resultset=array();  //Associative Array
echo "<div id='table'><center><table border=1>
<tr>
<th>Column Headings</th>
<tr>
</tr></center>";    
while($row=mysqli_fetch_assoc($r))
{
    echo "<tr>";
    echo "<td>" . $row['team'] . "</td>";
    echo "<td>" . $row['p'] . "</td>";
    ...
    echo "</tr>";
}
echo "</table><br>";
?>