PHP and mysql many to many relationship looping

El_Bajero · September 28, 2009, 10:40pm

I have a question relating to PHP and mysql that has stumped me for hours. A portion of my database contains three tables:

Table 1: albums (with two fields - id and name)
Table 2: artists (with two fields - id and name)
Table 3: albumartist (with two fields - album_id and artist_id)

The third table is used to manage the many to many relationhip between albums and artists.

The goal is to present a list of the albums with html links to their respective artists. Thus far it seems to me the solution will be in some type of PHP nested loop which I have yet to get my head around? Another way to think of this is being able use GROUP_CONCAT yet still retain the ability to link to artist individually. Below is the coding for the GROUP_CONCAT method that is not a relevant solution due to the only one link being available in the case of multiple artists for a given album.

Here is the coding: (Note “echo” will not be used for security once code is completed, instead ~ htmlspecialchars)

<?php

	   $sql	=	"SELECT albums.id as albumid, albums.name as albumname, artists.id as artistid,
				GROUP_CONCAT(artists.name SEPARATOR ', ') as artistname
				FROM albums
				INNER JOIN albumartist ON albumartist.album_id = albums.id
				INNER JOIN artists ON artists.id = albumartist.artist_id
				GROUP BY albums.id
				ORDER BY albumname, artistname ";
											
$result		=	mysqli_query($link, $sql);

				while ($row = mysqli_fetch_array($result))
				{
					$albums[] = array(	'albumid'		=&gt;	$row	['albumid'],
									'albumname'		=&gt;	$row	['albumname'],
									'artistid'		=&gt;	$row	['artistid'],
									'artistname'	=&gt;	$row	['artistname']
									);
				}

?>
<html>
<div id=“albums”>
<?php foreach ($albums as $album): ?>
<ul>
<li><a href=“?album_id=<?php echo($album[‘albumid’])?>”><?php echo($album[‘albumname’])?></a></li>
<li><a href=“?artist_id=<?php echo($album[‘artistid’])?>”><?php echo($album[‘artistname’])?></a></li>
</ul>
<?php endforeach; ?>
</div> <!-- end of albums div –>
</html>

Any ideas as to how to (inner) loop through the albumartist table while (outer) looping through the albums table?

Thank you for your help,

Brian

Beaumont · September 29, 2009, 12:19am

I guess there’s a variety of ways you could go about that. One would be to query all of the data from ‘albumartist’, then loop through it to create an array of albums, each of which has an array of artists as an element. That is, get rid of GROUP_CONCAT() and just select artists.name. Then in your loop:



$albums[ $row[ 'albumid' ] ][ 'albumname' ] = $row[ 'albumname' ];

$albums[ $row[ 'albumid' ] ][ 'artists' ][ $row[ 'artistid' ] ] = $row[ 'artistname' ];

Then loop over $albums and loop over the artists sub-array within.

r937 · September 29, 2009, 1:01am

the correct answer is to use a single query that retrieves the results you want in one call to the database

your query actually has the correct join, although it’s not necessary to GROUP_CONCAT the artist’s names, especially if you neglect to include the artist ids in the same way

El_Bajero · September 29, 2009, 2:17am

I realize a single query is ideal, however, I run into a few problems. One obstacle I encounter is omitting the GROUP_CONCAT will only show the first artist associated to the album (in albumartist relationship). Alternatively, I can omit the GROUP BY clause but then a row for each album will result for each artist.

For example, if an album has two artists, the undesired output would yield:

Album A
Artist 1

Album A
Artist 2

What I am after is:

Album A
Artist 1
Artist 2

By the way, I learned recently SQL from your book, thanks for taking the time to write the book!

Thanks,
B

r937 · September 29, 2009, 2:42am

what you want is a simple query which returns a result set like this –

Album A Artist 1
Album A Artist 2
Album B Artist 9
Album C Artist 4
Album C Artist 5
Album C Artist 6

re-arranging this result cosmetically to provide a nice indented output display is the job of your application language, not SQL

Beaumont · September 29, 2009, 3:10am

Like I said, “query all of the data from ‘albumartist’, then loop through it to create an array of albums, each of which has an array of artists as an element.” I neglected to say that you need to remove the GROUP BY clause as well as the GROUP_CONCAT() – sorry. I didn’t say it explicitly, but I meant to query all of that data, including keeping the JOINs you have pulling in the data from the other tables.

Alternatively, I can omit the GROUP BY clause but then a row for each album will result for each artist.

That’s what you want. I gave you a snippet of code that shows how to build the array once you query all of the data. After you build the $albums array as shown in my previous code snippet, then you can loop over it like this:



foreach ( $albums as $albumid => $album ) {

  // ...

  foreach ( $album[ 'artists' ] as $artistid => $artistname ) {

    // ...

  }
  // foreach

}
// foreach

El_Bajero · September 29, 2009, 3:23am

Thank you Beaumont, I appreciate your time.
This will provide me with the solution I am after. Your second code snippet really helps me.

Rudy’s solution is also relevant, but not quite how I would like to present the data.

Cheers,
Brian

r937 · September 29, 2009, 3:25am

perhaps you missed my point?

SQL should be used to return the data in the most efficient, simplest manner

presentation is the job of the front-end tier

oddz · September 29, 2009, 3:43am

The last post provides a link to a series of classes built to achieve exactly what you would like. The algorithm used is structured in a way to handle an infinite number of depths. Take a look at it if you like. The majority of code that is responsibility for the main algorithm can be found here. An example of usage is provided on the link I posted. The whole purpose behind it is to map a SQL result set to a hierarchical structure removing repeating items based on a unique identifier. It allows you the freedom to write any query you would like and map the result to a tree.

