current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 2 down vote favorite

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)

share|improve this question
    
I think you are searching for GROUP_CONCAT(..). Alternatively, loop through all results and add them to an array, then implode them with a comma. – Sumurai8 Dec 16 '14 at 6:31
    
Thanks for the reply, if it's not too much trouble would you be able to give me a quick example? Being the novice I am, those sound like great solutions but it may take me a few months to learn how to write them haha – Mitch Dec 16 '14 at 6:38
up vote 1 down vote accepted

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.

share|improve this answer
    
The "adding to array" solution works perfectly, thank you so much! – Mitch Dec 16 '14 at 9:08
    
Also, I'm sure it's simple, but how could I make "{$artist}" into a link that uses the artist_id in it's URL? (e.g. <a href="artist.php?id={$artist_id}">{$artist}</a> – Mitch Dec 16 '14 at 9:38
    
Make an array $artisttoid. Identify the code that is only run once for every artist and add your translation to the array there. You can then do $artisttoid[$artist]. – Sumurai8 Dec 16 '14 at 9:54
    
I don't really understand arrays, but I will try, thanks – Mitch Dec 16 '14 at 10:32
    
I hate to be a nag but I've been trying and I haven't been able to alter the code with the new array, are you able to help or give more detailed instructions? – Mitch Dec 17 '14 at 1:02
up vote 0 down vote

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 />";
  }


}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.