filter data of a query in minuscule and without spaces

Asked

Viewed 124 times

0

How to query the database that ignores accents and other special characters?

I have the products table that has the name field with the following values:

"shoes", "Tennis", "Karaoke and Video", etc...

In the BD the names of the products are accentuated and separated by space.

My URL: http://meusite.com.br/produtos/calcados

The products page receives the value "calcado" and I check if there are products with this name.

The problem is that, by the fact that in the bank the name of the product is engraved with accents and spaces, when sending "pressed" I do not find anything, because I have only recorded "footwear".

I’d also like him to consider compound words, like:

Karaoke and Video, Karaoke, Video...

SQL query

 $con = $pdo->query("SELECT * FROM ws_cat_sub WHERE sub_categoria = 'calcados' ");
  $return_id = $con->fetch(PDO::FETCH_OBJ);
php html mysql database

  • What is the database manager?

  • i use Mysql.

  • Evaluate the use of COLLATE in the WHERE clause to resolve the accentuation issue // For searching in compound words, evaluate the use of the LIKE operator. In this case, degradation in performance is likely to occur.

  • I think the best way would be to take all categories in array and pass str_replace then do the query ai yes clear spaces

1 answer

0


I don’t know if I understand this correctly, but I think you want to do a database search, but do you want the person to be able to search without accents? If this is the case the problem is that you should not put a sign of = for the results, and yes the parameter LIKE, then your code will look like this:

 $con = $pdo->query("SELECT * FROM ws_cat_sub WHERE sub_categoria LIKE '%calcado%' ");
 $return_id = $con->fetch(PDO::FETCH_OBJ);

Explanations:
% = The percentage symbol indicates that it can have some character before or not, this means that if the person seeks 'alcado' without the C will return the footwear, as she searched for a word that looks like one she has saved in the database, this applies to searches as well as calcad, alcado but not to acclaimed

LIKE = The like is a substitute for the =. If you don’t put the LIKE will give error! Below are the references!

NOTE: If this symbol appears: utf8_encode or utf8_decode.

REFERENCES:

https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html http://php.net/manual/en/function.utf8-encode.php

GOOD LUCK, I HOPE I HELPED!

Browser other questions tagged php html mysql database

You are not signed in. Login or sign up in order to post.