clean urls mod_rewrite and mysql...howto

I use mod_rewrite all the time, but I was still asking myself how you
combine it with a database.

e.g. you have an article with the title : "Mac, windows or linux. Who
will tell?"

so I can get something like this : http:///www.domain.com/article/mac-w...-who-will-tell
What should I do?

1. just urlencode the title (but then I have problems with the ,
and .)

2. use a regular expression to filter out the special characters
(replace spaces and ", ." by "-" ... etc) and save the filtered-title
into the database? When I want to search for this article I just
perform a regular expression on the title string and search for it in
the databse: WHERE title = ".regularexpressionfunction($title)."

3. Or don't you save this title into the database, but do you always
perform this regular expression while searching in the database? e.g.
WHERE REGEX(title) LIKE '".regularexpressionfunction($title)."'

I have been googling for a long time, but I can't find a descent
article about this ...

thanks for your help!

On 7 Aug., 11:11, Geradeaus <ben.brughm...@gmail.com> wrote:
> I use mod_rewrite all the time, but I was still asking myself how you
> combine it with a database.
>
> e.g. you have an article with the title : "Mac, windows or linux. Who
> will tell?"
>
> so I can get something like this : http:///www.domain.com/article/mac-w...-who-will-tell
> What should I do?
>
> 1. just urlencode the title (but then I have problems with the ,
> and .)
>
> 2. use a regular expression to filter out the special characters
> (replace spaces and ", ." by "-" ... etc) and save the filtered-title
> into the database? When I want to search for this article I just
> perform a regular expression on the title string and search for it in
> the databse: WHERE title = ".regularexpressionfunction($title)."
>
> 3. Or don't you save this title into the database, but do you always
> perform this regular expression while searching in the database? e.g.
> WHERE REGEX(title) LIKE '".regularexpressionfunction($title)."'
>
> I have been googling for a long time, but I can't find a descent
> article about this ...
>
> thanks for your help!


In my opinion the best way to handle article titles in url's is to
store them into the database as an additional table field. A field
named 'title' contains article title and 'urltitle' contains the
prepared title used for urls.

You don't need to search inside 'urltitle' use the real title instead.
'urltitle' is necessary if you want to link to the results.

If someone requests the page http://www.domain.com/article/mac-wi...-who-will-tell,
apaches mod rewrite engine will maybe pass this to an script as an
parameter value pair like article=mac-windows-or-linux-who-will-tell.
Now, you can execute a statement like "select * from articles where
urltitle='mac-windows-or-linux-who-will-tell'" to get the content of
the article. This way requires urltitles to be unique, otherwise you
would get more results.

purcaholic

P.S.: You can also dive into the core of some blogs, cms, etc. to find
out, how they solved this...

On 7 Aug, 10:48, purcaholic <purcaho...@googlemail.com> wrote:

>
> If someone requests the pagehttp://www.domain.com/article/mac-windows-or-linux-who-will-tell,
> apaches mod rewrite engine will maybe pass this to an script as an
> parameter value pair like article=mac-windows-or-linux-who-will-tell.
> Now, you can execute a statement like "select * from articles where
> urltitle='mac-windows-or-linux-who-will-tell'" to get the content of
> the article. This way requires urltitles to be unique, otherwise you
> would get more results.
>


You can do this without using mod_rewrite (depending on how your
webserver is configured), if say your page is at

http://example.com/path/article.php

And the user goes to a page...

http://example.com/path/article.php/...blah-blah-blah

then the same *script* will be accessed - the trailing stuff can be
found in $PHP_SELF (or some other $_SERVER vars)

HTH

C.

purcaholic wrote:

> P.S.: You can also dive into the core of some blogs, cms, etc. to find
> out, how they solved this...

I'm sure that the very first CMS you'd investigate would be demiblog
<http://demiblog.org> ;-)

demiblog uses roughly the technique that purcaholic describes. Blog
articles are given URLs like '/blog/YYYY/MM/DD/FOOBAR/' and the relevant
fields in the database are:

article_date timestamp
url_part varchar

