Need Help in MySQL LEFT JOIN Display Issue

i'm trying to display a MySQL query correctly on a web page.

i have 4 simple tables that connected together

singers: singer_id, singer_name

songs : singer_id, song_name, album_id

albums : singer_id, album_id, album_name

i already managed to get those tables using this query.

SELECT singers.singer_name, songs.song_name, albums.album_name

FROM singers 
LEFT JOIN songs ON singers.singer_id = songs.singer_id
LEFT JOIN albums ON albums.album_id = songs.album_id
WHERE singers.singer_id = ?
ORDER BY songs.song_id DESC

the problem i'm having now is that i cant seem to display those tables right.

lets say i have a singer with 1 album and 11 songs. this is the result i'm getting for this singer.

singer_name  |     song_name     |     album_name
__________________________________________________

singer name  |  song name        |    album name
singer name  |  song name        |    album name
singer name  |  song name        |    album name
singer name  |  song name        |    album name
singer name  |  song name        |    album name
singer name  |  song name        |    album name
singer name  |  song name        |    album name
singer name  |  song name        |    album name
singer name  |  song name        |    album name
singer name  |  song name        |    album name
singer name  |  song name        |    album name

so instead of getting 1 album i'm getting the same album 11 times.

on the web page i'm displaying the albums using foreach() and i'm getting the same album instead of just one album.

now for the question....

How can i display the album O开发者_Python百科NLY 3 times?

(without hard coding because i might have a singer with 9 albums as well).

thanks in advance for any help.

EDIT:

this is how i display the songs

                        <?php foreach($results as $song): ?>
                        <tr>
                            <td></td>
                            <td>
                                <a href="/song/<?php echo urlencode($song['song_url']); ?>/" class="song">
                                    <span><?php echo $song['song_name']; ?></span>
                                </a>
                            </td>
                            <td>
                                <a href="/album/<?php echo urlencode($song['album_name']); ?>/" class="album">
                                    <?php echo $song['album_name']; ?>
                                </a>
                            </td>
                            <td>
                                <span class="time">
                                    <?php echo $song['song_time']; ?>
                                </span>
                            </td>
                            <td>
                                <a href="/search/?search=<?php echo urlencode($song['song_genre']); ?>" class="genre">
                                    <?php echo $song['song_genre']; ?>
                                </a>
                            </td>
                            <td><a href="#" class="play">נגן</a></td>
                        </tr>
                    <?php endforeach; ?>

i get all the songs even if the songs are not attached to any album. just what i wanted.

the problem with displaying the albums:

                    <?php foreach($results as $album): ?>
                    <li>
                        <a href="/album/<?php echo urlencode($album['url']); ?>/" class="album">
                            <img src="/media/<?php echo $album['singer_dir'] . '/' . $album['album_thumb']; ?>" alt="" />
                            <span class="album_name"><?php echo $album['album_name']; ?></span>
                        </a>
                    </li>
                <?php endforeach; ?>

this singer have 1 album but i get it for 11 times. i'ts because this album have 11 songs. i need to display it only once....

