Order by title, but skip "The", "A", "An", etc
5 posts by 3 authors in: Forums > CMS Builder
Last Post: December 11, 2012 (RSS)
By zip222 - December 7, 2012
A
An
The
"A
"An
"The
Re: [zip222] Order by title, but skip "The", "A", "An", etc
The easiest way to do this is using this orderBy statement in your getRecords function:
list($blogRecords, $blogMetaData) = getRecords(array(
'tableName' => 'blog',
'loadUploads' => true,
'allowSearch' => false,
'orderBy' => 'IF(LEFT(title,2)="A ",SUBSTRING(title FROM 3),IF(LEFT(title,3)="An ",SUBSTRING(title FROM 4),IF(LEFT(title,4)="The ",SUBSTRING(title FROM 5),title)))',
));The only problem with this method is that it's a fairly server intensive.
Another method is to have a second field called something like title_search in the section, which contains the same text as the title field, but removing A an and the from the beginning of the text. Then setting the orderBy field to title_search in your getRecords function.
Thanks!
PHP Programmer - interactivetools.com
Re: [zip222] Order by title, but skip "The", "A", "An", etc
By mizrahi - December 11, 2012
Re: [zip222] Order by title, but skip "The", "A", "An", etc
By gregThomas - December 11, 2012 - edited: December 11, 2012
I modified the statement so that it includes anything starting with a quotation mark as well:
'orderBy' => 'IF(LEFT(title,2)="A ",SUBSTRING(title FROM 3),IF(LEFT(title,1)="\"",SUBSTRING(title FROM 2),IF(LEFT(title,3)="An ",SUBSTRING(title FROM 4),IF(LEFT(title,4)="The ",SUBSTRING(title FROM 5),title))))',Thanks
Greg
PHP Programmer - interactivetools.com
Re: [zip222] Order by title, but skip "The", "A", "An", etc
By mizrahi - December 11, 2012