Sorting &quot;the&quot;

Sorting "the"

cap97

Let's say I have a database table XP with a field "Title varchar(100)". Now I add an entry "The Amazing Adventures of Mike!" Is there a way, when displayed on a www page via PHP to have either PHP or MySQL ignore "The" from the title and sort by "Amazing" instead?

(Yeah sure, I could enter the title into the db as "Amazing Adventures of Mike, The", and I've been doing that, but I'm just curious if this can be done automatically while enterting the title as-is.)

Mike....

toma42

There are two ways. One, put this custom mysql function in place and use it in your queries:
http://216.239.37.100/search?q=cache😮0xYuH2PukwC:empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/+mysql+unprefix&hl=en&ie=UTF-8

two, use this php function I wrote for my site:

function sort_unprefix(&$array, $key = '', $index = false) {
        if (!$key) {
                uasort($array, 'sort_unprefix_uasort');
        } else {
                $cmp_define = "
                        \$a = unprefix(\$a['$key']);
                        \$b = unprefix(\$b['$key']);
                        return strcasecmp(\$a, \$b);
                ";
                $cmp = create_function('$a, $b', $cmp_define);
                if ($index) {
                        uasort($array, $cmp);
                } else {
                        usort($array, $cmp);
                }
        }
}

function sort_unprefix_uasort($a, $b) {
        $a = unprefix($a);
        $b = unprefix($b);
        return strcasecmp($a, $b);
}

This is a case insensitive sort and takes an array or multidimentional array.

If a multidimentional array is used you need to specify which key to sort on. the third argument, index, is a boolean for 'maintain index association'.

So,
$x = array(
[1] => array('title' => 'The stuff', 'other' => 'blah'),
[2] => array('title' => 'A Thing', 'other => 'halb')
);
to sort:
sort_unprefix($x, 'title');

superwormy

You guys always make things so hard on yourselves...

SELECT title, REPLACE(title, 'The ', '') AS sortbyme FROM table ORDER BY sortbyme

Edit: syntax was wrong: REPLACE(str,from_str,to_str)

http://www.mysql.com/doc/en/String_functions.html

toma42

Originally posted by superwormy
You guys always make things so hard on yourselves...

SELECT title, REPLACE('The', '', title) AS sortbyme FROM table ORDER BY sortbyme

And how would you write this query while ignoring all the other possible prefixes, superwormy? Mine takes into account 6 different ones.

UPDATE: forgot a rather important part of my code, sorry bout that cap97. If there are any prefixes you want to add or not remove, change the $_prefix_ignore array.

$_prefix_ignore = array(
        array('l' => 2, 's' => 'a '),
        array('l' => 3, 's' => 'an '),
        array('l' => 4, 's' => 'the '),
        array('l' => 3, 's' => 'la '),
        array('l' => 3, 's' => 'le '),
        array('l' => 2, 's' => "l'")
);

function unprefix($str) {
        global $_prefix_ignore;
        $start = 0;

    foreach ($_prefix_ignore as $p) {
            if (!strncasecmp($p['s'], $str, $p['l'])) {
                    $start += $p['l'];
                    break;
            }
    }
    return substr($str, $start);
}

toma42

SELECT title, REPLACE('The', '', title) AS sortbyme FROM table ORDER BY sortbyme

one more thing, concider titles such as:

Thespian
Theodore & The Chipmunks
The Other side of hell

They should sort to

The Other side of hell
Theodore & The Chipmunks
Thespian

Your example would sort them as

Theodore & The Chipmunks
The Other side of hell
Thespian

superwormy

Fine sheesh, then do it THIS way:

REPLACE(fieldname, 'The ', '') AS sortby

And he never asked about 'A', 'Le', 'La', sheesh guys, lighten up, it was meant to be amusing and to show another way that he might be able to do it.

superwormy

Oh and btw toma42, you can nest them to get rid of 'Le', 'La', etc. anyway.

SELECT *, REPLACE (REPLACE(descrip, 'LogoSoftwear.com ', ''), 'Caps ', '') AS sortby FROM logosoftwear ORDER BY sortby

toma42

sorry man. My criticism wan't ment personally.

I spent quite a bit of time researching this exact problem recently and I'm sure the solutions I presented are the best methods for implementing this.

While the original poster didn't specify A An, etc, he would have run into the problem quickly.

superwormy

Aight, no prob., no offense taken, and same goes for my posts.

On an off note, is there a particular reason you determined your method is better than mysql's REPLACE() function?

I can see certaintly if you plan on using other databases, I believe PostgreSQL's function has a diff. name, and dont' know which other databases support it...

superwormy

Also, note that if you use toma42 you can't do the sort in MySQL... you'll have to extract records, put them in a big multi-dimenstinall array, strip off the prefixes, then sort them again yes?

Not sure if tha tapplies to you, depends on what your SELECT is.

toma42

first, cap97, I was unable to use the mysql custom function because of my host and the number of other sites it hosts, but if you can use it, it's better than the php solution. Here's the compile string I used:

cc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME="/local/mysql" -DDATADIR="/local/mysql/var" -DSHAREDIR="/local/mysql/share/mysql" -DHAVE_CONFIG_H -DUSE_ALARM_THREAD -O3 -DDBUG_OFF -I/usr/include/mysql -shared -o unprefix.so unprefix.c

As for why my method is better:
Concider a large web site using mysql with 12 pages that each need to sort while ignoring the prefix. If you decide to add or remove a prefix, you will need to edit every page to implement that. With my php way you only need to edit one array. Using the mysql function you would need to edit the .c and recompile it.

Also, concider the speed differences between mysql doing nested replaces vs php thumping through an array. PHP will do it faster by a landslide. Also, you don't need to have the database do any sorting at all in your queries (saving some time) if using the php stuff since the php functions will change the sorting anyway.

The ideal solution for this problem is the mysql custom function. I only wrote the php stuff because my host didn't want to implement it. My code was written using the .c mysql function as a template. Of course, the implementation in php was much different.

Were you to do the same thing in postgresql, a custom function there would probably also be the best solution, but my custom function writing in postgresql is limited to more simple functions than what is required in this case, so I can't comment authoritativly on this.

toma42

Originally posted by superwormy
Also, note that if you use toma42 you can't do the sort in MySQL... you'll have to extract records, put them in a big multi-dimenstinall array, strip off the prefixes, then sort them again yes?

Not sure if tha tapplies to you, depends on what your SELECT is.

Correct. Here's the complete code to do this using my smarty and pear application structure (detailed at http://smarty.incutio.com/?page=SmartestSmartyPractices ):

$sql = 'select * from xp';
$data = $db->getAll($sql);
sort_unprefix($data, 'title');

superwormy

Did you know you have the same name as my boss? Sorry little off topic there.... hehe intesting though :-)

