Problem when mounting JSON with database return

Asked

Viewed 421 times

0

It all comes down to this image: Dados em amarelo são exibidos através de uma conexão normal com banco de dados e o mysql, as duas primeiras linhas "se repetem" porque temos uma relação de 1 para N no banco de dados, observem que das datas estão diferentes.

The problem is in the JSON mount, it does not group the first result and assigns an array to the dates, as it should be in the image below: Aqui é como deveria ficar, as datas agrupadas para o data, ficando num array

Is there a plugin in mysql or api in php that solves this problem, mount json or Gjson from a 1-N-related data set: Dados no SGBD quero transformar em Json agrupado/certinho

How to do this?

Follow the querys Pessoa

Inscricao

Relacionamento 1-n

Mais um complemento, código que gera a saída

I used php to exemplify what I want, however you can see that php does nothing else, it only displays the result of the query, I want to do the data manipulation in the query, but if it is not possible, the php solution will be welcome. : D

php mysql json

  • Doesn’t seem wrong, there are 2 Milrak so the date array. Show your query.

  • @Papacharlie there are no two Milrak in the Pessoa table, but two milrak entries on the inscription table. Actually the query returns two lines from it, however I would like the json to group in a single object milrak that would have two dates understands?

  • Enter the code php you’re using.

  • @Edilson I’m only using Mysql, functions like JSON_ARRAY and JSON_OBJECT. I don’t know solution in php for the problem. :/

  • Edit the question, and place the part of the code responsible for generating this sequence.

  • @Edilson doesn’t know which title to change to, so it stays the same and I added the code that makes the sequence.

  • Posts code as text, not as image.

Show 2 more comments

2 answers

1


A simple way to fix the problem, with the help of Mysql is to use the GROUP_CONCAT() and then use the explode() of PHP.

This way it would be:

SELECT nome, group_concat(data) FROM pessoa GROUP BY nome

This will return all dates in a single row, grouped by name. At the end the Mysql result will be the following, for example:

+---------+-----------------------+
| Nome    | Data                  |
+---------+-----------------------+
| Ana     | 2016-01-01,2017-01-01 |
| Bob     | 2000-01-01            |
| Charlie | 2016-12-01,2017-01-03 |
+---------+-----------------------+

That way all the dates will be together, allowing you to use a explode(',', $Data) to break the dates, which are separated by comma.

Then just use:

while($linha = mysqli_fetch_array($query)){

    $json_array[] = [
        'Nome' => $linha['nome'],
        'Data' => explode(',', $linha['data'])
    ];

}

echo json_encode($json_array);

  • sorry my ignorance, it seems that you have found a good solution to the problem... But when the DATA field is for example a text field, and in that text there is a comma in the middle, we won’t have any more problems?

  • The documentation, from GROUP_CONCAT, specifies this. You can change the "tab" using SEPARATOR str_val. For example, group_concat(data SEPARATOR '-- MEU SEPARADOR --'), doing this will all be like 2016-01-01-- MEU SEPARADOR --2017-01-01, you can just make a explode of -- MEU SEPARADOR --.

  • I found this the best of the answers, however I am solving the problem with array in php gave a darn job, is not 100% but will leave, when you are ready I will post here :)

0

There’s a similar question in stackoverflow. To achieve the desired effect you can use some Orm (frameworks Aravel, cakephp, ... or something standalone like notorm). Using any of these will require a relative learning curve.

The question link above suggests two alternatives:

query the first table, go through the results with a foreach, create a new array for each row returned. Then make a second query for each returned row. Another foreach to iterate over each row of the second query and create a new array, to place within the first query.

the second possibility is to make a single cosulta (as you did), and mount with the fetchAll array the hierarchy you want.

Browser other questions tagged php mysql json

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