How to use str_replace in single quotes without removing the ones that are required?

Question

I’m going through a database of a friend who had some data entered wrong or a little confused or I’m the one who’s making it difficult.

Ex: Mariana "Gurizinha’s"

This example was saved in the bank as follows: \'Gurizinha\'s\'

To display on his website I tried to create a function which replaces the initial character with a " and the ending also with ".

Right would be to pick up a text that was saved that way and that has 100 words or more and correct displaying: "Gurizinha's" instead of \'Gurizinha\'s\'. Then my head tied a knot str_replace it will take all the text and whatever has ' will change to " so if it is words that need Are’s it will replace by ".

function rt_especial($valor) {
  $i = 0;
  $palavras = explode(' ', $valor);

  foreach($palavras as $Arr) {
    $i++;
    $novotexto = stripcslashes($Arr);
    $i2 = 0;
    $palavras2 = explode("'", $novotexto);

    foreach($palavras2 as $Arr2) {
      $i2++;
    }

    for($i3 = 0; $i3 <= $i2; $i3++) {
      $str = '"';
      $str .= $palavras2[$i3];
    }
  }

  echo substr($string, -1);
  $novotexto = join(' ', $novotexto);

  return $novotexto;
}

Code entering data into Mysql database:

function db_executa($tabela, $dados, $acao = 'insert', $parametros = '') {
    reset($dados);
    if (strtolower($acao) == 'insert') {
      $consulta = 'insert into ' . $tabela . ' (';
      while (list($coluna, ) = each($dados)) $consulta .= $coluna . ', ';
      $consulta = substr($consulta, 0, -2) . ') values (';

  reset($dados);
  while (list(, $valor) = each($dados)) {
    switch ((string)$valor) {
      case 'now()':
        $consulta .= 'now(), ';
        break;
      case 'null':
        $consulta .= 'null, ';
        break;
      default:
        $consulta .= '\'' . db_entrada($valor) . '\', ';
        break;
    }
  }
  $consulta = substr($consulta, 0, -2) . ')';

} elseif (strtolower($acao) == 'update') {
  $consulta = 'update ' . $tabela . ' set ';

  reset($dados);
  while (list($coluna, $valor) = each($dados)) {
    switch ((string)$valor) {
      case 'now()':
        $consulta .= $coluna . ' = now(), ';
        break;
      case 'null':
        $consulta .= $coluna .= ' = null, ';
        break;
      default:
            $consulta .= $coluna . ' = \'' . db_entrada($valor) . '\', ';
            break;
        }
      }
      $consulta = substr($consulta, 0, -2) . ' where ' . $parametros;
    }
    return db_consulta($consulta);
  }

Solving part of the problem: It’s not a pretty way and much less certain, but it’s the one I got to solve.

function rt_especial($valor){

        $string = $valor;
        $separa = explode(" ", $string); // quebra a string nos espaços
        $count = count($separa); // quantidade de separações

        $arrayok = array();



        for($i=0; $i<= $count; $i++)
        {
            // Pego toda palavra que começa com \' e substituo por "
            $string2 = ereg_replace("^([/\'])", '"',$separa[$i]);
            $string3 = str_replace("\',", '",', $string2);
            $string4 = str_replace("\',", '",', $string3);

            $string5 = ereg_replace('^([/""])', '"',$string4);
            $string6 = ereg_replace('([/""])$', '"',$string5);

            //Pego toda palavra que termina com \' e substituo por "
            $string = ereg_replace("([/\'])$", '"',$string6);
            $string7 = str_replace('"\'', '"', $string);
            $string8 = str_replace("\'\"", '"', $string7);
            $string9 = str_replace('\"', '"', $string8);


            $arrayok[$i] = $string9;

        }

        $ccp = implode(' ', $arrayok);

        return $ccp;
    }

Answer 1

An alternative way is to select the records you want to fix.

SELECT 

campo,
 ROUND (   
        (
            LENGTH(campo)
            - LENGTH( REPLACE ( campo, '\'', '') ) 
        ) / LENGTH('\'')        
    ) AS count

FROM

`regexp`

HAVING (
ROUND (   
        (
            LENGTH(campo)
            - LENGTH( REPLACE ( campo, '\'', '') ) 
        ) / LENGTH('\'')        
    )
) % 2 = 1

