在PHP中运行查询以在表中显示

Question

我试图创建一个表，查询在phpmyadmin中工作。但是我不能使用php在表中显示它。

我认为问题在于我没有以正确的方式运行查询。当我运行代码时，我得到了“未填充的表查询”。

请您提供一些关于运行这个程序的建议：

    <?php

    $sql = 
                    "SELECT player_name,
    SUM(win+draw+lose) AS Played,
    SUM(win) AS Won,
    SUM(draw) AS Drawn,
    SUM(lose) AS Lost,
    SUM(`for`) AS `Goals For`,
    SUM(`against`) AS `Goals Against`,
    SUM(cast(`for`AS SIGNED) - cast(`against`AS SIGNED)) AS `Goal Difference`,
    SUM((win*3)+(draw)+extra_points) AS Points,
    ROUND((SUM((win*3)+draw))/SUM((win+draw+lose)*3)*100,1) AS Record

    FROM appearances
    WHERE season_id = 4
    GROUP BY player_name
    ORDER BY Points DESC,
    Played ASC,
    `Goal Difference` DESC, `Goals For` DESC,
    player_name ASC[...]";

    if (!$sql) {
        echo 'sql query has not worked';

    }else {


    $leaguetable = mysql_query($sql);    
    if (!$leaguetable){
    echo 'league table query not populated';
    } else {

            $records = mysql_fetch_array($leaguetable);

            If (!$records) {
                echo 'records have not been placed in assoc array';            
            }

            else {

                    echo "<tr width='600'>";
                    echo "<td>" . $records["player_name"] . "</td>";
                    echo "<td>" . $records["Played"] . "</td>";
                    echo "<td>" . $records["Won"] . "</td>";
                    echo "<td>" . $records["Drawn"] . "</td>";
                    echo "<td>" . $records["Lost"] . "</td>";
                    echo "<td>" . $records["Goals For"] . "</td>";
                    echo "<td>" . $records["Goals Against"] . "</td>";
                    echo "<td>" . $records["Goal Difference"] . "</td>";
                    echo "<td>" . $records["Points"] . "</td>";
                    echo "<td>" . $records["Record"] . "</td>";
                    echo "</tr>";

                //while ($records = mysql_fetch_assoc($sql));
            }
        }
    }
    ?>

当我运行上面的内容时，我得到了“未填充的表查询”。

提前感谢您的帮助

我刚刚尝试了PDO方法，它仍然没有显示记录，我相信有一个数据库连接，这是代码

 <?php

        require 'pdoconnect.php';

$db = new PDO('mysql:host=' . $config['db']['host'] . ';dbname=' . $config['db']['dbname'], $config['db']['username'], $config['db']['password']);

$stmt = $db->prepare("SELECT player_name,
        SUM(win+draw+lose) AS Played,
        SUM(win) AS Won,
        SUM(draw) AS Drawn,
        SUM(lose) AS Lost,
        SUM(`for`) AS `Goals For`,
        SUM(`against`) AS `Goals Against`,
        SUM(cast(`for`AS SIGNED) - cast(`against`AS SIGNED)) AS `Goal Difference`,
        SUM((win*3)+(draw)+extra_points) AS Points,
        ROUND((SUM((win*3)+draw))/SUM((win+draw+lose)*3)*100,1) AS Record

        FROM appearances
        WHERE season_id = 4
        GROUP BY player_name
        ORDER BY Points DESC,
        Played ASC,
        `Goal Difference` DESC, `Goals For` DESC,
        player_name ASC[...]");

$stmt->execute();
$result = $stmt->fetchAll(pdo::FETCH_ASSOC);

           while($row = $result){
                        echo "<tr width='600'>";
                        echo "<td>" . $row['player_name'] . "</td>";
                        echo "<td>" . $row['Played'] . "</td>";
                        echo "<td>" . $row['Won'] . "</td>";
                        echo "<td>" . $row['Drawn'] . "</td>";
                        echo "<td>" . $row['Lost'] . "</td>";
                        echo "<td>" . $row['Goals For'] . "</td>";
                        echo "<td>" . $row['Goals Against'] . "</td>";
                        echo "<td>" . $row['Goal Difference'] . "</td>";
                        echo "<td>" . $row['Points'] . "</td>";
                        echo "<td>" . $row['Record'] . "</td>";
                        echo "</tr>";

                    //while ($records = mysql_fetch_assoc($sql));
                }
        ?>

Answer 1

请把这张表替换一下

$leaguetable = mysql_query($sql);    

使用

$leaguetable = mysql_query($sql) or die(mysql_error());    

检查查询失败的原因

Answer 2

在对数据库发出任何查询之前，必须连接到数据库：

在脚本开始时尝试这样做(用服务器数据替换localhost、mysql_user和mysql_password )：

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
  die('Could not connect: ' . mysql_error());
}

$db_link= mysql_select_db('database_name', $link);
if (!$db_link) {
  die ('Can\'t use database_name: ' . mysql_error());
}

请参阅http://php.net/manual/en/function.mysql-connect.php

更多细节请与http://php.net/manual/en/function.mysql-select-db.php联系。

Answer 3

我不知道您使用的是哪个版本的PHP，但您可能会考虑http://php.net/manual/en/function.mysql-query.php上的警告

此扩展在PHP5.5.0中不再受欢迎，以后将被删除。相反，应该使用MySQLi或PDO_MySQL扩展。