Search in a JSON encoding field

Question

There is a field in the table where the content before being written is encoded in JSON.

The content is saved as follows:

[["nome_completo","Ana Concei\u00e7\u00e3o Dias"],["idade","27 anos"],["cidade","Lisboa"]]

In Phpmyadmin, select below returns no results:

SELECT * 
FROM funcionarios
WHERE custom LIKE '%Ana Concei\u00e7\u00e3o Dias%'

How to make the above query return the respective record?

Answer 1

My code below is not a complete solution, copy/Paste will not work. It is intended to push you in the right direction. I suppose my code may have some errors, although I’m not seeing it. If anyone finds the error, please, DEIXE UM COMENTÁRIO.

First I wanted to know why you converted the results to Json before saving to the bank. You already have an array, you don’t need a JSON string in your case. You need to convert back with this:

$yourArray = json_decode($json)

Now you can enter the data into the table. I don’t know how your Insert is , but if I look at your code, I think your sql would look something like this:

$sql = 'INSERT INTO tabela(id, nome_completo, idade, cidade) VALUES (:id, :nome_completo, :idade, :cidade)';

So your code would look something like this:

$stmt = $db->prepare( $sql );
$sql = 'INSERT INTO tabela(id, nome_completo, idade, cidade) VALUES (:id, :nome_completo, :idade, :cidade)';
$stmt->bindParam(':id', $yourArray ['id']);
$stmt->bindParam(':nome_completo', $yourArray['nome_completo']);
...
$stmt = $dbh->prepare( $sql );
$stmt->execute();

This is the correct way to store your "Json" data, so you can make select smoothly.

Answer 2

I found the solution here: http://dev.mysql.com/doc/refman/5.6/en/string-comparison-functions.html#operator_like

Since Mysql uses C escape syntax in strings ( for example, "n" to represent a new line ), it is necessary to double any "\".

For example, to search " n" it is necessary to specify " n". To search " ", specify it as " " ; this is because the backslashes are deleted once by the parser and again when pattern matching is done, leaving a single backslash to be searched.

The query was as follows:

SELECT *  
FROM funcionarios
WHERE custom LIKE '%Ana Concei\\\\u00e7\\\\u00e3o Dias%'