Smart sorting of Film Titles

[evanagee]

Greetings all,

I have a pretty typical problem that I'm hoping someone can help me solve.

I have a database of film names and some of them are formatted like this:

The Lord of the Rings: The Fellowship of the Ring

When I pull the list of all of the movies out of the database I'm putting them into an array and then sorting that array. Once the array is sorted I'm printing each entry out. Here's my code:

	$movies = array();
	$query = mysql_query("SELECT title, release_year from films",$db);
	while ($row = mysql_fetch_array($query)) {
		$title = $row["title"]; 
		$year = $row["release_year"];
		array_push($movies, "$title ($year)");
	}
	sort($movies);

foreach($movies as $movie) {
	echo "<li>$movie</li>";
}

The problem is that on movies, like the "The Lord of the Rings", the "The, An or A" should be ignored when sorting the titles and the movie should show up with the "L" movies, not the "T" movies.

Does anyone have a nifty way of accomplishing it either through PHP or MySQL?

[paulnaj]

Hi evangee,

You could use the mysql functions to strip out the 'The ' from the beginning of each title.

I haven't check the syntax of these functions, so it probaly won't work as it stands, but you get the idea.

IF(SUBSTR(title, 0, 4) = 'The ', SUBSTR(title, 4), title) as title

I'd check each function out in the manual.

Paul.

[devinemke]

this is untested:

$query = mysql_query('SELECT title, release_year from films where id > 0', $db);
$movies = array();
while ($row = mysql_fetch_array($query))
{
	if (strtolower(substr($row['title'], 0, 4)) == 'the ')
	{
		$title = substr($row['title'], 4) . ', ' . substr($row['title'], 0, 4);
	}
	else
	{
		$title = $row['title'];
	}
	$movies[] = $title . ' (' . $row['release_year'] . ')';
}

sort($movies);

foreach($movies as $movie)
{
	echo '<li>' . $movie . '</li>';
}

[evanagee]

I see what you're saying with this, thanks!

However, I still want the full, proper title to be printed out, just in the correct order with the other titles. See this page for an example:

http://movieweb.com/movies/genres.php?g=0&s=A

(even though they have some php errors at the top of the page)

Thanks!
Evan

[paulnaj]

SELECT title, IF(SUBSTR(title, 0, 4) = 'The ', SUBSTR(title, 4), title) as sort_title
FROM films
ORDER BY sort_title

[mtmosier]

You could use usort. (Untested)

usort($movies, 'mcmp');

function mcmp($a, $b) {
    if (strtolower(substr($a, 0, 4)) == 'the ') {
        $a = substr($a, 4) . ', ' . substr($a, 0, 4);
    } 
    if (strtolower(substr($b, 0, 4)) == 'the ') {
        $b = substr($b, 4) . ', ' . substr($b, 0, 4);
    }
    return strcmp($a, $b);
}

[paulnaj]

My advice is to always get mysql to do as much of the work as possible, and only let PHP do what mysql can't. For large queries, it's much much more efficient ...

SELECT title, IF(SUBSTR(title, 0, 4) = 'The ', CONCAT(SUBSTR(title, 4), ', The'), title) as sort_title
FROM films
ORDER BY sort_title

Then just use title in your display.

Paul.

PS. Remember to look up the proper syntax for these functions!!!

[evanagee]

Okay, I've tried to integrate what you've mentioned above:

	$movies = array();
	$query = mysql_query("SELECT title, IF(SUBSTRING(title, 0, 4) = 'The ', CONCAT(SUBSTRING(title, 4), ', The'), title) as sort_title, release_year from films order by sort_title",$db);
	while ($row = mysql_fetch_array($query)) {
		$title = $row["title"]; 
		$year = $row["release_year"];
		array_push($movies, "$title ($year)");
	}
	$count = count($movies);

echo "There are currently $count movies in the database!<ul>";
foreach($movies as $movie) {
	echo "<li>$movie</li>";
}
echo "</ul>";

However, this is the list that it gives me:

* 3 Men and a Baby (1987)
* A Boy, a Girl, and a Bike (1947)
* A Boy, a Girl, and a Dog (1946)
* A Child Is a Wild Young Thing (1976)
* A Girl, a Guy, and a Gob (1941)
* A Good Dissonance Like a Man (1977)
* A Guy, a Gal, and a Pal (1945)
* A Hero Ain\'t Nothin\' but a Sandwich (1977)
* A House on a Hill (2003)
* A Joke of Destiny Lying in Wait Around the Corner Like a Street Bandit (1983)
* A Man and a Woman: 20 Years Later (1986)
* A Man, a Woman, and a Bank (1979)
* A Minute to Pray, a Second to Die (1968)
* A Portrait of the Artist as a Young Man (1979)
* A Reason to Live, a Reason to Die (1974)
* A Room With a View (1985)
* A View to a Kill (1985)
* A Wave, a Wac, and a Marine (1944)
* A Whale of a Tale (1977)
* An Eye for an Eye (1981)
* An Officer and a Gentleman (1982)
* An Unsuitable Job for a Woman (1981)
* Give a Dog a Bone (1968)
* He\'s a Woman, She\'s a Man (1994)
* Like a Crow on a June Bug (1972)
* Live a Little, Love a Little (1968)
* Secuestro: a Story of a Kidnapping (1994)
* The Englishman Who Went Up a Hill but Came Down a Mountain (1995)
* The Lord of the Rings: The Fellowship of the Ring (2001)
* The Lord of the Rings: The Return of the King (2003)
* The Lord of the Rings: The Two Towers (2002)
* They Went That-a-Way and That-a-Way (1978)
* When a Man Loves a Woman (1994)

Also, movies starting with "A " and "An " should be treated the same way as movies starting with "The ".

Thanks guys!

[paulnaj]

Did you check the syntax?

For example, in SUBSTR, should that be a 1 or is 0 correct?

[godwheel]

This is not related to the actual programming aspect, but here are my two cents anyway.

You should display "An" an "A" as the correct movie title when it shows it to the user. For example, a person looking for the title "A Child Is a Wild Young Thing" will look under A not under "Child Is a Wild Young Thing". The same applies to "An" simply because the title makes more sense.

In addition, you should add ",The" at the end of the title for "Lord of The Rings."

Hope that helps.