Search PHP + SQL can not find results without typing the hyphen "-"

1

Hello! I have a search for auto products where the user can search for the company code, original code, product line, assembler 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 results.

The same problem happens in the original code field. If I search for example "12.345.67", or "12", or "1" it works. But it does not return anything if I fetch "1234567" without the dots in between.

Would anyone have any idea what I can change to work without these hyphen and periods?

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());
        }
    
asked by anonymous 22.08.2016 / 20:11

1 answer

0

As Guillermo Lautert said, you should submit the query to the same formatting. A more practical (perhaps not the most performatic) way that would suit you would be to clear the field to be compared in both PHP and sql server.

On the SQL server you could put:

 OR REPLACE(REPLACE(p.codOriginal, '.', ''), '-', '') LIKE "%'.$buscacodoriginal.'%"
    
22.08.2016 / 21:17