MySQL查询以选择具有相同artistID但CDID不同的记录

anonymous5671 发表于 Dev

匿名5671

选择专辑后，我试图显示艺术家的详细信息。但与此同时，与该艺术家相关的其他专辑也应显示。有什么办法可以做到这一点。

左列是CDID，右列是artistID。

这是我当前的mysql代码：

<?php

require "pdoDB.php";
$albumtitle = $_GET['title'];
$db = database::connect();
$artistsql = "SELECT a.artistName, p.pubName, cd.CDTitle FROM tiptop_artist a
             INNER JOIN tiptop_artistcd acd ON a.artistID = acd.artistID
             INNER JOIN tiptop_cd cd ON cd.CDID = acd.CDID 
             INNER JOIN tiptop_publisher p ON p.pubID = cd.pubID  
             WHERE cd.CDTitle = ?";
$stmt = $db->prepare($artistsql);
// $stmt->bindParam("catid", $categoryid);
$stmt->execute(array($albumtitle));

echo "<table>
<tr>
<th>Artist Name</th>
<th>Publisher</th>
<th>Other Album</th>
</tr>";

while ($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "<tr style=\"cursor: pointer;\">";
    // echo "<td>" . $row1['artistName'] . "</td>";
    echo "<td>" . $row['artistName'] . "</td>";
    echo "<td>" . $row['pubName'] . "</td>";
    echo "<td>" . $row['CDTitle'] . "</td>";
    echo "</tr>";
}

?>

最终输出应获得如下内容：

artist name | publisher | related albums by the artist
------------------------------------------------------
hardwell    | sony music| I am hardwell
                        | Spaceman

先感谢您。

前锤

您可以通过几种方法在单个查询或多个查询中完成此操作。您可能在某一时刻需要比较不同的查询策略，以查看哪个是性能最高的。

（注意：在下面的示例中，您可能希望在第一个查询之后添加一个检查，以确保它在运行第二个查询之前返回结果。）

这是一个使用两个查询的示例：

$sql = "SELECT artist.name as 'artist.name', 
               artist.id as 'artist.id',
               publisher.name as 'publisher.name', 
               cd.title as 'cd.title',
               cd.id as 'cd.id'
        FROM tiptop_cd as cd
        INNER JOIN tiptop_artistcd as artistcd ON(artistcd.cd_id = cd.id)
        INNER JOIN tiptop_artist as artist ON(artist.id = artistcd.artist_id)
        INNER JOIN tiptop_publisher as publisher ON(publisher.id = cd.publisher_id)
        WHERE cd.title = :title";

$stmt = $db->prepare($artistsql);
$stmt->bindParam(":title", $_GET['title']);
$stmt->execute();
$details = $stmt->fetch(PDO::FETCH_ASSOC);

$sql = "SELECT cd.title as 'cd.title'
        FROM tiptop_artistcd as artistcd
        INNER JOIN tiptop_cd as cd ON(cd.id = artistcd.cd_id AND cd.id != :cd_id)
        WHERE artistcd.artist_id = :artist_id";

$stmt = $db->prepare($artistsql);
$stmt->bindParam(":artist_id", $details['artist.id']);
$stmt->bindParam(":cd_id", $details['cd.id']);
$stmt->execute();

echo "<table>".
     "<tr>".
     "<th>Artist Name</th>".
     "<th>Publisher</th>".
     "<th>Other Album</th>".
     "</tr>".

     "<tr>".
     "<td>".$details['artist.name']."</td>".
     "<td>".$details['publisher.name']."</td>".
     "<td>".$details['cd.title']."</td>".
     "</tr>";

while ($album = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "<tr style=\"cursor: pointer;\">";
    echo "<td></td>";
    echo "<td></td>";
    echo "<td>" . $album['cd.title'] . "</td>";
    echo "</tr>";
}

echo "</table";

这是一个查询版本：

SELECT artist.name as 'artist.name', 
       publisher.name as 'publisher.name', 
       cd.title as 'cd.title',
       albums.title as 'albums.title'
FROM tiptop_cd as cd
INNER JOIN tiptop_artistcd as artistcd ON(artistcd.cd_id = cd.id)
INNER JOIN tiptop_artist as artist ON(artist.id = artistcd.artist_id)
INNER JOIN tiptop_publisher as publisher ON(publisher.id = cd.publisher_id)
INNER JOIN tiptop_artistcd as artistcd2 ON(artistcd2.artist_id = artist.id)
INNER JOIN tiptop_cd as albums ON(albums.id = artistcd2.cd_id)
WHERE cd.title = :title

这是在单个查询中执行此操作的另一种巧妙的（可能是更高性能的）方法。返回的第一行将包含您的详细信息和当前选择的cd，其余行将包含其他专辑：

SELECT artist.name as 'artist.name', 
       publisher.name as 'publisher.name', 
       cd.title as 'cd.title',
       IF(cd.title = :title, 1, 0) as selected
FROM tiptop_cd as cd
INNER JOIN tiptop_artistcd as artistcd ON(artistcd.cd_id = cd.id)
INNER JOIN tiptop_artist as artist ON(artist.id = artistcd.artist_id)
INNER JOIN tiptop_publisher as publisher ON(publisher.id = cd.publisher_id)
ORDER BY selected DESC

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。