Group two columns into two mysql tables

Asked

Viewed 195 times

-1

I have two tables that I am joining to recover the related data in this way:

Tabela Processos:              Tabela Anexos

id  |  processos              processoid  |  titulo         

1   |  Processo 1             1           |  REQUERIMENTO 
2   |  Processo 2             1           |  Apostilamento
3   |  Processo 3             2           |  requisição
4   |  Processo 4             2           |  REQUERIMENTO  
5   |  Processo 5             3           |  Apostilamento
6   |  Processo 6             3           |  CRAF

SELECT processos.processos, 
       processos.id as processoid, 
       anexos.*, 
       anexos.id as anexoid 
  FROM anexos 
  INNER JOIN processos ON (processos.id = anexos.processoid) 
  GROUP By processos.id 
  ORDER By anexos.processoid

With the above query it returns as follows:

Processo 1 =>  REQUERIMENTO 
Processo 1 =>  Apostilamento

Processo 2 =>  requisição
Processo 2 =>  REQUERIMENTO

Processo 3 =>  Apostilamento
Processo 3 =>  CRAF

What I need is below:

Processo 1 =>  REQUERIMENTO 
               Apostilamento

Processo 2 =>  requisição
               REQUERIMENTO

Processo 3 =>  Apostilamento
               CRAF

I believe it is only the use of GROUP BY but in this case it does not work.

php mysql sql database pdo

  • Why doesn’t the first result meet you?

  • Hello, do not answer me because I want to display the value of the grouped and non-repeated process.

  • It’s not simple to do that, you’d have to put a view or a program together to do that, and they’re very complex to program. A quick solution to the problem would be you program your php to present the way you want.

  • so buddy, I know you have to manipulate the query, in this case I think it has to do with GROUP By because I need to return the process field 1 time for each title of the Attachments table, in this case it repeats the process field.

  • Group By does not solve Cartesian plan, in your case is returning the index twice because the results of the second column is different.

  • would doing two separate query resolve?

  • It will always bring two results?

  • no, it’s random, some 3 other 5...

  • That complicates it. I insist that you can do it with php. You want to present this in a certain table?

  • I’ll list the documents of each process in relation to the title, maybe use table but I think I’ll do something more intuitive.

  • You bring the database result into array or object ?

  • I bring in array, do a foreach and display the processes and titles.

  • That return is in that language?

  • return is PHP direct.

  • Then the names of the processes would be the keys, correct?

  • The way you want it, just to do it in the language, in this case php

  • Exactly, and I would have him mount screen through conditions as he traverses the loop.

  • This should be done in your application and not in the SQL query since this "repetition" is what is expected from the query result. The use of some report Generator makes it easier to do what you want.

Show 13 more comments

1 answer

0

Thanks for the personal help, it was not really a generic solution that I found, but rather a different logic and the understanding that logic would not need to display the names of the processes but only the titles, in which case no change was necessary, since the goal is to display the titles of each process.

So the Query stayed that way and returned the way below:

        $valida = $this->db->query("SELECT processos.processos, processos.id as processoid, anexos.*, anexos.id as anexoid FROM anexos INNER JOIN processos ON (processos.id = anexos.processoid) WHERE processos.id = '$this->processo'");




foreach ($valida->fetchAll() as $folderProcesso) {
        echo $folderProcesso['titulo'] .' Data: '.folderProcesso['data'];
}

Note that it was not necessary to use GROUP BY since the titles are different and do not duplicate.

Browser other questions tagged php mysql sql database pdo

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