CODEWITHSUNDEEP
phphtmlmysqlwysiwygdolibarr
cilies38
cilies38

Reputation: 69

Search words in html text in mysql field description without plain text

I'm modifying code in dolibarr and i went through a problem with the search in desciption of products with WYSIG editor (recording html text). The search of description containing several words doesn't work after a
(maybe all tags) or at the very start of text, the first word does not return the description. Plus, "galvanisé" works with the "é" but not "d'options" with quote. see examples below :

I tried to use htmlentities and to include end or start of tags (see below).

the description :

Filtre standard avec tout un tas d'options 900x400x116
Nom 22
EN779-2002
acier galvanisé

for( $i=0 ; $i < sizeof($tabMots) ; $i++) {
    if($i == 0) {
        if(is_numeric($tabMots[$i])) 
            $sql.= ' WHERE (p.description LIKE \'% '.$tabMots[0].' %\' OR p.description LIKE \'%>'.$tabMots[0].' %\' OR p.description LIKE \'%>'.$tabMots[0].'<%\' OR p.description LIKE \'% '.$tabMots[0].'<%\' )';
        else
            $sql.= ' WHERE (p.description LIKE \'% '.$tabMots[0].'%\' OR p.description LIKE \'% '.htmlentities($tabMots[0]).'%\' OR p.description LIKE \'%>'.$tabMots[0].'%\' OR p.description LIKE \'%>'.htmlentities($tabMots[0]).'%\' )';
    } else {
        if(is_numeric($tabMots[$i])) {
            $sql.= ' AND (p.description LIKE \'% '.$tabMots[$i].' %\'  OR p.description LIKE \'%>'.$tabMots[$i].'%\' OR p.description LIKE \'%>'.$tabMots[$i].' %\' OR p.description LIKE \'%>'.$tabMots[$i].'<%\' OR p.description LIKE \'% '.$tabMots[$i].'<%\' )';
        } else {
            $sql.= ' AND (p.description LIKE \'% '.$tabMots[$i].'%\'  OR p.description LIKE \'% '.htmlentities($tabMots[$i]).'%\' OR p.description LIKE \'%>'.$tabMots[$i].'%\' OR p.description LIKE \'%>'.htmlentities($tabMots[$i]).'%\' )';

        }
    }
}

I would like to avoid to add a plain text field in order to not modify the dolibarr database. Thanks.

EDIT : The proposed Answer is no good because the problem is the text format HTML of the text description not criterias of search.

Upvotes: -1

Views: 173

Answers (2)

cilies38
cilies38

Reputation: 69

For those who put -1 without thinking i want to say that prepared queries didn't solved my problem because it's formatting the word searched. Here the problem was the format of the description text and i made researches and saw that after inserting
htmlentities() keeps \n and other chars. However, i Don't know the char kept at the start of the text. Thanks for the help i've implemented nevertheless the prepared queries. So, you have to add this search for
inserted when you want a space between % and word '%\n'.$word.'%'. Here you have the root of the word with
before.

Upvotes: 0

Pavel Janicek
Pavel Janicek

Reputation: 14738

I think that your error will be in the fact that the ' sign in word d'options escapes the string in SQL engine. 

$tabMots[0] = "d'options";
$sql.= ' WHERE (p.description LIKE \'% '.$tabMots[0].'%\'';
echo $sql;

Try the above online

The above will print out this:

WHERE (p.description LIKE '% d'options%'

Even from StackOverflow highlighting, you can see, what SQL engine gets from that.

I highly recommend to switch to prepared statements. Read about it here:

How can I prevent SQL injection in PHP?

Edit

How to switch to prepared statements? Example:

$param = $tabMots[$i];
$stmt = $db->prepare("SELECT id,Username FROM users WHERE Username LIKE CONCAT('%',?,'%') ");
$stmt->bind_param("s", $param);
$stmt->execute();

Copypasted from this answer: php mysqli prepared statement LIKE

Upvotes: 0

Related Questions