Sorting titles without definite article advice

[Big_Pat]

Hi again,

 

 There's something bugging me about my music database, and that's listing bands under 'T' when their name starts with The. I'd like to write some code to change this, but my current queries wouldn't work without a bit of a reshuffle. 

Currently I'm using a select option for the user to select a letter of the alphabet like so:

$query  = "SELECT title, artist, album, album_artist
            from songlist";
            if (isset($_POST["letter_filter"])) {
                $letter = $_POST["letter_filter"] . "%";
                $query .= " WHERE title LIKE '$letter'";
            } else {
                $query .= " WHERE title LIKE 'A%'";
            }
            $query .= " order by title asc";

And that's fine, it works well. Of course, this way bands starting with 'A', 'The', or even solo artists will appear under the wrong letter - apart from The The I suppose!

 

Now, I found this piece of code which works, but not with my current query. The code is:

SELECT title, artist, album, album_artist,CASE WHEN SUBSTRING(`artist`,1,2) LIKE 'a ' THEN SUBSTRING(`artist`,3) WHEN SUBSTRING(`artist`,1,3) LIKE 'an ' THEN SUBSTRING(`artist`,4) WHEN SUBSTRING(`artist`,1,4) LIKE 'the ' THEN SUBSTRING(`artist`,5) ELSE `artist` END AS `artist_sort` FROM `songlist` ORDER BY `artist_sort`

This would be fine if my initial query returned EVERY result but, as you can see, I currently only query by the first letter.

 

I've got a couple of options, I think. One would be to make a new column in my MySQL database called 'prefix', and populate that with sorted artist names (Sex Pistols, The, for example) and use my existing queery or I could re-write my query to return ALL results using the code above and then apply a letter filter in PHP. Both seem a little wasteful of resource, but I can't think of another way. Which, do you think, would be the better of the two options?

Edited May 22, 2013 by Big_Pat

[Dathremar]

You can stick to your query and just add this in the where clause, when you are searching for letter 't':

AND ((SUBSTRING(title,1,3) <> 'the')

Edited May 22, 2013 by Dathremar

[jazzman1]

I've got a couple of options, I think. One would be to make a new column in my MySQL database called 'prefix', and populate that with sorted artist names (Sex Pistols, The, for example) and use my existing queery or I could re-write my query to return ALL results using the code above and then apply a letter filter in PHP. Both seem a little wasteful of resource, but I can't think of another way. Which, do you think, would be the better of the two options?

 

Yep, for sure you must modify the database design so that it adheres to the basic rules of normalization.

 

Create new entities for example artists and albums and established relationships one-to-many.

 

So each artist produces many albums, but each album comes from only one artist. 

 

Google - "Database normalization".

Edited May 22, 2013 by jazzman1

[TOA]

 

So each artist produces many albums, but each album comes from only one artist. 

 

 

What about collabo's? Wouldn't you want it searchable by both artists?

 

Normalization is also about knowing when to de-normalize.

 

I definitely agree that you need to re-visit your structure.

Edited May 22, 2013 by TOA

[Big_Pat]

Thanks for the replies. Dathremar, your idea works, but isn't quite what I was looking for. I would, as you say, ignore the 'The' bands when searching under T, but wouldn't show them up in, say 'R' for 'Ramones, The'.

 

The database structure is pretty much dependent on a CSV file output from my music program so, without a lot of fiddling, it'll be difficult to do anything other than add a column for prefix or amend the php as stated above.

[DavidAM]

Unless your database is the CSV file, you can restructure (normalize) when you load it. Of course, that means writing a PHP script to load the data rather than using INFILE.

 

One common solution to your original question is to store a "sort" value for certain fields. i.e. titleSort, artistSort, albumSort, album_artistSort. When you load the data, you store the value to be used for sorting/searching in this field. So, the artist field may be "The Ramones" (for display purposes) and the artistSort field would be "Ramones" (for sorting/searching).

 

If you are using a script to load the CSV file, then these values can be calculated just before the insert. If you are using INFILE to load the data, you can write a couple of UPDATE queries to set these fields after the load is complete. You might also look at your music program, it may be that it will export sort fields as well in which case you can maintain them there and send them to the CSV for import without any extra work.

[Big_Pat]

Thanks, David, that's what I was thinking about when I mentioned the new column 'prefix' in my original post.  For manageability, I didn't fancy running an update script every time I uploaded a new infile (although I suppose I could always code that into the input page) and I've asked the writers of my music program if there's a way to populate a custom field with a sortable version of title, artist and all.

 

In the meantime, though, while I'm waiting for them, I've gone ahead and written a script that uses the expanded query I mentioned in my original post. It works fine, but the downside is that it has to grab EVERY artist before php filters by letter. It doesn't take long at all with 'only' 30,000 titles, but it wouldn't be very practical for hundreds of thousands.

 

I appreciate everyone's help!

[Eiseth]

Have you tried the NOT LIKE in mysql?

 

// get band starting with T but not starting with THE

SELECT ... FROM ... WHERE title LIKE 't%' AND title NOT LIKE 'the%';

 

// get band starting with given letter (a) and starting with 'the a'

SELECT ... FROM ... WHERE title LIKE 'a%' AND title LIKE 'the_a%';

[Big_Pat]

Oh, that's good too! I'd have to put a few ORs in (the %, th\'%, a %, for example) but that'd work I reckon.

 

Also, the music place has come back to me and told me how to put the 'prefix' column in. So many options now!