MySQL change call based on .htaccess

[Merri]

I need to replace old file path entries in a database. The only source for replacing I have is a .htaccess file with redirection rules. Is there any "neat" conversion call that I can use into the db that doesn't affect the rows that do not match to the .htaccess rules? Any easy way to go on this?

Here is a sample of the .htaccess file:

RedirectMatch /keskimaa/avatar/tumbe_(.*) http://www.vihrealohikaarme.com/avat/lahjoitetut/tumbe_$1
RedirectMatch /keskimaa/avatar/user_(.*) http://www.vihrealohikaarme.com/avat/lahjoitetut/user_$1
RedirectMatch /keskimaa/avatar/tg_(.*) http://www.vihrealohikaarme.com/avat/lahjoitetut/tg_$1
RedirectMatch /keskimaa/avatar/sava_(.*) http://www.vihrealohikaarme.com/avat/lahjoitetut/sava_$1
RedirectMatch /keskimaa/avatar/sndr_(.*) http://www.vihrealohikaarme.com/avat/lahjoitetut/sndr_$1
RedirectMatch /keskimaa/avatar/sowm_(.*) http://www.vihrealohikaarme.com/avat/lahjoitetut/sowm_$1
RedirectMatch /keskimaa/avatar/mon_(.*) http://www.vihrealohikaarme.com/avat/lahjoitetut/mon_$1
RedirectMatch /keskimaa/avatar/rm_(.*) http://www.vihrealohikaarme.com/avat/lahjoitetut/rm_$1
RedirectMatch /keskimaa/avatar/tara(.*) http://www.vihrealohikaarme.com/avat/lahjoitetut/tara$1
RedirectMatch /keskimaa/avatar/tunt(.*) http://www.vihrealohikaarme.com/avat/lahjoitetut/tunt$1
RedirectMatch /keskimaa/avatar/wolf(.*) http://www.vihrealohikaarme.com/avat/sudet/wolf$1
RedirectMatch /keskimaa/avatar/rohan_(.*) http://www.vihrealohikaarme.com/avat/rohan/_$1
RedirectMatch /keskimaa/avatar/kotka(.*) http://www.vihrealohikaarme.com/avat/elaimet/kotka$1
RedirectMatch /keskimaa/avatar/lintu(.*) http://www.vihrealohikaarme.com/avat/elaimet/lintu$1
RedirectMatch /keskimaa/avatar/lokki(.*) http://www.vihrealohikaarme.com/avat/elaimet/lokki$1
RedirectMatch /keskimaa/avatar/poni(.*) http://www.vihrealohikaarme.com/avat/elaimet/poni$1
RedirectMatch /keskimaa/avatar/asfa(.*) http://www.vihrealohikaarme.com/avat/elaimet/asfa$1
RedirectMatch /keskimaa/avatar/brego(.*) http://www.vihrealohikaarme.com/avat/elaimet/brego$1
RedirectMatch /keskimaa/avatar/vuori(.*) http://www.vihrealohikaarme.com/avat/luonto/vuori$1
RedirectMatch /keskimaa/avatar/salama(.*) http://www.vihrealohikaarme.com/avat/luonto/salama$1
RedirectMatch /keskimaa/avatar/purkaus(.*) http://www.vihrealohikaarme.com/avat/luonto/purkaus$1
RedirectMatch /keskimaa/avatar/kukka(.*) http://www.vihrealohikaarme.com/avat/luonto/kukka$1
RedirectMatch /keskimaa/avatar/joki(.*) http://www.vihrealohikaarme.com/avat/luonto/joki$1
RedirectMatch /keskimaa/avatar/tl_(.*) http://www.vihrealohikaarme.com/avat/luonto/tl_$1
RedirectMatch /keskimaa/avatar/fae(.*) http://www.vihrealohikaarme.com/avat/fantasia/fae$1
RedirectMatch /keskimaa/avatar/fairy(.*) http://www.vihrealohikaarme.com/avat/fantasia/fairy$1
RedirectMatch /keskimaa/avatar/fluffy(.*) http://www.vihrealohikaarme.com/avat/fantasia/fluffy$1
RedirectMatch /keskimaa/avatar/lohik(.*) http://www.vihrealohikaarme.com/avat/fantasia/lohik$1
RedirectMatch /keskimaa/avatar/unicorn(.*) http://www.vihrealohikaarme.com/avat/fantasia/unicorn$1
RedirectMatch /keskimaa/avatar/rotk_(.*) http://www.vihrealohikaarme.com/avat/elokuva/rotk_$1
RedirectMatch /keskimaa/avatar/ttt(.*) http://www.vihrealohikaarme.com/avat/elokuva/ttt$1
RedirectMatch /keskimaa/avatar/lorien_(.*) http://www.vihrealohikaarme.com/avat/elokuva/lorien_$1
RedirectMatch /keskimaa/avatar/balrog(.*) http://www.vihrealohikaarme.com/avat/tolkien/balrog$1
RedirectMatch /keskimaa/avatar/bruinen(.*) http://www.vihrealohikaarme.com/avat/tolkien/bruinen$1
RedirectMatch /keskimaa/avatar/emynmuil(.*) http://www.vihrealohikaarme.com/avat/tolkien/emynmuil$1
RedirectMatch /keskimaa/avatar/fango(.*) http://www.vihrealohikaarme.com/avat/tolkien/fango$1
RedirectMatch /keskimaa/avatar/kontu(.*) http://www.vihrealohikaarme.com/avat/tolkien/kontu$1
RedirectMatch /keskimaa/avatar/laiva(.*) http://www.vihrealohikaarme.com/avat/tolkien/laiva$1
RedirectMatch /keskimaa/avatar/luthien(.*) http://www.vihrealohikaarme.com/avat/tolkien/luthien$1
RedirectMatch /keskimaa/avatar/matkalaiset(.*) http://www.vihrealohikaarme.com/avat/tolkien/matkalaiset$1
RedirectMatch /keskimaa/avatar/mustaratsastaja(.*) http://www.vihrealohikaarme.com/avat/tolkien/mustaratsastaja$1
RedirectMatch /keskimaa/noitakuningas(.*) http://www.vihrealohikaarme.com/avat/tolkien/noitakuningas$1
RedirectMatch /keskimaa/avatar/tolkien(.*) http://www.vihrealohikaarme.com/avat/tolkien/tolkien$1
RedirectMatch /keskimaa/avatar/tove(.*) http://www.vihrealohikaarme.com/avat/tolkien/tove$1
RedirectMatch /keskimaa/avatar/saru(.*) http://www.vihrealohikaarme.com/avat/saruman/saru$1
Redirect /keskimaa/avatar http://www.vihrealohikaarme.com/avat/loput

