1
Hello! I have a search for automotive products where the user can search for the company code, original code, product line, automaker or vehicle. This system is working if in the search field I type, for example, "E-1001", or "1001", or "E". But if I type "E1001" without the hyphen, it returns no result.
The same problem happens in the original code field. If I search for example "12.345.67", or "12", or "1" it works. But nothing returns if I search "1234567" without the dots in the middle.
Would anyone have any idea what I can change to work without these hyphens and stitches?
Thank you!
if(isset($_POST['acao']) && $_POST['acao'] == 'enviar'){
$buscacodempresa = preg_replace('#[^a-z 0-9?()!-]#i', '', $_POST['searchcodempresa']);
$buscacodoriginal = preg_replace('#[^a-z 0-9?()!-.]#i', '', $_POST['searchcodoriginal']);
$idlinha = $_POST['parent_linha'];
$idmont = $_POST['parent_mont'];
$idveics = $_POST['parent_veics'];
if($_POST['parent_veics'] >= 1) {
$getresultsquery = mysql_query('SELECT p.*, a.idProduto, a.idVeiculos, a.ano, v.nomeVeiculos, v.idVeiculos, GROUP_CONCAT(a.ano SEPARATOR "<br>"), GROUP_CONCAT(a.idVeiculos SEPARATOR "<br>"), GROUP_CONCAT(v.nomeVeiculos SEPARATOR "<br>")
FROM produtos p
INNER JOIN aplicacao a ON p.idProduto = a.idProduto
INNER JOIN veiculos v ON a.idVeiculos = v.idVeiculos
WHERE p.codEmpresa LIKE "%'.$buscacodempresa.'%"
OR p.codOriginal LIKE "%'.$buscacodoriginal.'%"
OR p.idLinha = "'.$idlinha.'"
OR v.idVeiculos = "'.$idveics.'"
GROUP BY p.codEmpresa') or die(mysql_error());
} else {
$getresultsquery = mysql_query('SELECT p.*, a.idProduto, a.idVeiculos, a.ano, v.nomeVeiculos, v.idVeiculos, GROUP_CONCAT(a.ano SEPARATOR "<br>"), GROUP_CONCAT(a.idVeiculos SEPARATOR "<br>"), GROUP_CONCAT(v.nomeVeiculos SEPARATOR "<br>")
FROM produtos p
INNER JOIN aplicacao a ON p.idProduto = a.idProduto
INNER JOIN veiculos v ON a.idVeiculos = v.idVeiculos
WHERE p.codEmpresa LIKE "%'.$buscacodempresa.'%"
OR p.codOriginal LIKE "%'.$buscacodoriginal.'%"
OR p.idLinha = "'.$idlinha.'"
OR p.idMontadoras = "'.$idmont.'"
GROUP BY p.codEmpresa') or die(mysql_error());
}
The reason for this which is in your database your data must also be saved formatted. So for this correct do not return as it would be a comparison
12.345.67
(bank)==
1234567
(string). To resolve this you must commit the database data to the same type of text formatting you are looking for.– Guilherme Lautert
Guilherme, in the database the data are as 12.345.67 and E-1001. But the code should not display the result even missing one or other character? Especially because I’m using the LIKE operator.
– RaphaMMS
That’s not quite how like works. Let’s assume, your query generates, for example, something like '%E1001%'. What you are saying to the bank is: "look for any record that contains 'E1001' as it is written, but disregard what comes before or after that value (%)". It would bring for example, 'abcE1001', 'E1001abc' and 'abcE1001abc'. But it would not bring 'Eabc1001'.
– Daniel Ferreira
Got it Daniel, thanks for the explanation. So in case, would there be an operator that does this search in the "Eabc1001" style? Or a way to circumvent would be to edit the DB by inserting a column predicting a more generic search to return the result? For example, in the table "products", create a column "Generico" and in the line E-1001, put the value E1001 and add in SQL something like "OR p.Generico LIKE "%'. $buscacodempresa.'%""
– RaphaMMS