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

Recommended Answers

All 13 Replies

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.

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?

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.

Change your double quotes to single quotes.

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....

you'd have to use each query separate.

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!

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

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

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.

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_query("UPDATE `feeds` SET `title` = REPLACE(`title`,'ă','a')");

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.