Using a PDO to replace characters in a field

Question

I have table of around 9,000 rows in which I need to go about searching one specific column and replacing specific characters with their appropriate replacement. For this I have an associative array of values. Below is my code which will give a better idea of what I'm trying to achieve. I am trying to do this as efficiently as possible and with as little queries as I can. Any help would be appreciated.

try {
  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
}
catch(PDOException $e) {

    die($e->getMessage());

}

$diacritics = array(
    'Š'=>'S', 'š'=>'s', 'Ð'=>'Dj', 'Ž'=>'Z', 'ž'=>'z', 'C'=>'C', 'c'=>'c', 'C'=>'C', 'c'=>'c',
    'À'=>'A', 'Á'=>'A', 'Â'=>'A', 'Ã'=>'A', 'Ä'=>'A', 'Å'=>'A', 'Æ'=>'A', 'Ç'=>'C', 'È'=>'E', 'É'=>'E',
    'Ê'=>'E', 'Ë'=>'E', 'Ì'=>'I', 'Í'=>'I', 'Î'=>'I', 'Ï'=>'I', 'Ñ'=>'N', 'Ò'=>'O', 'Ó'=>'O', 'Ô'=>'O',
    'Õ'=>'O', 'Ö'=>'O', 'Ø'=>'O', 'Ù'=>'U', 'Ú'=>'U', 'Û'=>'U', 'Ü'=>'U', 'Ý'=>'Y', 'Þ'=>'B', 'ß'=>'Ss',
    'à'=>'a', 'á'=>'a', 'â'=>'a', 'ã'=>'a', 'ä'=>'a', 'å'=>'a', 'æ'=>'a', 'ç'=>'c', 'è'=>'e', 'é'=>'e',
    'ê'=>'e', 'ë'=>'e', 'ì'=>'i', 'í'=>'i', 'î'=>'i', 'ï'=>'i', 'ð'=>'o', 'ñ'=>'n', 'ò'=>'o', 'ó'=>'o',
    'ô'=>'o', 'õ'=>'o', 'ö'=>'o', 'ø'=>'o', 'ù'=>'u', 'ú'=>'u', 'û'=>'u', 'ý'=>'y', 'ý'=>'y', 'þ'=>'b',
    'ÿ'=>'y', 'R'=>'R', 'r'=>'r'
);


$dia_remove_STH = $DBH->prepare("UPDATE mytable SET title=?"); 
//THE FOLLOWING LINE IS OBVIOUSLY NOT VALID BUT IS THE FUNCTIONALITY I'M LOOKING TO ACHIEVE
$dia_remove_STH->execute(array(strtr(mytable.title, $diacritics));

See [this post](http://stackoverflow.com/questions/4813620/how-to-remove-accents-in-mysql) and [this post](http://stackoverflow.com/questions/2753422/mysql-replace-accented-characters) relating to accented characters. — Michael Benjamin, Mar 15 '13 at 22:38

score 1 · Answer 1 · answered Mar 15 '13 at 22:40

1

I don't think there is a simple SQL solution for this, so you should either write a stored procedure or simply read the records into PHP, do the replacement and then update the record. You could do it within a transaction which could speed it up.

answered Mar 15 '13 at 22:40

Peter Wooster

6,009
2
27
39

Michael Benjamin · Accepted Answer · 2013-03-15T22:50:43.033

EDIT: Saw you were using codeigniter, replace with codeigniter database functions.

$query="UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,?,?)";
foreach($diacritics as $key=>$value){
    $params= array($key, $value);
    $this->db->query($query, $params);
}

Alternately:

$query="REPLACE(COLUMN,?,?)";
$params=array();
foreach($diacritics as $key=>$value){
    $params[]= $key;
    $params[]= $value;
    $query= 'REPLACE('.$query.',?,?)';
}
$query= 'UPDATE TABLE_NAME SET COLUMN = ' . $query;
$this->db->query($query, $params);

Using a PDO to replace characters in a field

2 Answers2