So when, say, the URL in my signature is visited, the CMS will search for
blog articles with article_date='2007-08-02' AND
url_part='command-line-again'. This way the article_date can be different
from the date the article was first created on, and different from its
last-modified date, and the url_part can be different from the
article_title, which is often useful if you have a post with a really long
title, but don't want your URL to be too long!

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 47 days, 16:04.]

Command Line Interfaces, Again
http://tobyinkster.co.uk/blog/2007/0...nd-line-again/

On 07.08.2007 11:11 Geradeaus wrote:
> I use mod_rewrite all the time, but I was still asking myself how you
> combine it with a database.
>
> e.g. you have an article with the title : "Mac, windows or linux. Who
> will tell?"
>
> so I can get something like this : http:///www.domain.com/article/mac-w...-who-will-tell
> What should I do?
>
> 1. just urlencode the title (but then I have problems with the ,
> and .)
>
> 2. use a regular expression to filter out the special characters
> (replace spaces and ", ." by "-" ... etc) and save the filtered-title
> into the database? When I want to search for this article I just
> perform a regular expression on the title string and search for it in
> the databse: WHERE title = ".regularexpressionfunction($title)."
>
> 3. Or don't you save this title into the database, but do you always
> perform this regular expression while searching in the database? e.g.
> WHERE REGEX(title) LIKE '".regularexpressionfunction($title)."'
>
> I have been googling for a long time, but I can't find a descent
> article about this ...
>
> thanks for your help!
>

A title to url conversion is simple

function title_to_url($title) {
preg_match_all('/\w+/', strtolower($title), $m);
return implode('-', $m[0]);
}

echo title_to_url("Mac, windows or linux. Who will tell?");

The opposite (given an url, find an article) is far more complicated.
There are two approaches: store url in the database, as others
suggested, or split an url to words and perform the full text search for
those words. The latter has an advantage that users will be able to use
different urls to access the same article, e.g.

http:///www.domain.com/article/linux-who-will-tell

or

http:///www.domain.com/article/linux-mac-windows

etc.




--
gosha bine

makrell ~ http://www.tagarga.com/blok/makrell
php done right ;) http://code.google.com/p/pihipi

On 7 aug, 15:24, gosha bine <stereof...@gmail.com> wrote:
> On 07.08.2007 11:11 Geradeaus wrote:
>
>
>
> > I use mod_rewrite all the time, but I was still asking myself how you
> > combine it with a database.
>
> > e.g. you have an article with the title : "Mac, windows or linux. Who
> > will tell?"
>
> > so I can get something like this : http:///www.domain.com/article/mac-w...-who-will-tell
> > What should I do?
>
> > 1. just urlencode the title (but then I have problems with the ,
> > and .)
>
> > 2. use a regular expression to filter out the special characters
> > (replace spaces and ", ." by "-" ... etc) and save the filtered-title
> > into the database? When I want to search for this article I just
> > perform a regular expression on the title string and search for it in
> > the databse: WHERE title = ".regularexpressionfunction($title)."
>
> > 3. Or don't you save this title into the database, but do you always
> > perform this regular expression while searching in the database? e.g.
> > WHERE REGEX(title) LIKE '".regularexpressionfunction($title)."'
>
> > I have been googling for a long time, but I can't find a descent
> > article about this ...
>
> > thanks for your help!
>
> A title to url conversion is simple
>
> function title_to_url($title) {
> preg_match_all('/\w+/', strtolower($title), $m);
> return implode('-', $m[0]);
>
> }
>
> echo title_to_url("Mac, windows or linux. Who will tell?");
>
> The opposite (given an url, find an article) is far more complicated.
> There are two approaches: store url in the database, as others
> suggested, or split an url to words and perform the full text search for
> those words. The latter has an advantage that users will be able to use
> different urls to access the same article, e.g.
>
> http:///www.domain.com/article/linux-who-will-tell
>
> or
>
> http:///www.domain.com/article/linux-mac-windows
>
> etc.
>
> --
> gosha bine
>
> makrell ~http://www.tagarga.com/blok/makrell
> php done right ;)http://code.google.com/p/pihipi

Thanks,

maybe the solution to save the title to url conversion is the best
indeed... thanks for all your help!