相同的专辑名称查询结果

Question

我正在尝试为歌手插入专辑，例如：

我的心: Singer1

可爱: Singer1和Singer3

触摸我: Singer5

可爱的专辑，是由两位歌手分享的

以此类推，在PHP中显示结果时，我确实喜欢这样：

我的心: Singer1可爱: Singer1 - Singer3触摸我: Singer5

偶然的是，我有一个相同的专辑名，歌手之间没有共享，在显示结果的同时，它显示如下：

我的心: Singer1 - Singer8

可爱: Singer1 - Singer3

触摸我: Singer5

我的心: Singer1 - Singer8

然而，Singer1和Singer8并没有分享这张专辑，我想显示如下：

我的心: Singer1

可爱: Singer1 - Singer3

触摸我: Singer5

我的心: Singer8

$queryy = 'SELECT silsila_ar, id_shksj 
           FROM mariyat_silsila 
              JOIN mariyat_silsila_join on 
                  mariyat_silsila.ids = mariyat_silsila_join.id_silsilaj 
           WHERE mariyat_silsila.silsila_tran = "'.$albumName.'"';
echo $queryy.'<br>';      
$reqq = $connexion->query($queryy);
$resultt = $reqq->fetchAll();

if($resultt){

    //$listShk = array();
    $listShk = '';
    foreach($resultt as $kv) {
        $id_shksj = $kv['id_shksj'];
        $silsila_ar = $kv['silsila_ar'];

        // get sheikh name
        //$queSh = 'SELECT shk_fname, shk_lname FROM sheikh_tbl WHERE shk_tran = "'.$main_shk.'"';

        $queSh = 'SELECT shk_fname, shk_lname, shk_tran 
                  FROM sheikh_tbl 
                  WHERE id_shk = "'.$id_shksj.'"';

    echo $queSh.'</br>';      
    $reqSh = $connexion->query($queSh);
    $resSh = $reqSh->fetchAll();


    foreach($resSh as $aSh)
    {
        $shk_fname = $aSh['shk_fname'];
        $shk_lname = $aSh['shk_lname'];
        $shk_tran = $aSh['shk_tran'];

        $shkFullName = $shk_fname.' '.$shk_lname;

        //$listShk[] = $shkFullName;
        $listShk .= '<a href="Sheikhs/'.$shk_tran.'.html" target="_BLANK">';
        $listShk .= $shkFullName;
        $listShk .= '</a>';
        $listShk .= ' - ';
        $listSheikhs = substr($listShk, 0,-2).'<br />';


    }
}

Answer 1

您想要的内容取决于您的数据库结构(对不起，我无法理解您的查询语言)。您的行应该有唯一的标识符；通常是数字ID。你可能有张把专辑和歌手联系在一起的桌子，对吧？

album_singer
+---------------------+
| album     | singer  |
+---------------------+
| My Heart  | Singer 1|
| My Heart  | Singer 8|
| ...                 |

这个表应该只包含您的专辑和歌手的ID。

albums                  singers                 album_singer
+---------------------+ +---------------------+ +----------------------+
| Id      | Name      | | Id     | Name       | | album_id | singer_id |
+---------------------+ +---------------------+ +----------------------+
| 201     | My Heart  | | 101    | Singer 1   | | 201      | 101       |
| 202     | My Heart  | | 102    | Singer 8   | | 202      | 102       |
| ...                 | | ...                 | | ...                  |

如果这是您的结构，您的查询应该如下所示：

SELECT albums.name as album_name, 
       singers.name as singer_name
FROM album_singer 
JOIN albums on album_singer.album_id = albums.id
JOIN singers on album_singer.singer_id = singer.id

# Output:
# +------------------------------------+
# | album_name    | singer_name        |
# +------------------------------------+
# | My Heart      | Singer 1           |
# | My Heart      | Singer 2           |
# +------------------------------------+

Answer 2

这有用吗？

//Assuming you are connected to the database...

$album_query = "SELECT id, album FROM album_table ORDER BY id";
$album_results = mysqli_query($database_connection, $album_query);

// Create while loop listing the albums. 
while(list($id, $album) = mysqli_fetch_array($album_results, MYSQLI_NUM)){
    echo 'Album -' . $album . '<br>';
    echo 'Singers - ';

    // Then create another query based on the first loop's album name.
    // *note - would maybe be easier to do this with id's instead of names.
    // If there are no common fields, perhaps you could set a variable from a JOIN in the first query. ?
    $singers_query = "SELECT singer_id, singer_name, album
                      FROM singers_table
                      WHERE album = ' . $album . ' ";
    $singers_results = mysqli_query($database_connection, $singers_query);

        //Now create another while loop to nest inside the album loop.
        while(list($singer_id, $singer_name, $album)=mysqli_fetch_array($singers_results, MYSQLI_NUM)){
            echo  $singer_name . ', ';
        }
    echo '<br><br>';
}