Many to many php array

[gmc1103]

Hi

I'm having trouble regarding having a record with only one line instead of several records I have an issue with the following: A user choose a place, and that place can have several subplaces each one with an id, so i have one main table, another one with places and other many to many. When i use a select (pdo) gives me an array, the problem is since the user choose for example 5 places, a i have five records and i would like to have only one. This how i'm doing but no success

 $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
			$final = array();
			foreach ($result as $row) {
				$idVisita = $row['idVisita'];
				if (!isset($final[$idVisita])) {
					$final[$idVisita]['idVisita'] = $row['idVisita'];
					$final[$idVisita]['school'] = $row['school'];
					$final[$idVisita]['Departement'] = $row['Departement'];
					$final[$idVisita]['Name'] = $row['Name'];
					$final[$idVisita]['Class'] = array();
					$final[$idVisita]['Destination'] = $row['Destination'];
					$final[$idVisita]['StartDate'] = $row['StartDate'];
					$final[$idVisita]['number'] = $row['Number'];
					$final[$idVisita]['Total'] = $row['Total'];
					$final[$idVisita]['Pdf'] = $row['PdfV'];
					$final[$idVisita]['Evaluation'] = $row['Evaluation'];					
					$final[$idVisita]['Description'] = array();
				}
				$final[$idVisita]['Description'][] = $row['Description'];
				$final[$idVisita]['Class'][] = $row['Class'];
			}
			foreach ($final as $idVisita => $reservation) {
				$json[]=$reservation;
			}
            echo json_encode($json);

So but this code you can see i must have 2 arrays to join "Class" and "Description". When i use this code i have wrong records. Instead of having 4 "Turmas" i have something like this

2ºB,1ºA,1ºA,1ºB,2ºA,2ºA,2ºB,1ºA,1ºB,1ºB,2ºA,2ºB,2ºB,1ºA,1ºB,1ºB,2ºA,2ºB,1ºA,1ºA,1ºB,2ºA,2ºA,2ºB,2ºB,1ºA,1ºA,1ºB,2ºA,2ºA,2ºB,1ºA,1ºA,1ºB,2ºA,2ºA,2ºB,1ºA,1ºB,1ºB,2ºA,2ºB,2ºB,1ºA,1ºB,1ºB,2ºA,2ºB,2ºB,1ºA

And it's easy to see they repeated...

 

This is my query

SELECT v.idVisita, e.Escola, d.Departamento, dom.Descricao, p.Nome, t.Turma, v.Destino, v.StartDate, vt.numAlunos, v.TotalAluno,v.PdfVisita, v.Avaliacao
FROM
    visitas AS v
    INNER JOIN escolas AS e ON (v.idEscola = e.idEscola)
    INNER JOIN visitasturmas AS vt ON (v.idVisita = vt.idVisita)
    INNER JOIN turmas AS t ON (vt.idTurma = t.idTurma)
    INNER JOIN professores AS p ON (v.idProfessor = p.idProfessor) 
    INNER JOIN visitasdepartamentos AS vd ON (v.idVisita = vd.idVisita)
    INNER JOIN departamentos AS d ON (vd.idDepartamento = d.idDepartamento)
    INNER JOIN visitasdominios AS vds ON(v.idVisita = vds.idVisita)
    INNER JOIN dominios AS dom ON (vds.idDominio = dom.idDominios)
    ORDER BY v.StartDate

Any help?

[glenelkins1984]

Hey

 

I'm not quite sure i understand what you're asking but going on a whim here, if you just want 1 record why don't you add a limit to the db query? LIMIT 1 ?

 

You can also use the PDO fetch() rather than fetchAll() to get a single row - you maybe could also use PDO::FETCH_OBJ instead of PDO::FETCH_ACCOC to get yourself a nice single object?

 

Maybe i am misunderstanding what you're asking?

[gmc1103]

Hi

 

Thank you for your reply.

No it is not that.

This is a model where a user (teacher) can schedule a study visit with several classes.

One visit can have one or many class.

So i have one table visit, another table class and one table many to many with visit details.

So when i run that query if i have 4 classes for one visit i only want to have one line with:

Visit

Destination     Date                    Class

AAAA             2017-08-30          Class A, Class B, Class C, Class D

 

instead of 4 records ..

[Barand]

You could get that output with

SELECT
    destination
  , date
  , GROUP_CONCAT(class) as classes
FROM
   ...
GROUP BY
    destination, date

NOTE: the values of any column not in the group by clause will be arbitrary values from a record in the group (ie meaningless) unless directly related (eg destination_id, destination_name)

[Barand]

Start your own thread - don't hijack someone else's

[gmc1103]

Hi Barand

 

How are you? Thank you for the reply...

It is better to show my database structure and point of that structure

 

 

So the main table is "Visitas" then i have one table "Domains" (Dominios) and another one "Classes" (Turmas)

As you can see, i have 2 other tables related between "Visitas" and the other 2 mentioned.

Why table between "Visitas" and  "Classes" (Turmas)? Table "VisitasTurmas"

Because a visit can have several classes and in each classes not all students (field : numAlunos) could go to that visit. 

What is a "Domain" ? It's anual school plans (26 domains can be choosen)

Why table between  "Visitas" and "Domains" (Dominios)? Table "VisitasDominios"

Because each visit have at least 2 and max of 6 domains and then i will have to use a chart where the principle can analyse wich domains are most used.

 

Now my example when i have inserted a new study visit

I have choosed 6 domains

 

