Title Sort MySQL Results

MySQL's ORDER BY is great for sorting most things, but when you're trying to sort books, movies, or something of that nature, it fails to take into account that indefinite articles (a, an) and definite articles (the) should be ignored. You could create a second column with the "sort value," but that's wasteful of both user time and data space, plus it's prone to errors. So, the solution is to devise a MySQL query that somehow sorts it properly. That's what I've done here… All you have to do is copy and paste.

SELECT *, CASE WHEN SUBSTRING(`title`,1,2) LIKE 'a ' THEN SUBSTRING(`title`,3) WHEN SUBSTRING(`title`,1,3) LIKE 'an ' THEN SUBSTRING(`title`,4) WHEN SUBSTRING(`title`,1,4) LIKE 'the ' THEN SUBSTRING(`title`,5) ELSE `title` END AS `title_sort` FROM `books` ORDER BY `title_sort`, `title`

I've also included an example so you can see how it works using the novels that John O'Hara wrote. In the first table, you'll see a traditional sort. In the second, you'll see that the definite and indefinite articles were appropriately ignored:

1.   $result = mysql_query('SELECT * FROM `books` ORDER BY `title`');
2.   echo '<table class="example">';
3.   echo '<thead><tr><td>Title</td><td>Author</td><td>Year</td></tr></thead>';
4.   echo '<tbody>';
5.   while ($row = mysql_fetch_array($result)) {
6.   	echo '<tr><td>'.stripslashes($row['title']).'</td><td>'.stripslashes($row['author']).'</td><td>'.$row['year'].'</td></tr>';
7.   }
8.   echo '</tbody></table>';

TitleAuthorYear
A Rage to LiveJohn O'Hara1949
Appointment in SamarraJohn O'Hara1934
BUtterfield 8John O'Hara1935
Elizabeth AppletonJohn O'Hara1960
Files on ParadeJohn O'Hara1939
From the TerraceJohn O'Hara1959
Lovey Childs: A Philadelphian's StoryJohn O'Hara1969
Ourselves to KnowJohn O'Hara1960
Pal JoeyJohn O'Hara1940
Ten North FrederickJohn O'Hara1955
The Big LaughJohn O'Hara1962
The EwingsJohn O'Hara1970
The Farmers HotelJohn O'Hara1951
The InstrumentJohn O'Hara1967
The Lockwood ConcernJohn O'Hara1965
The Second EwingsJohn O'Hara1972
1.   $result = mysql_query('SELECT *, CASE WHEN SUBSTRING(`title`,1,2) LIKE \'a \' THEN SUBSTRING(`title`,3) WHEN SUBSTRING(`title`,1,3) LIKE \'an \' THEN SUBSTRING(`title`,4) WHEN SUBSTRING(`title`,1,4) LIKE \'the \' THEN SUBSTRING(`title`,5) ELSE `title` END AS `title_sort` FROM `books` ORDER BY `title_sort`, `title`');
2.   echo '<table class="example">';
3.   echo '<thead><tr><td>Title</td><td>Author</td><td>Year</td></tr></thead>';
4.   echo '<tbody>';
5.   while ($row = mysql_fetch_array($result)) {
6.   	echo '<tr><td>'.stripslashes($row['title']).'</td><td>'.stripslashes($row['author']).'</td><td>'.$row['year'].'</td></tr>';
7.   }
8.   echo '</tbody></table>';

TitleAuthorYear
Appointment in SamarraJohn O'Hara1934
The Big LaughJohn O'Hara1962
BUtterfield 8John O'Hara1935
Elizabeth AppletonJohn O'Hara1960
The EwingsJohn O'Hara1970
The Farmers HotelJohn O'Hara1951
Files on ParadeJohn O'Hara1939
From the TerraceJohn O'Hara1959
The InstrumentJohn O'Hara1967
The Lockwood ConcernJohn O'Hara1965
Lovey Childs: A Philadelphian's StoryJohn O'Hara1969
Ourselves to KnowJohn O'Hara1960
Pal JoeyJohn O'Hara1940
A Rage to LiveJohn O'Hara1949
The Second EwingsJohn O'Hara1972
Ten North FrederickJohn O'Hara1955

(If you're wondering why I have a MySQL table full of John O'Hara novel titles laying around, check out Lantenengo.com.)


Comments

Loading…

This post was published on August 18th, 2011 by Robert James Reese in the following categories: MySQL and PHP. Before using any of the code or other content in this post, you must read and agree to our terms of use.