user-friendly URLs reliable with the database

Question

I have a database containing a table named songs with a field title. Now If my url is http://www.foo.com/songs/xxx (xxx = the title of the song), apache is silently redirecting to a page that looks similar to : /song.php?title=xxx.

To embellish the URLs I convert spaces into underscores (cause I know some browser display %20 instead of space, not%20really%20user%20friendly%20ya%20know%20what%20i%20mean).

There's a snag cause if the title contains spaces and underscores (e.g. DJ_underscore fx) and the script converts it into DJ_underscore_fx the sql :

    select * from songs where songs.title=xxx

can't find it.

here's the sketch to be more specific:

• a script fetches the different titles in the database
• converts all the space into underscore ( e.g. name_of the song -> name_of_the_song )
• echo them as links ( e.g. <a href="/songs/name_of_the_song">name_of_the_song</a> )
• the user clicks on the link and requests the document
• apache is silently redirecting ( e.g. /songs/name_of_the_son -> /song.php?title=name_of_the_song )
• song.php fetches the specific data ( e.g. select * from songs where songs.title=name_of_the_song )

ok you see that there's no entry in the database that looks like name_of_the_song but name_of the song.

How can I manage the whole so that my URL remains clear and the title field is not restricted to a certain amount of values (can have spaces, underscore, dashes, well anything)?

Answer 1

Use something like /1234/name-of-page/ where 1234 is the primary key ID of the row and name-of-page is ignored by your script.

This gives a link directly to the primary key of the entry in the table, which will give you several benefits:

1. No need to have duplicate ID fields.
2. Fast indexing on SELECT queries.
3. You still get the readability and SEO benefits of a "pretty" URL.

You might notice that StackOverflow itself does exactly this:

/questions/8211267/user-friendly-urls-reliable-with-the-database/

Which probably gets re-written to something like:

question.php?id=8211267

Answer 2

What your trying to achieve is definetly the wrong way, you could have hundreds of variations to lookup in your database and is also bad for SEO.

Start by setting a rule that all URL's have _ to seperate the space, that's how most site URL's are done (digg.com being an example).

Then create a seperate field that stores the URL e.g.

title     | url
song name | song_name

Then do your lookup based on the URL field.

For legacy reasons you could also replace any spaces with _ in your lookup script when you receive the title from the GET before doing the database query.

Answer 3

well, if you want spaces in the url, people will have it uri encoded for transit. if rather than replacing all _ with spaces, just use a uridecoder (can't remember the exact title). it would still allow for spaces to be typed. On the displayer, the shown text in the link, cant you do an str_replace to convert %20 in spaces?

Either that of have a computer friendly version of the title (that doesn't use spaces, but underscores) and a user friendly column that does have the spaces

Answer 4

Just add another field that will keep the exact name used in URL. And when you have some "duplicates" - just append them with _2, _3 etc or give a way for user to edit and give another name manually.