1

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)?

rob006
  • 21,383
  • 5
  • 53
  • 74
vdegenne
  • 12,272
  • 14
  • 80
  • 106

4 Answers4

5

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

Polynomial
  • 27,674
  • 12
  • 80
  • 107
  • 1
    +1. [This is the way to go](http://stackoverflow.com/questions/2647789/problem-in-displaying-a-url-slug-with-dash/2647828#2647828). – Shef Nov 21 '11 at 11:52
3

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.

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • 2
    Or do something like `/1234/name-of-whatever/` where `1234` is the ID of the object and `name-of-whatever` is ignored by the script. – Polynomial Nov 21 '11 at 11:48
  • 1
    @Polynomial: and it is even much better. You should put it as a separate answer – zerkms Nov 21 '11 at 11:49
  • @zerkms I thought about this solution but as I spent numerous minutes to figure my request out I can't think out of the box right now, I just wished there would had been other solutions, thanks anyway, I just wait for more answers but gotta be the checked answser – vdegenne Nov 21 '11 at 11:53
1

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.

fire
  • 21,383
  • 17
  • 79
  • 114
0

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

topherg
  • 4,203
  • 4
  • 37
  • 72