[RESOLVED] Static, Long, Filenames using underscores

[Tim_Silva]

Good evening to all,

I've noticed that more and more sites are using the long static filenames lately and I am considering doing the same. Although I already use a Rewrite Rule to give my dynamic files a static name I do not know how to do it using long names. Here is what I mean.

What I have now:

1. The code pulls an article from MySQL based on it's id, say id=12345 Title=Italy Wins World Cup For Fourth Time

2. Instead of the file being named /article.php/?id=12345 the Rewrite changes it to /article_12345.htm

What I would like to do differently is to change the filename to /italy_wins_world_cup_for_the_fourth_time.htm

Any pointers would be appreciated.

Tim

[suntra]

Well you would need to add an extra column in your DB to put the "URL title" of the article...

The normal title is "Italy Wins World Cup For Fourth Time"
The URL title is "italy_wins_world_cup_for_the_fourth_time"

And then, you simply use something like this
RewriteRule ^{([\w_]+).html$} article.php?url_title=$1

[Tim_Silva]

Suntra,

Thanks for your quick reply. But there must be a better way to do it. one problem with your solution, for example, is the thousands of entries already in the DB.

Many sites do this on the fly (digg and netscape, just to name two).

These sites take the title of your story, remove capitalizations, add underscores between words, and that becomes the filename.

Regards,

Tim

[alimadzi]

You could just write a script to run through the current entries in your DB table, do a preg_replace() on the title, and store the result in the new column that Suntra suggested. Then modify your existing story insert code to do the same thing each time a new story is added.

[bradgrafelman]

Tim Silva wrote:

one problem with your solution, for example, is the thousands of entries already in the DB.

Don't you already have a separate "title" column for the stories in your DB? If not, sounds like the problem lies in the bad DB design, not the method.

[rowanparker]

He has a title just not a URL title as someone suggested.
Why not just convert the title to a url-friendly title each time the script is loaded instead of storing another one in a db?

[hagen00]

Just by the by, you might want to have your titles delimited by a dash and not an underscore. Since a dash is the best way in SEO to indicate a space. An underscore indicates that it's part of the same word. If you don't believe me, look it up.

[Tim_Silva]

Why not just convert the title to a url-friendly title each time the script is loaded instead of storing another one in a db?

Do you mean a script that creates a pseudo-url even before the page is loaded? How is that possible? I thought the only way this can be done is by a Rewrite Rule and by creating a separate row just for the url (the titleUrl).

What I was thinking though, is this:

1. Write and run a one-time-only script that will retrieve all the current "Titles" and populate the new row [titleUrl] with the Italy-Wins-blah-blah-blah thingy.

2. Rewrite my current Insert Article Form so that now every time I hit send it will insert the adapted [title] row into the new [titleUrl] row. That way I won't have to manually insert a title url row every time I insert an article.

I would appreciate any help on accomplishing these two steps.

Thanks again,

Tim

[Tim_Silva]

Ok. I was able to get the first step done. With the code below I can get all my titles, remove white spaces, remove uppercase letters, and any punctuation that is not url standard (all that I could think of).

$patterns[0] = '/ /';
$patterns[1] = '/:/';
$patterns[2] = '/\?/';
$patterns[3] = '/\'/';
$patterns[4] = '/\"/';
$patterns[5] = '/,/';
$patterns[6] = '/--/';
$patterns[7] = '/\./';
$patterns[8] = '/\!/';
$patterns[9] = '/\\/';
$patterns[10] = '/\//';
$replacements[10] = '-';
$replacements[9] = '-';
$replacements[8] = '';
$replacements[7] = '-';
$replacements[6] = '';
$replacements[5] = '';
$replacements[4] = '';
$replacements[3] = '';
$replacements[2] = '';
$replacements[1] = '-';
$replacements[0] = '-';
ksort($patterns);
ksort($replacements);

echo strtolower(preg_replace($patterns, $replacements, $info['title'])); 

Now, with this code I can get: italy-wins-world-cup-for-the-fourth-time

I also created a new db column called UrlTitle.

My problem now is: How do I apply this code to populate this new column with the thousands of title rows I have in my db?

Thanks again for the help.

Tim

[bradgrafelman]

Just so you know, you could reduce your code to simply:

$patterns[] = '@[/\\\.: ]@';
$replacements[] = '-';

$patterns[] = '/[!,"\'\?]|--/';
$replacements[] = '';

echo strtolower(preg_replace($patterns, $replacements, $info['title']));

Tim Silva wrote:

How do I apply this code to populate this new column with the thousands of title rows I have in my db?

Well that's easy - just SELECT all rows that don't have a value for this new column, and then perform UPDATE statements as you loop through them.

[Tim_Silva]

Well that's easy - just SELECT all rows that don't have a value for this new column, and then perform UPDATE statements as you loop through them.

Okay, say I have the following fields:' id', 'title', 'content', date, and now the new field ('urlTitle').

You told me to do a SELECT of all rows that DON'T have the new value?

$data = mysql_query("SELECT id, title, content, date FROM $usertable ORDER BY date") 
or die(mysql_error()); 
while($info = mysql_fetch_array( $data ))

and we have the code:

echo strtolower(preg_replace($patterns, $replacements, $info['title'])); 

Could you please tell me how to do the UPDATE with the loop?

Thanks a lot.

[bradgrafelman]

Assuming id is a primary unique key, you don't really need to select the content and date columns. Other than that, you're just doing a simple UPDATE:

while($info = mysql_fetch_array($data)) {
    $upd_query = sprintf("UPDATE %s SET urlTitle = '%s' WHERE id = %d LIMIT 1",
        $usertable,
        mysql_real_escape_string(strtolower(preg_replace($patterns, $replacements, $info['title']))),
        $info['id']);
    $upd_exec = mysql_query($upd_query);
}

[Tim_Silva]

Thanks for the code. I run it but unfortunately nothing happened. This is how I implemented it:

<?php
$hostname="myhostname.net";
$username="myusername";
$password="mypassword";
$dbname="mydbname";
$usertable="myusertable";

$patterns[] = '@[/\\\.: ]@';
$replacements[] = '-';

$patterns[] = '/[!,"\'\?\(\)]|--/';
$replacements[] = '';

mysql_connect($hostname,$username,$password) or die(mysql_error());
mysql_select_db("$dbname") or die(mysql_error());

while($info = mysql_fetch_array($data)) {
    $upd_query = sprintf("UPDATE %s SET urlTitle = '%s' WHERE id = %d LIMIT 1",
        $usertable,
        mysql_real_escape_string(strtolower(preg_replace($patterns, $replacements, $info['title']))),
        $info['id']);
    $upd_exec = mysql_query($upd_query);
} 
?>

Thanks again for your willingness to help.

[Tim_Silva]

I was just thinking, if I am going to preg_replace 'title' don't I have to SELECT it somewhere? Could this be the reason for the code not to work?

Tim

[Tim_Silva]

Yes. I had to do a SELECT first.

Thanks a lot. It works fine. All I have to do now is to incorporate the same idea in my form for all future entries.

Thanks again,

Tim