Doubt query sql from php with special characters

Navigation
3

I hope you can help me with this doubt,

Given the following php code that connects to a sql server database: 

$sql = "select * from tabla1 where campo1 like '%ñ%'" ;
$datos = $this->objeto->listar() ;
$numRegistros = sqlsrv_row_num($datos) ;
print_r($numRegistros) ;

The result on the screen is 0. If I remove the where the result is 219, that is, all the records in the table.  I know that in the table there are at least 20 results with ñ. That is, it does not recognize special characters when doing the where. The same thing happens with the accents.

The query is correct, copied and pasted in the management studio returns the 20 results. The functions utf8_encode and utf8_decode do not work for me, or at least I do not know how to do it.

Can you help me with the problem?

    
asked by Sergio Teijido 14.12.2018 в 18:56
source

4 answers

1

In my case I apply two techniques to perform searches with accented characters.

On the PHP side     utf8_decode ('áéíóúñ')

link

And on the side of SQLServer a collation that allows to make the search insensitive to uppercase and lowercase, accents and width where Latin1_General_CI_AI complies with these rules.

Ex: 

nombre
-------
Ramón
Ramon

SELECT nombre FROM Tabla WHERE nombre LIKE '%ó%' COLLATE Latin1_General_CI_AI

In this case the result will be that both records meet the condition by specifying that the comparison is insensitive to accented characters.

To know the specification of each comparison COLALTE you can consult them in your same engine: 

SELECT * FROM  sys.fn_HelpCollations() 
WHERE name like ('%Latin1_General_CI_AI%')
ORDER BY name;

-----------------------
Latin1_General_CI_AI: 
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive

Brief explanation and example about the width of text strings.

link

    
answered by 14.12.2018 / 20:33
source
1

With utf8_decode () the problem should be resolved. 

$sql = "select * from tabla1 where campo1 like '%".utf8_decode("ñ")."%'";
//echo $sql; Si deseas imprimir la consulta en pantalla.
$datos = $this->objeto->listar() ;
$registros = sqlsrv_row_num($datos) ;
print_r($registros) ;
  
  • Sanitization and security in the parameters is your responsibility, it is important to prevent SQL injection.
    •   
        
    answered by 14.12.2018 в 19:22
    0

    Could you try using the like collate? 

    SELECT * FROM tabla1 WHERE campo1 LIKE '%ñ%' collate Latin1_General_CI_AI

    I hope you find it useful

        
    answered by 14.12.2018 в 19:12
    0

    With a utf8 it would solve your problem, this is going to make the connection with the database.

    Annex example:

    $ mysqli = new mysqli ('localhost', 'root', 'password', 'db');

    mysqli_set_charset ($ mysqli, 'utf8'); // line to place so you can handle special characters.

    It should be noted that the field in table1 must have collation (latin1_swedish_ci) at least that's how it worked for me.

    By doing the above, you do not need to modify your sql query.

    Greetings.

        
    answered by 14.12.2018 в 19:59
    strtok of tabulator with 4 spaces C Handling lists in python