As an added note, often times in a case like this it's nice to write a cute little PHP function to actually build your MySQL query, which I would probably do now that you mention this issue:

Concider a large web site using mysql with 12 pages that each need to sort while ignoring the prefix. If you decide to add or remove a prefix, you will need to edit every page to implement that. With my php way you only need to edit one array. Using the mysql function you would need to edit the .c and recompile it

Build a little PHP function that takes an array and a fieldname that builds your MySQL ( or other database, make it expandable! ) REPLACE() statement for the database.

Then just change the array, and every function would change too.

cap97

Ladies, ladies, no need to fight. There's plenty of me to go around. :p

toma42
Your function sounds like the ideal solution, but I'm afraid it's a bit over my head as I have no idea how to apply it my site. 😕

superwormy
I'm always amazed at what MySQL can do. Never knew that one, but like toma42 said, it won't work if I can only alter one type at a time. Any way to replace "THE" AND "A" and have it sort by sortname?

🙂

superwormy

Read my previous post :-)

You can nest them.

SELECT *, REPLACE (REPLACE(descrip, 'LogoSoftwear.com ', ''), 'Caps ', '') AS sortby FROM logosoftwear ORDER BY sortby

The only reason toma's sounds confusing is because it uses Smarty, which you probably arn't using. It is pretty simple though, and effective as well.

toma42

How to apply it to your site:

put the text from this url into a file such as 'unprefix.php':
http://db.etree.org/source/include/sort_unprefix.phps

Then, on your page you need to sort this way, use

require_once 'unprefix.php';

then, to use it in your code:

$sql = 'select * from xp';
$data = $db->getAll($sql);
sort_unprefix($data, 'title');

After the sort_unprefix call $data will be sorted by title.

Good luck