By the way, looking at your PHP script, I think you miss understood my problem with Artist separation. Take my initial example
Jennifer Lopez - I Luh Ya Papi
Jennifer Lopez f. Pitbull - Live it up
I don't care about the featured artist.
Just edit the PHP script to fit your needs. All you have to edit is $sql variable to exclude featured artist and match at the beginning of artist's name:
$sql = "UPDATE `songs` SET `artist_played` = NOW() WHERE `artist` LIKE '$main_artist%'";
That will update only main artist's name and RDJ will exclude those tracks. I just realized that the script could use preg_split instead of preg_match.
First, if you would ignore the featured artist, you would have a big problem. There are many artists who sing mostly with other artists, eg. "Pitbull" and i'm sure you would hate to hear 3-4 tracks one after another with that artist.
That is exactly why I added the featured artist update to my PHP code.
Second, the solution is not simple at all, in the latest version anyone is able to edit the update track and artist procedure and i already wrote on other topics that i don't have a solution for this issue and if someone more advanced in MySQL can give me a solution, i will implement it and until now no one did. With a few occasions i exchanged some PM's with AndyDeGroo and trust me that he's no beginner when it comes to sql.
I agree that there is no simple solution to this problem. Thank you, Marius, for your kind words. Truth is, I know a bit about SQL, but I'm no genius.
I wrote and tested a crude SQL procedure, but it still has same problem of matching irrelevant artists. It takes a fixed delimiter string as second argument, but the string is case-sensitive:
DELIMITER $$
CREATE PROCEDURE UpdateFeatured( trackID INT, delimStr VARCHAR(15) )
BEGIN
SET @Artist = ( SELECT `artist` FROM `songs` WHERE ID = trackID );
# Find main artist by splitting artist
SET @mainArtist = SUBSTRING_INDEX( @Artist, delimStr, 1 );
# Check if mainArtist was found
IF (@mainArtist != @Artist) THEN
# Find featured artist
SET @featArtist = SUBSTRING( @Artist, POSITION(delimStr IN @Artist )+CHAR_LENGTH(delimStr));
# Update matching entries
UPDATE `songs` SET `artist_played` = NOW()
WHERE `artist` LIKE CONCAT(@mainArtist, '%')
OR `artist` LIKE CONCAT(@featArtist, '%');
END IF;
END$$
DELIMITER ;
Note: It will also update featured artist, but can be adapted for updating only main artist. Also the delimiter has to contain spaces on both ends or it won't work.
The procedure could be called from UpdateTracks procedure like this:
CALL UpdateFeatured(4242, ' feat. ');
OR part of it could be merged into UpdateTracks procedure if someone really wants it.
PS: We will need to put together a list of used separators and to think them well.
For eg on some tracks i've seen the artists separated with the "and" or "&" symbol, but there are also many bands which are containing that symbol "Belle & Sebastian", "Iron & Wine" etc and using these keywords/symbols as separators could lead to unwanted results, so i think that we should limit to "feat." and "featuring" which are more clear that are separating the actual artists.
Based on what I've seen, the splitting regex could be:
/ (f|ft|feat|featuring)[\.;\/]? /If it is anything to go by,
MusicBrainz database contains 1400+ artists with "and" and "&". So those two string are not an option for this purpose.
IMO, the assigned artists should be editable in track editor. Advanced users could then edit those entries to achieve the effect they need. There also should be an option to enable the splitting feature which would be disabled by default to use current artist separation logic.