Table "VisitasDominios" (fields: idAnoEscolar (school year) ,  idVisita (generated id from table "Visitas"), idDominio (id from table Dominios) N-1)

idAnoEscolar  idVisita  idDominio  
------------  --------  -----------
           1         1            2
           1         1            3
           1         1            6
           1         1           10
           1         1           15
           1         1           18

, and 4 Classes

 

Table "VisitasTurmas" (  idVisita (generated id from table "Visitas"), idTurma (id from table Turmas) N-1, numAlunos (number of students going to the visit), idAnoEscolar (school year))

idVisita  idTurma  numAlunos  idAnoEscolar  
--------  -------  ---------  --------------
       1        1         12               1
       1        2          3               1
       1        3         23               1
       1        4         14               1

So when i run a simplified version of my query it gives me

idVisita  Escola  Domains                                         Classes   Destino  StartDate   numAlunos  TotalAluno  
--------  ------  ------------------------------------------------  ------  -------  ----------  ---------  ------------
       1  Carmo   1.2 - Motivação e Empenho                         1ºA     wq       2017-08-28         12           234
       1  Carmo   1.2 - Motivação e Empenho                         1ºB     wq       2017-08-28          3           234
       1  Carmo   1.2 - Motivação e Empenho                         2ºA     wq       2017-08-28         23           234
       1  Carmo   1.2 - Motivação e Empenho                         2ºB     wq       2017-08-28         14           234
       1  Carmo   1.3 - Abertura à Inovação                         1ºA     wq       2017-08-28         12           234
       1  Carmo   1.3 - Abertura à Inovação                         1ºB     wq       2017-08-28          3           234
       1  Carmo   1.3 - Abertura à Inovação                         2ºA     wq       2017-08-28         23           234
       1  Carmo   1.3 - Abertura à Inovação                         2ºB     wq       2017-08-28         14           234
       1  Carmo   2.1 - Infraestruturas                             1ºA     wq       2017-08-28         12           234
       1  Carmo   2.1 - Infraestruturas                             1ºB     wq       2017-08-28          3           234
       1  Carmo   2.1 - Infraestruturas                             2ºA     wq       2017-08-28         23           234
       1  Carmo   2.1 - Infraestruturas                             2ºB     wq       2017-08-28         14           234
       1  Carmo   2.5 - Escola para todos                           1ºA     wq       2017-08-28         12           234
       1  Carmo   2.5 - Escola para todos                           1ºB     wq       2017-08-28          3           234
       1  Carmo   2.5 - Escola para todos                           2ºA     wq       2017-08-28         23           234
       1  Carmo   2.5 - Escola para todos                           2ºB     wq       2017-08-28         14           234
       1  Carmo   3.1.3 - Diferenciação e apoio                     1ºA     wq       2017-08-28         12           234
       1  Carmo   3.1.3 - Diferenciação e apoio                     1ºB     wq       2017-08-28          3           234
       1  Carmo   3.1.3 - Diferenciação e apoio                     2ºA     wq       2017-08-28         23           234
       1  Carmo   3.1.3 - Diferenciação e apoio                     2ºB     wq       2017-08-28         14           234
       1  Carmo   3.1.6 - Valorização do saber ser e saber estar    1ºA     wq       2017-08-28         12           234
       1  Carmo   3.1.6 - Valorização do saber ser e saber estar    1ºB     wq       2017-08-28          3           234
       1  Carmo   3.1.6 - Valorização do saber ser e saber estar    2ºA     wq       2017-08-28         23           234
       1  Carmo   3.1.6 - Valorização do saber ser e saber estar    2ºB     wq       2017-08-28         14           234

So as you can see, 6 domains and 4 classes gives me 24 records wich is right

 

With your query it gives me this

idVisita  Escola  Nome                             Classes                                                                                                                  Destino  StartDate   numAlunos  TotalAluno  
--------  ------  -------------------------------  -----------------------------------------------------------------------------------------------------------------------  -------  ----------  ---------  ------------
       1  Carmo   Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx   2ºA,1ºA,2ºB,2ºA,1ºA,2ºB,1ºB,1ºA,2ºA,1ºB,2ºA,1ºB,2ºB,2ºA,1ºA,2ºB,1ºB,1ºA,2ºB,1ºB,1ºA,2ºA,1ºB,2ºB                          wq       2017-08-28         23           234

Wich is not what i pretend, if i have only 4 classes it is repeated 

 

It is well explained?

Edited August 31, 2017 by gmc1103

[glenelkins1984]

You might be better off building up the details you want using PHP rather than trying to bash your head in with the mysql. 

 

So select your teachers from the database with relevant details, loop over them all with PHP and pull the details of classes for each teacher on their own.

 

Depending on how large your data is going to become for a single teacher this could work, or it could be very inefficient.

 

So just a bit of an example:

$teachers=[];
foreach($teacherDB as $t){
    $teacherClasses = pull the classes in a db query

    $teacher = (object)[
      'details'=>$t,
      'classes'=>array of class objects
    ];

    $teachers[] = $teacher;
}

This code isn't anything really just trying to show an example of how you could do it.

[Barand]

Your first post talked about places and subplaces. Your second was about destinations. None of these terms appear in your data. So, what is your query intended to show. What is the desired output?

 

EDIT: Only getting a single row with group_concat suggests a missing group by clause.

Edited August 31, 2017 by Barand

[gmc1103]

Hi Barand

 

It was an example.

The database structure is what i'm using. (domains and classes)

 

I'm still testing what it the best way to achieve what i'm pretend