Query in mysql with php does not return result in queries with accents

Question

Hello I have a system in php/mysql using zend 1.12, but when consulting the mysql database with a word that has no accent and in the table has accent, does not return any result for example:

when searching: {Joao} and in the database this {john}, does not return results.

the database is configured with: Innodb latin1_general_ci.

I want to know if there is a way I remove the accents from the records that are already in the table for the query to occur naturally when the user does the word search without accent.

ex: usuario digita: {atencao} in the table I have the phrase: {Be very careful when returning from the trip.}

this returns 0 result.

I do not know if I could be clear, I have looked here on the forum and I did not find anything like.

if someone can indicate a link or give some hint or even clarify this.

Thank you.

Answer 1

You can perform the query without it considering the accent as follows:

SELECT *
FROM minhaTabela
WHERE minhaColuna LIKE '%joao%' COLLATE utf8_general_ci

Considerations

• The COLLATE depends on what is defined in your database connection. For more information you can see:

  10.1.4 Connection Character Sets and Collations

• Checks if effectively in the database is stored joão and not its representation in HTML entities.

Alternative

You can resolve the issue faster by editing the table in the database and changing the collation of the column where you are performing the search for UTF-8, specifically the utf8_unicode_ci, thus ensuring that accented letters will be identified in their version with or without accent.

---

Zend 1.12

To perform the query using the object Zend_Db_Select via interface Fluent:

$select = $db->select()
    ->from( "minhaTabela", "*" )
    ->where( "minhaColuna LIKE '%joao%' COLLATE utf8_general_ci" );