Cronjob for modify table content (replace)

[RobotFX]

Hi!
I'm looking for a way to search and replace certain strings in a database table I have. Those are the commands I execute now from within phpmyadmin:

UPDATE `feeds` SET `title` = REPLACE(`title`,"ă","a")
UPDATE `feeds` SET `title` = REPLACE(`title`,"î","i")
UPDATE `feeds` SET `title` = REPLACE(`title`,"ÅŸ","s")
UPDATE `feeds` SET `title` = REPLACE(`title`,"Å£","t")
UPDATE `feeds` SET `title` = REPLACE(`title`,"â","a")

UPDATE `feeds` SET `text` = REPLACE(`text`,"ă","a")
UPDATE `feeds` SET `text` = REPLACE(`text`,"î","i")
UPDATE `feeds` SET `text` = REPLACE(`text`,"ÅŸ","s")
UPDATE `feeds` SET `text` = REPLACE(`text`,"Å£","t")
UPDATE `feeds` SET `text` = REPLACE(`text`,"â","a")

The problem is I cannot do this every day and I need a cronjob to do it. I don't know if is possible to make a cron execute a sql command or better make a script and run it through a cron, I have no idea. Any suggestion on how to run this periodically, without my intervention, would be apreciated.
Thank you!

P.S. Even the code above doesn't work if I try all at once, I must run the commands one by one

[pritaeas]

You can put these lines in a text file (commands.sql), but make sure all lines end with semi-colon ( ; ). Then you can use a cron-job in which you could put something like

mysql -u user -p password < commands.sql

See the help for mysql to get more details on the parameters.

[RobotFX]

Thanks for the help, is not working, though. The cron tells me the password is wrong, though is not. Any chance to make it work?

[RobotFX]

Ok, fixed the password error. So, here's the error now:

ERROR 1064 (42000) at line 6: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's");
UPDATE `feeds` SET `title` = REPLACE(`title`,"ÃÆÂ:","t");
UPDATE `feeds` SE' at line 1

And here's what the .sql file contains:

UPDATE `feeds` SET `title` = REPLACE(`title`,"ă","a");
UPDATE `feeds` SET `title` = REPLACE(`title`,"î","i");
UPDATE `feeds` SET `title` = REPLACE(`title`,"ÅŸ","s");
UPDATE `feeds` SET `title` = REPLACE(`title`,"Å£","t");
UPDATE `feeds` SET `title` = REPLACE(`title`,"â","a");
UPDATE `feeds` SET `title` = REPLACE(`title`,"ș","s");
UPDATE `feeds` SET `title` = REPLACE(`title`,"ț","t");
UPDATE `feeds` SET `title` = REPLACE(`title`,"È™","s");
UPDATE `feeds` SET `title` = REPLACE(`title`,"ÃŽ","I");
UPDATE `feeds` SET `title` = REPLACE(`title`,"Åž","S");
UPDATE `feeds` SET `title` = REPLACE(`title`,"„","");
UPDATE `feeds` SET `title` = REPLACE(`title`,"“","");
UPDATE `feeds` SET `title` = REPLACE(`title`,"’","'");
UPDATE `feeds` SET `title` = REPLACE(`title`,"Å¢","T");
UPDATE `feeds` SET `title` = REPLACE(`title`,"�"," ");

UPDATE `feeds` SET `text` = REPLACE(`text`,"ă","a");
UPDATE `feeds` SET `text` = REPLACE(`text`,"î","i");
UPDATE `feeds` SET `text` = REPLACE(`text`,"ÅŸ","s");
UPDATE `feeds` SET `text` = REPLACE(`text`,"Å£","t");
UPDATE `feeds` SET `text` = REPLACE(`text`,"â","a");
UPDATE `feeds` SET `text` = REPLACE(`text`,"ș","s");
UPDATE `feeds` SET `text` = REPLACE(`text`,"ț","t");
UPDATE `feeds` SET `text` = REPLACE(`text`,"È™","s");
UPDATE `feeds` SET `text` = REPLACE(`text`,"ÃŽ","I");
UPDATE `feeds` SET `text` = REPLACE(`text`,"Åž","S");
UPDATE `feeds` SET `text` = REPLACE(`text`,"„","");
UPDATE `feeds` SET `text` = REPLACE(`text`,"“","");
UPDATE `feeds` SET `text` = REPLACE(`text`,"’","'");
UPDATE `feeds` SET `text` = REPLACE(`text`,"Å¢","T");
UPDATE `feeds` SET `text` = REPLACE(`text`,"�"," ");

Now, what doesn't make sense is that these commands are running fine from the phpmyadmin panel, but when I try to run using the cron I got the above error. BTW, for what it matters, the word "text" in the code above is written in small letters, I don't know why when I publish it here it appears in caps.

[pritaeas]

Change your double quotes to single quotes.

[RobotFX]

Doesn't help, either. I've tried to eliminate some rows but there always something wrong. Is there a way to make the cronjob skip to the next command in the .sql file, if something is wrong with one line? Now I think the cron does nothing, but if is something wrong with one of the UPDATE commands, at least skip to the next one....

[pritaeas]

you'd have to use each query separate.

[RobotFX]

I can't do that, I'm affraid, I have too many crons and making another 30 is...well, will be confusing :)
Is there a way to make this work as a php script, called from browser?
Thank you!

[pritaeas]

You can use a php file as your cronjob, in which you execute each sql statement separately, and ignore any errors (or a loop reading line by line from your file).

[RobotFX]

Can you give an example, please? I have no idea how to do this.

[pritaeas]

http://www.htmlcenter.com/blog/running-php-scripts-with-cron/
http://www.tizag.com/mysqlTutorial/mysqlquery.php

[RobotFX]

Anyone else, please? I don't have any ideas :)

I think that the script gets stucked when it encounters something it doesn't need to change - it doesn't skip to the next UPDATE command, but that's just an idea, I don't know how really works.

[pritaeas]

Your PHP file to run should look like this. Repeat the mysql_query, it tries to execute and then continues to the next one without asking.

$link = @mysql_connect('localhost', 'USER', 'PASS');
@mysql_select_db('DB');

@mysql_query("UPDATE `feeds` SET `title` = REPLACE(`title`,'ă','a')");

[RobotFX]

I'm about to lose my patience with this :) It still doesn't work. The script runs without errors, but does nothing (!?) I've tryied to run it as root, as user, making it writable, executable etc.
I think I'll give up this.