Pulling data from database and ignoring article in titles (the, a, an)

rogue3

I'm trying to pull data from my database for an index (where the user clicks on the letter and all titles starting with that letter are listed) and I want the SELECT command in the mysql statement to ignore the articles (The, An, A) for any titles that start with them.

I was successful doing this to SORT my data (it puts it in alphabetical order and ignores the THE, etc). But I'm having trouble getting the notation down so that it selects the data that way to. I didn't know if this would be better suited using PHP or if there is something in the MYSQL statement that would also achieve this.

Here is my statement so far that works when it comes to sorting, but not fetching...

$result = $db->sql_query("SELECT id, title, hits, reviewer, score, date, imagethumb, category FROM ".$prefix."_reviews WHERE UPPER(title) LIKE '$letter%' $querylang ORDER by TRIM(LEADING 'a ' FROM TRIM(LEADING 'an ' FROM TRIM(LEADING 'the ' FROM LOWER(`title`))))");

Should php be used for this in the $letter statement?

This precedes the query statement:

$letter = substr("$letter", 0,1);

dalecosp

Have you tried applying the same SQL logic to the WHERE clause itself?

SELECT id, title, hits, reviewer, score, date, imagethumb, category 
FROM ".$prefix."_reviews 
WHERE 
UPPER(TRIM(LEADING 'a ' FROM TRIM(LEADING 'an ' FROM TRIM(LEADING 'the ' FROM LOWER(`title`)))))
LIKE '$letter%'

NogDog

Probably my preferred solution would be to add a new column called something like "sort_title". Then the application code would be responsible when inserting/updating to fill it with something like "Wee Free Men, The" while title would still be "The Wee Free Men". That way you can put an index on it to optimize your sorting and/or searching. In fact, you could even make a stored function in the DBMS with an INSERT/UPDATE trigger to populate it, if you prefer.

rogue3

dalecosp;11052275 wrote:
Have you tried applying the same SQL logic to the WHERE clause itself?
SELECT id, title, hits, reviewer, score, date, imagethumb, category 
FROM ".$prefix."_reviews 
WHERE 
UPPER(TRIM(LEADING 'a ' FROM TRIM(LEADING 'an ' FROM TRIM(LEADING 'the ' FROM LOWER(`title`)))))
LIKE '$letter%' 

That worked! Thanks! I tried applying this logic to the statement like this but the only thing I forgot to do was put it in ( ) after the UPPER statement.

dalecosp

You're welcome. If you get time, please use the link under the "Thread Tools" dropdown to mark this thread as "RESOLVED".

And, welcome to PHPBuilder!