oddz · September 29, 2009, 4:00am

Just to show yu how this works given your example.

Dumby array:


$arrResult = array(
	array('albums_id'=>2,'artists_name'=>'Three Days Grace','artists_id'=>4,'albums_name'=>'One-X')
	,array('albums_id'=>313,'artists_name'=>'Three Days Grace','artists_id'=>4,'albums_name'=>'Life Starts Now')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research')
	,array('albums_id'=>5409,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Our Long Road Home')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero')
);

Query Mapper code


$objQueryMapper = new QueryMapper();

$objTable1Node = $objQueryMapper->createNode();
$objTable1Node
->setPrimaryKey('id')
->addMapping('artists_id','id')
->addMapping('artists_name','name');

$objTable2Node = $objQueryMapper->createNode();
$objTable2Node->setPrimaryKey('id')
->setName('albums')
->addMapping('albums_id','id')
->addMapping('albums_name','name');

$objTable1Node->addChild($objTable2Node);

/*
* Create QueryMapper collection to collect result rows
*/
$objQueryMapperCollection = $objQueryMapper->createCollection();

/*
 * Create parser
 * @arg 1: QueryMapperCollection
 * @arg 2: QueryMapper 
 */
$objQueryMapperParser = $objQueryMapper->createParser(
	$objQueryMapperCollection
	,$objQueryMapper
);

foreach($arrResult as $row) {
	$objQueryMapperParser->parse($row,$objTable1Node);
}

echo '<pre>',print_r($objQueryMapperCollection),'</pre>';

Result:

couldn’t be simpler and saves a lot time while allowing you to construct the query yourself. Added bonus of supporting surrogate keys by passing any number of fields to setPrimaryKey() method.

oddz · September 29, 2009, 4:36am

Throwing tracks into the mix because I can,lol

Dumby array:


$arrResult = array(
	array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>23,'title'=>'It\\'s All Over')
	,array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>455,'title'=>'Pain')
	,array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>67,'title'=>'Animal I Have Become')
	,array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>390,'title'=>'Never Too Late')
	,array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>43789,'title'=>'On My Own')
	,array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>123,'title'=>'Riot')
	,array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>54,'title'=>'Get Out Alive')	
	,array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>14,'title'=>'Let it Die')	
	,array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>3400,'title'=>'Over and Over')	
	,array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>4678,'title'=>'Gone Forever')	
	,array('albums_id'=>2,'artists_name'=>'One-X','artists_id'=>4,'albums_name'=>'Three Days Grace','tracks_id'=>4278,'title'=>'One X')
	
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>78,'title'=>'March of the Dogs')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>1,'title'=>'I Will Not Fall For You')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>3,'title'=>'Violent Seas')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>7,'title'=>'Birthday')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>9,'title'=>'Facepeeler')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>11,'title'=>'Calling')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>13,'title'=>'Forever Endevor')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>17,'title'=>'Lost In The Woods')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>19,'title'=>'So Eager')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>21,'title'=>'She')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>267,'title'=>'Promise')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>25,'title'=>'Nightmare')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>27,'title'=>'Bly-Sky Research')
	
	
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>29,'title'=>'Underclass Hero')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>31,'title'=>'Walking Disaster')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>33,'title'=>'Speak of The Devil')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>36,'title'=>'Dear Father')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>37,'title'=>'Count Your Blessings')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>78,'title'=>'Ma Poubelle')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>20,'title'=>'The Jester')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>18,'title'=>'With Me')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>10,'title'=>'Pull The Curtain')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>6,'title'=>'King of Contridication')
	,array('albums_id'=>56,'artists_name'=>'Sum 41','artists_id'=>57,'albums_name'=>'Underclass Hero','tracks_id'=>6,'title'=>'Best of Me')
	,array('albums_id'=>412,'artists_name'=>'Taproot','artists_id'=>32,'albums_name'=>'Blue-Sky Research','tracks_id'=>15,'title'=>'April Suits')
);

Query Mapper logic


$objQueryMapper = new QueryMapper();

$objTable1Node = $objQueryMapper->createNode();
$objTable1Node
->setPrimaryKey('id')
->addMapping('artists_id','id')
->addMapping('artists_name','name');

$objTable2Node = $objQueryMapper->createNode();
$objTable2Node->setPrimaryKey('id')
->setName('albums')
->addMapping('albums_id','id')
->addMapping('albums_name','name');

$objTable3Node = $objQueryMapper->createNode();
$objTable3Node->setPrimaryKey('id')
->setName('tracks')
->addMapping('tracks_id','id')
->addMapping('title','title');

$objTable1Node->addChild($objTable2Node);
$objTable2Node->addChild($objTable3Node);

/*
* Create QueryMapper collection to collect result rows
*/
$objQueryMapperCollection = $objQueryMapper->createCollection();

/*
 * Create parser
 * @arg 1: QueryMapperCollection
 * @arg 2: QueryMapper 
 */
$objQueryMapperParser = $objQueryMapper->createParser(
	$objQueryMapperCollection
	,$objQueryMapper
);

foreach($arrResult as $row) {
	$objQueryMapperParser->parse($row,$objTable1Node);
}

echo '<pre>',print_r($objQueryMapperCollection),'</pre>';

Result

So as you can see the algorithm itself is pretty powerful and heavily reduces the amount of time it would take to achieve the above on a case by case basis.

El_Bajero · September 29, 2009, 5:29am

I understand your point, communication is the tough part! The PHP coding to present the data is where I was struggling.

You the man.

B

El_Bajero · September 29, 2009, 5:31am

Query Mapper seems interesting, but is pretty intense for my current needs. But for the future…

Thanks again everyone for the input.

B