Need Help Stringing Some Php Commands Together

[FUNKAM35]

Hi, please could someone help me

 

I need to string several about 30 in total commands together in total

 

An example of the commands are:

UPDATE `property` SET `description` = replace(description, "é", "e")

UPDATE `property` SET `description` = replace(description, "á", "a")
UPDATE `property` SET `description` = replace(description, "St.", "St ")

UPDATE `property` SET `description` = replace(description, "Ü", "u")

UPDATE `property` SET `description` = replace(description, "È", "e")
UPDATE `property` SET `description` = replace(description, "ü", "u")

UPDATE `property` SET `description` = replace(description, "ü", "u")
UPDATE `property` SET `description` = replace(description, "ß", "ss")
UPDATE `property` SET `description` = replace(description, "Ö", "o")

 

Now I have to run each one separately and manually

 

I would like to be able to join them all together and run them all in one file and then I could set up a cron job for one file

 

Last time I tried I unwittingly deleted all the descriptions, nightmare!

 

Please can someone help me?

 

Thanks

[ManiacDan]

You may be surprised to learn that none of what you posted was PHP.

 

This PHP script will run the queries as long as you establish the proper database connection:

 

mysql_connect(/*proper values here*/);
$statements = array(
'UPDATE `property` SET `description` = replace(description, "é", "e")',
'UPDATE `property` SET `description` = replace(description, "á", "a")',
'UPDATE `property` SET `description` = replace(description, "St.", "St ")',
'UPDATE `property` SET `description` = replace(description, "Ü", "u")',
'UPDATE `property` SET `description` = replace(description, "È", "e")',
'UPDATE `property` SET `description` = replace(description, "ü", "u")',
'UPDATE `property` SET `description` = replace(description, "ü", "u")',
'UPDATE `property` SET `description` = replace(description, "ß", "ss")',
'UPDATE `property` SET `description` = replace(description, "Ö", "o")');
foreach ( $statements as $statement ) {
 mysql_query($statement);
}

You can also do this through bash, or pipe a file full of statements to mysql right from the cron interface.

[FUNKAM35]

thanks for the reply, I have been running these staetments individually through mysql datatbase but now need to join them

[The Little Guy]

Just put it in a sql file and run from the commandline:

 

mysql -h localhost -u myUserName -pMyPassword myDatabase < mySQLFile.sql

Edited October 2, 2012 by The Little Guy

[mikosiko]

thanks for the reply, I have been running these staetments individually through mysql datatbase but now need to join them

 

You don't need to run separated updates (and in a loop)... only ONE update is enough if you use a CASE... example

 

 

'UPDATE `property`

SET `description` = ( CASE

WHEN `description` = "é" THEN "e"

WHEN `description` = "á" THEN "a"

....

....

ELSE `description`

END

)

[FUNKAM35]

ok thanks will try this

just terrified as last time it updated the description to nothing and all descriptions on 30,000 items were gone

[mikosiko]

ok thanks will try this

just terrified as last time it updated the description to nothing and all descriptions on 30,000 items were gone

 

just don't forget the ELSE part

[ManiacDan]

That case won't work. Running them all one at a time is going to be slightly slower but not so much that you'd care. My script will work, though The Little Guy provided my "other" solution, right in the cron line.

[mikosiko]

That case won't work.

 

why not?... did you tested?.. I did... it does work... maybe you are seeing something that I'm not... enlight us

[ManiacDan]

The queries are designed to replace "special" foreign characters with their latin equivalent in a field called "description." It's assumed these are actual descriptions, longer than one character. The case given will only replace descriptions that are one character long and ignore the rest. That's why he was using the REPLACE function with no WHERE.

[mikosiko]

absolutely valid point Dan, I didn't see the query from that angle... if that was the OP objectives as it seems, you are absolutely correct, my code suggestion doesn't work in that scenario; thank you for correct my Uh-oh moment.

Edited October 2, 2012 by mikosiko

[mikosiko]

a modified version that should cover the right scenario pointed out by Dan. granted it is a micro-optimization

 

UPDATE `property`
SET `description` = ( CASE
WHEN instr(`description`, "é") > 0 THEN replace(`description`, "é", "e")
WHEN instr(`description`, "á") > 0 THEN replace(`description`, "á", "a")
....
....
ELSE `description`
END
)

Edited October 2, 2012 by mikosiko

[ManiacDan]

Will MySQL continue to evaluate CASE statements? What if the description contains 8 different special characters? Will all 8 of the replacements evaluate?

 

 

[mikosiko]

you are right again... I should have stayed at home today

[jazzman1]

Well, what about with differen charset and collation like UTF ?

[fenway]

You know, MySQL has collation conversions specifically to address this issue.

[FUNKAM35]

Can you please tell me more about these mysql collation conversions? thanks

[FUNKAM35]

1. confused.com so which of the examples should I use?
   

[ManiacDan]

The very first example I gave you will work.

 

 

[FUNKAM35]

hi, I have used

<?php
include_once("variables.php");
$link_id = mysql_connect("$db_host","$db_user","$db_password");
if (mysql_select_db("$db_database", $link_id));
$statements = array
('UPDATE `property` SET `town` = replace(town, "i­", "i")',
'UPDATE `property` SET `town` = replace(town, "í", "i")',
'UPDATE `property` SET `town` = replace(town, "ú", "u")');
foreach ( $statements as $statement ) {  mysql_query($statement);}
?>

 

And it doesnt work any ideas whats wrong

thanks

[ManiacDan]

Turn on error reporting, check the errors, echo mysql_error()

[FUNKAM35]

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><META content="IE=5.0000" http-equiv="X-UA-Compatible">
<META content="text/html; charset=windows-1252" http-equiv=Content-Type></HEAD>
<BODY></BODY></HTML>

 

Thanks, I added error reporting but no errors showed

 

For some strange reason today it dis replace & with and

I figured it was something to do with the encoding that it wouldnt change ß to ss

 

I added the above headers and it worked, strange or what!