Mysql查询选择具有相同艺术家ID但具有不同CDID的记录

Question

我试图在选择相册后显示艺术家详细信息。但同样地，与艺术家相关的其他专辑也应该显示。有没有办法做到这一点。

左边一栏是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 

预先感谢您。

Answer 1

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

（注：在下面的例子中，你可能会想在第一次查询后增加一个检查，以确保它运行第二个查询之前返回结果）

下面是使用两个查询的例子：

$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