Search in a field with JSON encoding

1

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

Content is written as follows:

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

In PhpMyAdmin, the select below does not return any results:

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

How do the above query return its record?

    
asked by anonymous 23.01.2015 / 20:26

2 answers

1

I found the solution here: link

Because MySQL uses C escape syntax in strings (for example, "\ n" to represent a new line), you need to double any \ "."

For example, to search for "\ n", you need to specify "\ n". To search for "\", 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 for.

The query looks like this:

SELECT *  
FROM funcionarios
WHERE custom LIKE '%Ana Concei\\u00e7\\u00e3o Dias%'
    
26.01.2015 / 15:34
1

My code below is not a complete solution, copy / paste will not work. It is meant to push you in the right direction. I suppose my code may have some errors, though I'm not seeing this. If someone finds the error, please DEIXE UM COMENTÁRIO.

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

$yourArray = json_decode($json)

Now you can enter the data into the table. I do not 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 select it without any problems.

    
24.01.2015 / 16:56