PHP多表数据显示异常求助：SQL语句及代码问题排查

1. SQL语句语法错误 #

你的两条SQL语句中，子查询的反引号包裹存在语法错误：

• 第一条COUNT统计SQL里，反引号`在换行后单独出现，导致SQL解析失败
• 第二条分页查询SQL同样存在多余换行和反引号位置错误

修正后的COUNT统计SQL：

SELECT COUNT(*) AS Count
FROM (
    SELECT song.SongId, song.Picture, song.Length, song.UrlSong, artist.Lastname 
    FROM song 
    LEFT JOIN songandartist ON song.SongId = songandartist.SongId 
    LEFT JOIN artist ON songandartist.ArtistId = artist.ArtistId
) AS T1

修正后的分页查询SQL：

SELECT `Length`,`Picture`,`SongId`,`Lastname`,`UrlSong` 
FROM (
    SELECT song.SongId, song.Picture, song.Length, song.UrlSong, artist.Lastname 
    FROM song 
    LEFT JOIN songandartist ON song.SongId = songandartist.SongId 
    LEFT JOIN artist ON songandartist.ArtistId = artist.ArtistId
) AS T1
LIMIT :start, :pageSize;

2. JOIN逻辑错误 #

原SQL中LEFT JOIN songandartist后使用INNER JOIN artist，这会抵消LEFT JOIN的效果——如果某首歌没有关联的艺术家（songandartist中无对应记录），songandartist.ArtistId为NULL，INNER JOIN会直接排除这些记录，等同于全部用INNER JOIN。若需要保留所有歌曲（即使无艺术家关联），应改为LEFT JOIN artist。

3. 类实例化错误 #

在getAllRecords方法中，你尝试实例化GetAllSong类，但当前定义的类名是Song，这会触发致命错误，无法创建对象，最终返回空数组导致页面无内容输出。

修正代码：

// 错误代码
// $song = new GetAllSong();
// 修正为
$song = new Song();

4. 安全性与调试优化 #

• 开启PHP错误显示，方便快速排查问题：在index.php开头添加

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

• 使用PDO参数绑定替代直接拼接SQL，避免SQL注入风险：

// 分页SQL改为参数绑定
$sql = "SELECT `Length`,`Picture`,`SongId`,`Lastname`,`UrlSong` 
        FROM (
            SELECT song.SongId, song.Picture, song.Length, song.UrlSong, artist.Lastname 
            FROM song 
            LEFT JOIN songandartist ON song.SongId = songandartist.SongId 
            LEFT JOIN artist ON songandartist.ArtistId = artist.ArtistId
        ) AS T1
        LIMIT :start, :pageSize;";

$stmt = $conn->prepare($sql);
$stmt->bindParam(':start', $start, PDO::PARAM_INT);
$stmt->bindParam(':pageSize', $pageSize, PDO::PARAM_INT);
$stmt->execute();

完整修正后的getAllRecords方法 #

public static function getAllRecords($pageNo, $pageSize, &$totalRecords) {
    $options = array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
    $dsn = "mysql:host=" . DatabaseInfo::getServer() . ";dbname=" . DatabaseInfo::getDatabaseName() . ";charset=utf8";
    $conn = new PDO($dsn, DatabaseInfo::getUserName(), DatabaseInfo::getPassword(), $options);

    $pageNo = (int)$pageNo;
    $pageSize = (int)$pageSize;

    // 修正后的COUNT查询
    $sql = "SELECT COUNT(*) AS Count
            FROM (
                SELECT song.SongId, song.Picture, song.Length, song.UrlSong, artist.Lastname 
                FROM song 
                LEFT JOIN songandartist ON song.SongId = songandartist.SongId 
                LEFT JOIN artist ON songandartist.ArtistId = artist.ArtistId
            ) AS T1";

    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $totalRecords = $row['Count'];
    $stmt = NULL;

    $totalPages = ceil($totalRecords / $pageSize);
    if ($pageNo > $totalPages) {
        $pageNo = $totalPages;
    }

    $start = $pageSize * $pageNo - $pageSize;
    if($start < 0) {
        $start = 0;
    }

    // 修正后的分页查询，使用参数绑定
    $sql = "SELECT `Length`,`Picture`,`SongId`,`Lastname`,`UrlSong` 
            FROM (
                SELECT song.SongId, song.Picture, song.Length, song.UrlSong, artist.Lastname 
                FROM song 
                LEFT JOIN songandartist ON song.SongId = songandartist.SongId 
                LEFT JOIN artist ON songandartist.ArtistId = artist.ArtistId
            ) AS T1
            LIMIT :start, :pageSize;";

    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':start', $start, PDO::PARAM_INT);
    $stmt->bindParam(':pageSize', $pageSize, PDO::PARAM_INT);
    $stmt->execute();

    $list = Array();
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        // 修正类实例化
        $song = new Song();

        $song->length = $row["Length"];
        $song->picture = $row["Picture"];
        $song->songId = $row["SongId"];
        $song->lastname = $row["Lastname"];
        $song->urlSong = $row["UrlSong"];

        array_push($list, $song);
    }

    $conn = NULL;

    return $list;
}

内容的提问来源于stack exchange，提问作者newbieforit