Correct approach to ensure a rewritten URL always finds what you expect it to?

I'm having a play around with using mod_rewrite to provide a better looking URL to the user, and to search engines - as is fairly common practice on many dynamic websites.

So for instance, I began with:

http://www.mydomain.com/profile/view.php?id=136

Assuming user number 136 is called, for the sake of argument Bob T. Smith the 3rd (apologies Bob, if you exist).

As far as I understand it, I must have a variant of Bob's name in my 'nasty' URL, because you can only rewrite what exists - so i'd need to change my code so that this URL finds the profile we're looking for:

http://www.mydomain.com/profile/view.php?id=bob-t-smith-the-3rd

can now become

http://www.mydomain.com/profile/bob-t-smith-the-3rd

I'm using a custom function to convert underscores & spaces to dashes, strip any non alpha-numeric characters and then lower case everything to make the URL look really nice.

And so within my page, rather than looking up on the very unique primary key that is '136', I now have to look up on the users name - but the sanitized version, so i'm using something like this - because MySQL can't do regular expressions:

SELECT LOWER(REPLACE(REPLACE(REPLACE('Bob T. Smith the 3rd'," ","-"),"_","-"),".",""));

The name would obviously be replaced by whatever came in on the URL, cleaned up.

The problem i'm expecting is that without wanting to chain together MySQL REPLACE() functions to handle every conceivable illegal character (which would be confusing, hard to maintain, and probably inefficient), and without the ability to do regular expressions on the query side, how can I ensure the user profile is found and gets returned every single time?

If Bob signed up as 'Bob T. Smith! (The 3rd)' - I would still clean that up in PHP to bob-t-smith-the-3rd because of the preg_replace that strips the exclamation point and the parentheses, but my query - unless I added more REPLACE() functions, wouldn't find that user.

Should I just ensure that users can only sign up using A-Z, 0-9, dash and underscore?

Should I in some way continue to pass the unique numerical ID but just omit it from the rewrite rule somehow?

Am I missing the point e开发者_StackOverflowntirely? Is there an accepted way of doing this that I just haven't thought of?

Thanks :-)

Correct me if I am missing something but why don't you just strip the username from the dashes you added in your rewrite rule when the php script is called?

$undashed_username = explode("-",$dashed_username);
$username = implode(" ",$undashed_username);

which you can then search for on your database.

Why don't you make this request?

http://www.mydomain.com/profile/bob-t-smith-the-3rd?id=136

So you have a nice URL and you do not need to deal with actually searching for the username.

If you fear to have ? inside of your URL you can do this as well:

http://www.mydomain.com/profile/136/bob-t-smith-the-3rd

And then parsing the numeric information from the request string.

This will also reduce the work of the database as well.