Without knowing much about the $result variable (eg. it could be a PDO object or an array, I don't know) my answer will have to be a hackish:

<?php foreach($results as $album): ?>
    <li>
        <a href="/album/<?php echo urlencode($album['url']); ?>/" class="album">
            <img src="/media/<?php echo $album['singer_dir'] . '/' . $album['album_thumb']; ?>" alt="" />
            <span class="album_name"><?php echo $album['album_name']; ?></span>
        </a>
    </li>
    <?php break; ?>
<?php endforeach; ?>

Personally I wouldn't do this in my own code, but without more of a bigger picture of your code and a clearer idea of what you are trying to do, it is what it is.

Edit 1:

If that's the case then all you need is the following. The foreach is unnecessary:

<li>
    <a href="/album/<?php echo urlencode($result[0]['url']); ?>/" class="album">
        <img src="/media/<?php echo $result[0]['singer_dir'] . '/' . $result[0]['album_thumb']; ?>" alt="" />
        <span class="album_name"><?php echo $result[0]['album_name']; ?></span>
    </a>
</li>

Edit 2:

SELECT album_name
FROM albums 
WHERE singer_id = ?

And loop over this result set using the foreach.

See my answer given to the question how to display the changing values only – I've mentioned that in a comment on your previous question. It basically boils down to grouping the results in PHP without splitting your SQL query.

First, query your database with the following query:

SELECT a.artist_name, COALESCE(b.album_name, '(no album)'), s.song_name, 
FROM songs s
LEFT JOIN artists a
ON s.singer_id = a.singer_id
LEFT JOIN albums b
ON s.album_id = b.album_id AND s.singer_id = b.singer_id
ORDER BY a.artist_name, b.album_id, s.song_id  # order by artist, album then song

This will get you a result set where each row represents a full song (including artist and album name) – imagine a spreadsheet –, that's why you asked for a join in the first place. Let's output this table, printing artist and album only once (the correct ORDER BY is important for this.

$prev = array( 'artist' => null, 'album' => null );
while(($row = mysqli_fetch_assoc($result)) !== FALSE) {
  // print groupings
  if($row['artist_name'] != $prev['artist'])
    displayArtist(); // only display the first occurrence of each artist
  if($row['album_name'] != $prev['album'])
    displayAlbum($row['album_name']); // same goes for albums

  displaySong($row['song_name']); // but display every song

  $prev['artist'] = $row['artist_name'];
  $prev['album'] = $row['album_name'];
}

format*() can be as simple as:

function displayArtist($artist_name) {
  printf('<h1 class="artist">%s</h1>', htmlspecialchars($artist_name));
}
function displayAlbum($album_name) {
  printf('<h1 class="album">%s</h1>', htmlspecialchars($album_name));
}
function displaySong($song_name) {
  printf('<div class="song">%s</div>', htmlspecialchars($song_name));
}

but you can of course use a table layout like in your question

The LEFT OUTER JOIN method is designed as you seen, album should be repeated as a result of cartesian product.

It's not clear your point, I interpreted your question in two ways. 1. repeating singer, repeating songs, repeating albums, because of no group by. 2. non-repeating singer/songs, just want to grouping albums.

If you mean the way 1, the answer is simple. Add distinct keyword. I'm sure you meant way 2, you should manage repeating information. And, if you're using html table to output, rowspan attribute will help you.

example data:

CREATE TABLE t 
( a     varchar(25) 
, b     varchar(25) 
, c_id  integer     
, c     varchar(25) 
);

INSERT INTO t VALUES ( '2ne1', 's1', 1, 'lonely'); 
INSERT INTO t VALUES ( '2ne1', 's2', 1, 'lonely'); 
INSERT INTO t VALUES ( '2ne1', 's3', 1, 'lonely'); 
INSERT INTO t VALUES ( '2ne1', 's4', 1, 'lonely'); 
INSERT INTO t VALUES ( '2ne1', 's5', 1, 'lonely'); 

INSERT INTO t VALUES ( 'anonymous', 'rock1', 2, 'um album'); 
INSERT INTO t VALUES ( 'anonymous', 'rock2', 2, 'um album'); 
INSERT INTO t VALUES ( 'anonymous', 'rock3', 2, 'um album'); 

example code:

<?php

$conn = mysql_connect('localhost', '..', ',,') 
    OR die (mysql_error());
mysql_select_db('test') 
    OR die (mysql_error());
$rs = mysql_query
    ( 'select t.a, t.b, r.span, t.c_id, t.c '.
      'from t                               '.
      'join                                 '.
      '( select c_id, count(c) span         '.
      '  from t                             '.
      '  group by c_id) r                   '.
      '    on t.c_id = r.c_id               ' ) 
    OR die (mysql_error());

echo "<html><table border=1>\n";
$old_c_id = -1;
while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
    echo "<tr>";
    echo "<td>".htmlspecialchars($row['a'])."</td>\n";
    echo "<td>".htmlspecialchars($row['b'])."</td>\n";
    if ($old_c_id != $row['c_id'])
    {
        $old_c_id = $row['c_id'];
        echo  sprintf("<td rowspan=%d>", $row['span'])
            . htmlspecialchars($row['c'])
            . "</td>\n";
    }
    echo "</tr>\n";
}
echo "</table></html>";

?>