In this example, the query will return all that have single quote (') and the sum of its occurrences by Row, and the amount of occurrences must be odd number.

What is the logic of doing this?

If there are odd numbers, there are probably occurrences like 'Gurizinha’s\'.

With this, it would eliminate the need to look at a good part of the results. For all other results with resulting sum in pairs, just apply a general replace, replacing the escaped single quote with a double quote without the escape character.

It still won’t solve 100%, but at least you’ll have a list of all the Rows Ids that need to be fixed.

There are other ways with REGEXP, however, there is no way to use REGEXP with REPLACE. You would have to use a UDF. But I think you can solve it in a simpler way without needing Udfs.

Answer 2

In case your question is not PHP oriented, but Mysql.

But come on:

Run the query by replacing the name of your table and your column

UPDATE sua_tabela
SET sua_coluna = REPLACE(sua_coluna, "\\'", '"')
WHERE sua_coluna LIKE "%\'%"

Answer 3

Look up regular expressions with PHP, I’m not experienced with them yet, but I believe the example below solves your problem:

<?php
    function ModificarTXT($str)
    {
        // Pego toda palavra que começa com \' e substituo por "
        $string = ereg_replace("^([/\'])", '"',$str);

        //Pego toda palavra que termina com \' e substituo por "
        $string = ereg_replace("([/\'])$", '"',$string);
        return $string;
    }

    echo ModificarTXT("\'Gurizinha\'s\'"); // Teste da funcao
?>

Answer 4

The problem is in your connection with the bank.

Use PDO that your information will be stored exactly the way it went, without having to remove or replace special characters, quotes, etc. And when you recover, it will be the same way you entered it. PDO also accepts parameterized statements (Preparement Statement) that will protect you from SQL injections.

To connect:

// Dados da conexão
$host = 'localhost';
$port = '3306';
$user = 'root';
$pass = '123456';
$dbname = 'meubanco'

// Configuração para garantir a codificação correta caso esteja usando utf8
$config = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8");

// Realiza a conexão
$pdo = new PDO("mysql:host=$host;port=$port;dbname=$dbname", $user, $pass, $config);

Example of parameterized INSERT:

// Recebe variáveis
$codigo = $_POST['codigo'];
$nome = $_POST['nome'];

// Define a query usando '?' no lugar das variáveis
$query = "INSERT INTO table1 (codigo, nome) VALUES (?,?)";

// Prepara
$stmt = $pdo->prepare($query);

// Seta os parametros definidos como '?' na query de acordo com a ordem
$stmt->bindValue(1, $codigo, PDO::PARAM_INT);
$stmt->bindValue(2, $nome, PDO::PARAM_STR);

// Executa query
$stmt->execute();

// Pega o total de linhas afetadas
$total = $stmt->rowCount();

// Verifica se inseriu
if( $total > 0 )
    echo "Inseriu!";
else
    echo "Não Inseriu!";

Example of parameterized SELECT:

// Recebe variáveis
$codigo_minimo = $_POST['codigo_minimo'];

// Define a query usando '?' no lugar das variáveis
$query = "SELECT * FROM table1 WHERE codigo > ?)";

// Prepara
$stmt = $pdo->prepare($query);

// Seta os parametros definidos como '?' na query de acordo com a ordem
$stmt->bindValue(1, $codigo_minimo, PDO::PARAM_INT);

// Executa query
$stmt->execute();

// Muda de acesso as colunas para o modo pelo nome
$stmt->setFetchMode(PDO::FETCH_ASSOC);

// Usando fetchAll você pega todos resultados em um array
$result = $stmt->fetchAll();

var_dump($result);

var_dump($result) would print something like this:

array (size=3)
   0 => array (size=2)
       'codigo' => 1
       'nome' => "bla bla bla"
   1 => array (size=2)
       'codigo' => 2
       'nome' => "ble ble ble"
   2 => array (size=2)
       'codigo' => 3
       'nome' => "blu blu blu"

To disconnect:

// basta setar como NULL
unset($pdo); 

For more information on how to use PDO, access this link.

Answer 5

Use a regular expression to replace ' by " at the beginning and end of whole words:

preg_replace_callback('/(?<=\s|\t|\r|\n| )(\'.+\')(?=\s|\t|\r|\n|\.|,| )/muU', function($match) {
    return '"' . substr($match[0], 1, -1) . '"';
}, $x);

You can use this function (udf) to replace all records that are already in the database:

REGEXP_REPLACE(col, '/(?<=\s|\t|\r|\n| )\'(?=.*\'[\s\t\r\n\., ])/', '"');
REGEXP_REPLACE(col, '/(?<=[\s|\t|\r|\n| ]\'.*)\'(?=[\s\t\r\n\., ])/', '"');

Or create a script to select and update all columns:

$rows = $dbh->query("SELECT id, col FROM tabela")->fetchAll(\PDO::FETCH_ASSOC);
$sth = $dbh->prepare("UPDATE tabela SET col = ? WHERE id = ?");
foreach($rows as $row) {
    $str = preg_replace_callback('/(?<=\s|\t|\r|\n| )(\'.+\')(?=\s|\t|\r|\n|\.|,| )/muU', function($match) {
       return '"' . substr($match[0], 1, -1) . '"';
    }, $row['col']);
    if($str == $row['col']) continue;
    $sth->execute(array( $str, $row['id'] ));
    $sth->closeCursor();
}