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

Asked

Viewed 184 times

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());
        }
mysql sql quest

  • 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, 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.

  • 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'.

  • 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.'%""

1 answer

0


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

In SQL server you could put: 

 OR REPLACE(REPLACE(p.codOriginal, '.', ''), '-', '') LIKE "%'.$buscacodoriginal.'%"

  • Daniel, it’s the other way around. The data in db is HYPHENATED and the point, so replace wouldn’t work. I would have to change db by taking hyphen and dots, or create a column with the data without hyphen and dot and add this search option also in SQL.

  • Replace is just in the SQL field. What I do is remove the possible dots and hyphens before comparing. But of course, you can change the database by removing the columns or adding a sanitized column. Whichever is more convenient.

Browser other questions tagged mysql sql quest

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