Insert line break in field text into database

Question

I have a table with a field type text, the field stores a string imported via archive .csv, the same was inserted without line break and I’m trying to insert it, I was able to identify that the line break should happen after the " - ", hyphen space hyphen, I tried this code snippet:

<?php

include('Connections/conexao.php');

mysql_select_db($database_conexao, $conexao);
$query_rsPesquisa = "SELECT * FROM  `produtos` WHERE status = 1";
$rsPesquisa = mysql_query($query_rsPesquisa, $conexao) or die(mysql_error());
$row_rsPesquisa = mysql_fetch_assoc($rsPesquisa);
$totalRows_rsPesquisa = mysql_num_rows($rsPesquisa);


do {

    $id_produto = 10;

    $string = $row_rsPesquisa['detalhes'];  

        if( strstr($string," - ")){         
            $novaString = wordwrap($string, 20, "<br />\n");            
            $UpDetalhe ="UPDATE produtos SET detalhes = $novaString WHERE id_produto = $id_produto;";
            $sucesso = mysql_query($UpDetalhe) or die(mysql_error("Erro ao alterar registro")); 

            if ($sucesso > 0) {
                echo "Próximo registro";
            }

        }

} while ($row_rsPesquisa = mysql_fetch_assoc($rsPesquisa));

?>

The plain text is like this:

"Measures of the products of photo(s) (s) (cm): - 3 Places + Chaise: - Width: 281 - Depth: 161 - Height: 94 - 3 Places: - Width: 225 - Depth: 94 - Height: 94 - 2 Places: - Width: 170 - Depth: 94 - Height: 94 - Modulated 2 Places + Corner + 3 Places: - Width (2 Places + Corner): 246 - Width (3 Places + Corner): 301 - Depth: 94 - Height: 94 - Size options: - 1 Place - 2 Places - 3 Places - Modulated (seat, chaise and corner). - Model also has puff option. - Coating: - Various options in fabric and synthetic leather."

After my attempt it was like this:

Medidas dos<br />
produtos da(s)<br />
foto(s) (cm):2<br />
Lugares: -  <br />
Largura: 212 -  <br />
Profundidade: 90 -  <br />
Altura: 86 - 3<br />
Lugares + Chaise: - <br />
 Largura: 212 -  <br />
Profundidade: 155 - <br />
 Altura: 86 -<br />
Opções de tamanho:<br />
-   2 Lugares -   3<br />
Lugares -   Modulado<br />
(assento, chaise e<br />
canto). -<br />
Revestimento: -  <br />
Várias opções em<br />
couro natural e<br />

I’d like to keep it that way:

Medidas dos produtos da(s) da(s) foto(s) (cm):
2 Lugares: 
- Largura: 212 
- Profundidade: 90 
- Altura: 86 
3 Lugares + Chaise: 
- Largura: 212 
- Profundidade: 155 
- Altura: 86 
- Opções de tamanho:
- 2 Lugares 
- 3 Lugares 
- Modulado (assento, chaise ecanto). 
- Revestimento: 
- Várias opções em couro natural e couro sintético.

In displaying the variable on my page I am using it without success:

echo nl2br($row_rsProdutos['detalhes']);

Answer 1

If you have a pattern you can make it easy. If you don’t have a clear pattern it is very complicated or impossible. If it wasn’t a posting error of yours (it seems to be, after all it is impossible for the data source to give the result you said you want, the data doesn’t even match, they are different things) can not do exactly as you posted, has things out of pattern or the pattern is very complicated and the algorithm would have to be equally complicated.

If I understand the pattern I can do this:

<?php
include('Connections/conexao.php');

mysql_select_db($database_conexao, $conexao);
$query_rsPesquisa = "SELECT detalhes FROM  `produtos` WHERE status = 1";
$rsPesquisa = mysql_query($query_rsPesquisa, $conexao) or die(mysql_error());
$row_rsPesquisa = mysql_fetch_assoc($rsPesquisa);
$totalRows_rsPesquisa = mysql_num_rows($rsPesquisa);

do {
    $id_produto = 10;
    $string = $row_rsPesquisa['detalhes'];  

        if( strstr($string," - ")){         
            do {
                $posicao = strpos($texto, "-", 1);
                $linhaQuebrada .= ($posicao ? substr($texto, 0, $posicao) : $texto) . "\r\n";
                $texto = substr($texto, $posicao);
            } while ($posicao);

            $UpDetalhe ="UPDATE produtos SET detalhes = $linhaQuebrada WHERE id_produto = $id_produto;";
            $sucesso = mysql_query($UpDetalhe) or die(mysql_error("Erro ao alterar registro")); 

            if ($sucesso > 0) {
                echo "Próximo registro";
            }
        }
} while ($row_rsPesquisa = mysql_fetch_assoc($rsPesquisa));

Behold working in the ideone. And in the repl it.. Also put on the Github for future reference.

Note that I only took the field detalhes, If you’re just gonna manipulate him you don’t have to use * in the select, this creates a huge overhead.

If the pattern is not quite this one, explain it better. I saw that there is a difference in some lines that can be worked out differently. That is, you can sort out how to treat products since they have extra spacing. But you need to make sure that there are these standards. Your posting of how it should look doesn’t show a clear pattern.

And you need to decide whether to use <br> or \r\n. The code indicates that it is <br> and I did it originally. Your edit says it will use the normal text line break and then it will convert to HTML line break when it is presented. No consistency makes it hard to understand the problem.

Behold working in the ideone. And in the repl it.. Also put on the Github for future reference. I could have used two suits but it would be less readable.

Remembering that if any data is badly formatted, it will not work.

Answer 2

nl2br: Inserts HTML line breaks before all newlines into a string. Syntax: nl2br($string);

Functioning of the thing: When giving "enter" to break line in the textarea field, these "enters" are preserved and inserted, along with the data, in the database.

Example:

print "<p>".nl2br($produto['detalhes_do_produto'])."</p>";

RESULT

Estou testando esta área com quebra de linha
acabei de dar um enter
mais um
e mais um

source: http://www.linhadecomando.com/php/php-salvando-dados-com-quebras-de-linha-no-banco

Answer 3

The best way to do this is through a regular expression. Using the function preg_split() you can separate the string each -. It works that way:

$string = $row_rsPesquisa['detalhes'];  
$id_produto = 10;

    if( strstr($string," - ")){ 
        $novaString = preg_split("/( - )/",$string); //vai retornar um array            
        foreach ($novaString as $pedaco){ //loop em todos os pedaços da string
            $UpDetalhe ="UPDATE produtos SET detalhes = $pedaco WHERE id_produto = $id_produto;";
            $sucesso = mysql_query($UpDetalhe) or die(mysql_error("Erro ao alterar registro")); 
        }
     }