As you can see, it is pretty complex stuff that I just bothered to write sometime, because having one big folder with thousands of avatars wasn't really nice to browse.

[john tindell]

Im not sure about .htaccess with mysql, but could you redirect them all to a PHP file and depending on what gets send check them in the MySQL DB and redirect them from there?

[Merri]

Oh, I guess I forgot to explain this: the redirection doesn't work anymore, because the old folder doesn't exist. Of old I just made it as kind of a hack to use the avatars from other server, especially to save bandwidth costs (the other server was fast and "free"). Now I don't have the other server anymore, but I have copied the avatars to the forum server and the bandwidth isn't an issue. But entries in the DB still point to the old avatar locations and all users haven't switched the avatars.

Thus, due to knowing I have a lack of MySQL knowledge besides rather basic syntax, I'm wondering if there is a better way to update the database other than to writing a somewhat heavy and complex SQL PHP script to do the task. Writing it will take quite some time as the .htaccess file is very long.


Also, is there a way to convert .htaccess regular expression to SQL compatible format?

[dis1411]

i'm trying to understand, so the db entry points to for example /avatar/tumbe_.gif and you want /avat/lahjoitetut/tumbe_.gif ?

[Merri]

The full db entry is like http://koti.mbnet.fi/keskimaa/avatar..._something.jpg - hmm, is it possible to do an SQL call that is able to select a part of the old data and have it in the new data?

[penagate]

I don't quite understand. Do you just want to do a find/replace?

update table_name
set field_name = replace(
  field_name,
  'http://koti.mbnet.fi/keskimaa/avatar/',
  'http://www.vihrealohikaarme.com/avat/lahjoitetut/'
);