Help - PHP/MySQL sending me loopy

Dev Shed Forums Sponsor:

July 10th, 2003, 08:01 PM

rickydazla

Junior Member

Join Date: Jul 2003

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

Help - PHP/MySQL sending me loopy

I have been trying to get to grips with MySQL/PHP for a couple of weeks now for a fairly basic task and have got a little stuck. I have a table containing song lyrics, the track they're from and the artist performing them, amongst other bits. The following will display a list of artists as links and clicking the link will then display the lyrics...

Quote:

// display individual record

if ($id) {

$result = mysql_query("SELECT * FROM lyrics WHERE lyric_id=$id",$db);

$myrow = mysql_fetch_array($result);

printf($myrow["artist"]);

printf($myrow["track"]);

printf($myrow["lyrics"]);

} else {

// show list

$result = mysql_query("SELECT * FROM lyrics",$db);

if ($myrow = mysql_fetch_array($result)) {

// display list if any to display

do {

printf("<a href=\"%s?id=%s\">%s</a><br>\n", $PHP_SELF, $myrow["lyric_id"], $myrow["artist"]);

} while ($myrow = mysql_fetch_array($result));

} else {

// none to display

echo "Sorry, none found";

}

}

The thing is, some of the artists have more than one set of lyrics in the database and so they appear twice on the list. What I am trying to do is have another level so that they only appear once on the list, then clicking their name will display a second list of tracks and then clicking the tracks will take you through to the lyrics.

I've tried a few options but keep getting tangled in loops. Is there a simple(ish)way of doing what I want?

Cheers

Rick

July 11th, 2003, 12:38 AM

jharnois

mod_dev_shed

Dev Shed God 19th Plane (14000 - 14499 posts)

Join Date: Sep 2002

Location: Atlanta, GA

Posts: 14,359

Time spent in forums: 1 Month 1 Week 2 Days 7 h 37 m 27 sec
Reputation Power: 804

Your problem is probably in your database design, not your PHP. Dump the database structure and show us that.

July 12th, 2003, 02:25 AM

rickydazla

Junior Member

Join Date: Jul 2003

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

Pretty basic - just one table with the individual song lyrics, the track title and artist as columns within...

July 12th, 2003, 10:11 AM

jharnois

mod_dev_shed

Join Date: Sep 2002

Location: Atlanta, GA

Posts: 14,359

Time spent in forums: 1 Month 1 Week 2 Days 7 h 37 m 27 sec
Reputation Power: 804

That's what's making this difficult. You should have two tables that relate to each other. One for your artists, one for the songs (including lyrics).

Code:

table.artist
id
name

table.song
id
artist_id
title
lyrics

This is a basic one-to-many relationship; you have one artist that has many songs. With this setup, you can query just the artist table to get a list of artist, then query the song table based on the selected artist.

PHP Code:


		
			
/*-- artists.php --*/

$sql = 'SELECT id, name FROM artist ORDER BY name';

$result = mysql_query($sql);

while($row = mysql_fetch_object($result))

  {

  echo '<a href="lyrics.php?artist='.$row->id.'">'.$row->name.'</a><br  />';

  }

PHP Code:


		
			
/*-- lyrics.php --*/

$artist = $_GET['artist'];

// check $artist to make sure someone isn't trying to do SQL injection

$sql = 'SELECT title, lyrics FROM song WHERE artist_id='.$artist.' ORDER BY title';

/// .. get the results, which will be those songs by the selected artist

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: