If I ever get great at php its going to be entirely because of you guys with all the advice Im getting here. And on that note, Im back for more !

I have two different pages with essentially the same problem:

PHP Code:

$query = " SELECT* FROM mreleased WHERE name LIKE '$letsel%'". 


PHP Code:

$result = mysql_query("SELECT * FROM mreleased WHERE genre='$pqualifier' ORDER BY name") or die(mysql_error()); 


In the first one it just looks for any name that starts with the given letter. Then for the second it orders the list by name within the subcategory.

My problem is that since this is a database of movies there is the incredibly irritating occurence of movie titles starting with the word 'The'. Now I could retrieve the entire database and then use php to remove 'The' and then (assuming there is a command to order in php, i havent looked yet) sort by name in PHP. But seeing as mysql already does the sorting/searching for me I would rather avoid that.

Is it possible to search for and cancel out part of a sql string?

Like SELECT * FROM mreleased WHERE name!='The %' & WHERE name LIKE'$letsel%'

That last line was just me fishing, from what Ive read you cant even have '&' in a mysql statement but hopefully that gives you an idea of what Im trying to achieve.

Thanks,
Truly.

Use negation and AND

... WHERE name NOT LIKE 'the %' AND name LIKE '%search_pattern%' ...

I have seen a lot of movie titles listed with "The" appended on the end like: Wizard Of Oz, The

Of course this has no benifit to you now if you already have tons of moview titles already strored!

Yeh I was thinking of that as an option but I wanted to a) find out how to do it and b) be able to still output the movie name correctly.

Thanks guys!

Sorry guys I think I did a bad explanation of my problem because the fix that christhirst gave me unfortunately wont work. I am sorting the database alphabetically and want to output movie titles with The in the correct spot as if The wasnt in the name. For example, The Attack of Chrishirst would be sorted under 'A'.

So the code that was suggested would just remove anything with 'The' at the beginning from the search completely.

Anyways I know how to do it using php. Just output everything not containing 'The' to an array, and then the stuff containing 'the' strip the openening 'The', merge the two arrays and then sort them and then add "the" back onto the ones that had it before.

Ok so that would work, but before I do that is there a simple and efficient sql way to do that so I dont slow down the load speed of the page by having it do pointless work.

Thanks again.

I found something that should work for you:

SELECT *
FROM table_name
ORDER BY REPLACE(UPPER(field_name), 'THE ', '');

I suggest you better to have two eparate fields in your table, one for original title with 'The' in the beginning, and the second for digested title, with 'The' at the end. This is to avoid calling functions on each query and thus increase performance.

SQL isn't the best way with the "The" problem - write something to loop through the db table, taking the title, searching for initial 'The' by string comparison and then re-insert the title in the form "xxx xxx, The" - as above...

Yeh I might end up taking you advice on that mtishetsky. Otherwise I will just make a php script to search it.

Anyways thanks for the suggestions guys.