himynameismark Posted September 16, 2011 Share Posted September 16, 2011 I am working on a website for a company who does Karaoke and they need a song list. I have a sample page of a few songs selected from a sample database I set up. The MySQL and PHP I used organize the artists (removing the word "the" from the artist name), and then the songs when the artist has multiple songs on their list. However, when the artist has multiple entries in the database, I only want their name to appear in the first instance. For example: Adam Ant - Goody Two ShoesThe Beatles - Eleanor Rigby__________ - Hey Jude__________ - The Long and WInding RoadBlondie - Call Meetc. (obviously, this will be much more structured when appearing on the web page) As opposed to: Adam Ant - Goody Two ShoesThe Beatles - Eleanor RIgbyThe Beatles - Hey JudeThe Beatles - The Long and Winding RoadBlondie - Call Meetc. Here is my code as of now: <?php//Link to Database$link = mysql_connect('localhost','root','') or die("Could not connect!");$dblink = mysql_select_db('songlist') or die("Could not find database!"); //Order by Artist and Song, Removing THE from Artist$query = mysql_query("SELECT * FROM songs ORDER BY TRIM(LEADING 'The ' FROM artist), song"); //Start Tableecho "<table>"; //While Loopwhile ($row = mysql_fetch_array($query)) { //Declare Variables $artist = $row['artist']; $song = $row['song']; //Display Song List echo "<tr><td>" . $artist . "</td><td>" . $song . "</td></tr>"; } //End Tableecho "</table>"; //Close Connectionmysql_close($link);?> What should I add to get the desired effect? Thanks in advance,Mark Link to comment Share on other sites More sharing options...
TheGallery Posted September 16, 2011 Share Posted September 16, 2011 Not sure if this the best way to do it but i would add a variable that stores the name of the last artist and then compare it to each loop. If it's the same it would output a different result. Like this $last_artist = ''; while ($row = mysql_fetch_array($query)){ $artist = $row['artist']; $song = $row['song']; if ($last_artist == $artist) echo "<tr><td></td><td>" .$song. "</td></tr>"; else echo "<tr><td>" .$artist. "</td><td>" .$song. "</td></tr>"; $last_artist = $artist;} Link to comment Share on other sites More sharing options...
himynameismark Posted September 16, 2011 Author Share Posted September 16, 2011 That's very similar to something I had already tried, except I didn't think to add the $last_artist = ''; line above the loop. That works, thank you! Link to comment Share on other sites More sharing options...
TheGallery Posted September 16, 2011 Share Posted September 16, 2011 Well the if statement would meet an undeclared variable in the first loop and you'd get an error. No problem. You are actually the first person i helped Link to comment Share on other sites More sharing options...
himynameismark Posted September 17, 2011 Author Share Posted September 17, 2011 Okay, now I have another question regarding the same page. I know how to find the first letter of each artist, but is there a way that I can find the first instance of the first letter being _? I want the users to be able to browse the list by first letter, so they can click a-z or # and it brings them directly to the top of the list starting with whatever letter they click. Link to comment Share on other sites More sharing options...
TheGallery Posted September 17, 2011 Share Posted September 17, 2011 For that, i think you will have to add an id to the first artist that starts with each letter and then your A-Z selection could get the user to those IDs. You can go to an id like in links using <a href="#ID"></a> Link to comment Share on other sites More sharing options...
himynameismark Posted September 17, 2011 Author Share Posted September 17, 2011 Nevermind, I figured that out. It's essentially the same thing as $last_artist, but there is also a counter: if ($prevfirstletter != $firstletter) $counter = 1;else $counter++; And then at the end of the while loop, $prevfirstletter == $firstletter; Then I altered the code to change my first starting with each letter to: if ($counter == 1) echo "<tr><td><a name='" . $firstletter . "' />" . $artist . "</td>";else echo "<tr><td>" . $artist . "</td>; Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.