CODEWITHSUNDEEP
phpmysqlloopswhile-loop
Mitch
Mitch

Reputation: 87

PHP/MYSQL: how to loop matching results from a query already inside a while loop

I'm very new to PHP/MYSQL and find this quite difficult to explain, so hopefully someone will understand what I'm trying to do. I have a database that collects information on artists and songs. I want to be able to link the artists to the song and display the "credit" with the song information.

I have a database with tables similar to this:

Artist | artist_id, artist_name

Credits | credit_id, credit_name

Song | song_id, song_name

Credit_To_Artist | credit_id, artist_id, song_id

Example data:

Artist | 2, Peter Mark

Artist | 5, Mette Christiansen

Credits | 1, Producer

Credits | 2, Writer

Credits | 3, Vocalist

Song | 23, The Game

Credit_To_Artist | 1, 2, 23

Credit_To_Artist | 2, 2, 23

Credit_To_Artist | 3, 5, 23

I have created a page "song.php" that displays information on each song using mysql_real_escape_string to get the song ID from the URL:

$id = mysql_real_escape_string($_GET['id']);

if (!$id) {
    die('Please provide an id!');
}

$query = "SELECT * FROM `Credit_To_Artist` AS c2a 
INNER JOIN `Credits` AS cr ON cr.credit_id = c2a.credit_id
LEFT OUTER JOIN `Artist` AS a ON a.artist_id = c2a.artist_id
LEFT OUTER JOIN `Song` AS s ON s.song_id = c2a.song_id
WHERE c2a.song_id = $id";

$res = mysql_query($query);

$row = mysql_fetch_assoc($res);

The issue I'm having is I want to be able to list all of the artists linked to that song, and all of their credits in brackets next to it. Since there are more than one artist linked to each song, and most of them have more than one credit (producer, writer, vocalist etc), I have no idea how to write a loop function that shows both of these. Below is my attempt to show what I mean, although it obviously doesn't work:

 while ($row = mysql_fetch_array($res)) {
            $artist = $row[artist_name];

        echo "$artist";

        while ($row = mysql_fetch_array($res)) {
            $credit = $row[credit_name];
            echo "$credit";
            }
        echo "<br />";
    }

This is what I'd ideally like to achieve from the example data above:

Song: The Game

Credits: Peter Mark (Producer, Writer) Mette Christiansen (Vocalist)

Upvotes: 2

Views: 1927

Answers (2)

Matured Fella
Matured Fella

Reputation: 73

Let's start with the tables, here is what i think you should do

artist | id, name
song | id, title
credit | id, credit
song_artists | id, song_id, artist_id
credit_to_artists | id, song_artists_id, credit_id

Should be the way to handle the kind of relationship you want.

And here is the PHP code, it might not be the most efficient one, but it will do the job

$query = "SELECT * FROM song_artists WHERE song_id = $id_from_link";
$result = mysql_query($query);

while($row = mysql_fetch_array($result))
{
  $artistQuery = "SELECT * from artist WHERE id = {$row['artist_id']}";
  $artistResult = mysql_query($artistQuery);
  while($artistRow = mysql_fetch_array($artistResult))
  {
    echo "The Artist: " . $artistRow['name'];
  }

  echo $songArtistId;
  $creditToArtistQuery = "SELECT * FROM credit_to_artists WHERE song_artists_id = {$row['id']}";
  $creditToArtistResult = mysql_query($creditToArtistQuery);
  if(mysql_num_rows($creditToArtistResult)>0)
  {
    echo " { ";
    $isFirstCredit = true;

    while($creditToArtistRow = mysql_fetch_array($creditToArtistResult))
    {
      $creditQuery = "SELECT * FROM credit WHERE id = {$creditToArtistRow['credit_id']}";
      $creditResult = mysql_query($creditQuery);
      while($creditRow = mysql_fetch_array($creditResult))
      {
        if($isFirstCredit)
        {
         echo $creditRow['credit'];
         $isFirstCredit = false;
        }
        else
        {
          echo ", " .$creditRow['credit'];
        }
      }
    }
    echo " } <br />";
  }


}

Upvotes: 0

Sumurai8
Sumurai8

Reputation: 20737

You have two options:

GROUP_CONCAT(..)

You can use GROUP_CONCAT(..). This mysql function groups values in a column that are in each group. You would alter the sql to group by artist_id in this case.

SELECT a.artist_name as aname, GROUP_CONCAT(c.credit_name) as credits
FROM Credits_To_Artist as c2a
JOIN Artist as a ON c2a.artist_id = a.artist_id
JOIN Credits as c ON c2a.credit_id = c.credit_id
GROUP BY c2a.credit_id

Your rows would look like:

Array( "aname" => "name",
       "credits" => "function 1,function 2" )

The biggest problem with GROUP_CONCAT is that if you have to concat a lot of values together, it might exceed the maximum width of the row. This does not seem to be the case for your problem. You would not need a loop with this approach.

Adding to array

If you keep the query as it is, you have a row for each 'credit'. You can prepare your data by adding it to an Array, then use implode(..) in php to add commas.

$artists = Array();
while( $row = mysql_fetch_array($res) ) {
    $artist = $row[artist_name];
    $credit = $row[credit_name];

    if( !array_key_exists( $artist, $artists ) ) {
        $artists[$artist] = Array();
    }

    $artists[$artist][] = $credit;
}

foreach( $artists as $artist => $creditarr ) {
    $credits = implode( ", ", $creditarr );
    echo "{$artist} ({$credits})<br>";
}

You'll find that preparing your data in an array will sometimes be much faster than writing a query that does the same thing. I would probably choose the latter solution.

Upvotes: 1

Related Questions