Treating Many-to-Many Query

Question

Treating Many-to-Many Query

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I got into a doubt about Many-to-Many relationships and I hope I'm not asking a repeat question, but I did not find the solution to my question in the least.

It turns out that I have an image table, a job table, and a relationship table that makes a Many-to-Many relationship between the first two.

Each job can have several images related to it. So I did a query as follows:

select jobs.*, f1.file_name, f2.file_name 
from jobs 
    left JOIN photos_per_job p ON p.job_id = jobs.id
    left JOIN fotos f1 on p.photo_id = f1.id 
    inner join fotos f2 on jobs.foto_capa = f2.id

And I got the result below:

My problem now is to organize this information in the view. If you notice the work that has three photos repeats in the result three times, but when I show the works to the user I will show one job at a time and each job will have its series of photos.

Is there a way to organize the result in a way that facilitates my loop in the view or to organize this better do I need to work on the loop itself?

php mysql relacionamento

asked by anonymous 23.06.2016 / 05:16

2 answers

0

You have to group it.

Do something like this:

select jobs.*, f1.file_name, f2.file_name 
from jobs j 
    left JOIN photos_per_job p ON p.job_id = j.id
    left JOIN fotos f1 on p.photo_id = f1.id 
    inner join fotos f2 on j.foto_capa = f2.id
 group by j.id, j.titulo, j.foto_capa, j.descricao, j.bg_color,
   j.font_color, j.time, f2.file_name

23.06.2016 / 13:39

how to add an image only in checked checkbox [duplicate] How to make a local variable in C (or C ++) global?

score 1 · Accepted Answer

You can implement this logic in PHP itself, change your select like this:

SELECT jobs.*, f1.file_name, f2.file_name AS nome_foto_capa 
from jobs 
    LEFT JOIN photos_per_job p ON p.job_id = jobs.id
    LEFT JOIN fotos f1 on p.photo_id = f1.id 
    INNER JOIN fotos f2 on jobs.foto_capa = f2.id

Use this PHP code to handle the result of the bank:

$trabalhos = array();
/* LAÇO QUE PERCORRE OS RESULTADOS RETORNADO PELO MYSQL (PODE SUBSTIRUIR PELO QUE VOCE UTILIZA)*/
foreach($results as $row){
    if(!isset($trabalhos[$row['id']])){
        $trabalhos[$row['id']] = array(
            'id' => $row['id'], 
            'titulo' => $row['titulo'], 
            'foto_capa' => $row['foto_capa'], 
            'descricao' => $row['descricao'], 
            'bg_color' => $row['bg_color'], 
            'font_color' => $row['font_color'], 
            'time' => $row['time'], 
            'nome_foto_capa' => $row['nome_foto_capa']
        );
    }
    $trabalhos[$row['id']]['fotos'][] = $row['file_name'];  
}

Or you can group the name of the photos using GRUOUP_CONCAT , so the name of the photos they will appear concatenated only in a field separated by commas, after and only give an explode by the comma. But depending on the amount of photos I do not recommend using this approach because GROUP_CONCAT has a character limit.

SELECT jobs.*, GROUP_CONCAT(f1.file_name) AS fotos, f2.file_name AS nome_foto_capa 
from jobs 
    LEFT JOIN photos_per_job p ON p.job_id = jobs.id
    LEFT JOIN fotos f1 on p.photo_id = f1.id 
    INNER JOIN fotos f2 on jobs.foto_capa = f2.id
GROUP BY jobs.id