Home / Questions / 456819
Accepted
John Doe
Asked: 2022-06-03 00:10:23 +0800 CST 2022-06-03 00:10:23 +0800 CST

Link two sql tables using two functions in MVC PHP

  • 772

I have two functions where I make two queries and what I want is to join both queries but I can't find a way to do it.

I have a getLanguages() function:

    public static function obtieneIdiomas()
{

$sql= "SELECT nombre as idiomas FROM idiomas";
$resultado=self::ejecutaConsulta($sql);
$arrayIdiomas=array();

while ($fila=$resultado->fetch(PDO::FETCH_ASSOC))
             {
                  $arrayIdiomas[]= $fila["idiomas"];                               
             } 
        
         return $arrayIdiomas; 
}

And a getCandidate() function:

public static function obtieneCandidatos()

{    
$sql2= "SELECT distinct c.dni, c.nombre, c.apellidos, c.sexo FROM candidatos c
inner join idiomas_encuesta i on c.dni=i.dni_candidato
inner join idiomas id on i.id_idioma=id.id;";

$resultado2=self::ejecutaConsulta($sql2);
$arrayCandidatos=array();

while ($fila=$resultado2->fetch(PDO::FETCH_ASSOC))
             {
                  $arrayCandidatos[]=new Candidato ($fila);
             }
        
         return $arrayCandidatos; 
}

In the controller I call these two functions to build the arrays:

$arrayDatosPersonas=ClaseBase::obtieneCandidatos();
$arrayIdiomasPersonas=ClaseBase::obtieneIdiomas();

And this is as far as I've been able to get:

enter image description here

This is the query that should output:

enter image description here

But without repeating the personal names, that is, in Miguel's Language field, French and Chinese must appear.

I've thought that maybe we should use the getCandidates function the getLanguages ​​function but I have no idea how to do it. There is a table that shares the key between candidates and languages ​​and I imagine that they will have to be compared in some way.

Let's see if you can give me a hand thanks.

PS: Thinking about it, maybe it's not necessary to use the getLanguages() function, maybe you could do two selects in getCandidate()?

php

1 Answers

  1. Best Answer

    You can get the results you want in a single query by using an aggregation function .

    You don't state what database engine you're using. If you're using MySQL, that function is GROUP_CONCAT . If you use a different engine, it will have a function with similar characteristics at its disposal. Oracle for example has LISTAGG .

    In this link you have a small example so you can see how it works with some test data, although I will also paste it here below.

    CREATE TABLE CANDIDATOS (
  DNI varchar(9) NOT NULL,
  NOMBRE varchar(20) DEFAULT NULL,
  APELLIDOS varchar(20) DEFAULT NULL,
  SEXO char(1) DEFAULT NULL,
  PRIMARY KEY (`DNI`)
);


CREATE TABLE IDIOMAS_ENCUESTA (
  ID INT NOT NULL,
  DNI_CANDIDATO varchar(9) NOT NULL,
  ID_IDIOMA INT NOT NULL,
  PRIMARY KEY (`ID`)
);


CREATE TABLE IDIOMAS (
  ID INT NOT NULL,
  DESCRIPCION varchar(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
);


INSERT INTO CANDIDATOS (DNI, NOMBRE, APELLIDOS, SEXO) VALUES ('1','RICARDO','RUBIO','V');
INSERT INTO CANDIDATOS (DNI, NOMBRE, APELLIDOS, SEXO) VALUES ('2','JUAN','TRUJILLO','V');

INSERT INTO IDIOMAS (ID, DESCRIPCION) VALUE (1,'INGLES');
INSERT INTO IDIOMAS (ID, DESCRIPCION) VALUE (2,'FRANCES');
INSERT INTO IDIOMAS (ID, DESCRIPCION) VALUE (3,'ITALIANO');
INSERT INTO IDIOMAS (ID, DESCRIPCION) VALUE (4,'ALEMAN');

INSERT INTO IDIOMAS_ENCUESTA (ID, DNI_CANDIDATO, ID_IDIOMA) VALUE (1,'1',1);
INSERT INTO IDIOMAS_ENCUESTA (ID, DNI_CANDIDATO, ID_IDIOMA) VALUE (2,'1',2);

INSERT INTO IDIOMAS_ENCUESTA (ID, DNI_CANDIDATO, ID_IDIOMA) VALUE (3,'2',2);
INSERT INTO IDIOMAS_ENCUESTA (ID, DNI_CANDIDATO, ID_IDIOMA) VALUE (4,'2',3);
INSERT INTO IDIOMAS_ENCUESTA (ID, DNI_CANDIDATO, ID_IDIOMA) VALUE (5,'2',4);

    Modifying the query that you include a bit, we obtain the expected result

    SELECT C.DNI, 
       C.NOMBRE, 
       C.APELLIDOS, 
       C.SEXO, 
       GROUP_CONCAT(ID.DESCRIPCION SEPARATOR ',') AS LISTA_IDIOMAS
FROM candidatos c
inner join idiomas_encuesta i on c.dni = i.dni_candidato
inner join idiomas id on i.id_idioma = id.id
GROUP BY C.DNI, C.NOMBRE, C.APELLIDOS, C.SEXO;

    Result with my data set reduced

    DNI NOMBRE  APELLIDOS   SEXO    LISTA_IDIOMAS
1   RICARDO RUBIO       V       FRANCES,INGLES
2   JUAN    TRUJILLO    V       ALEMAN,ITALIANO,FRANCES

    Next time try to add to your question the structure of the tables (CREATE) and some set of test data (INSERT) so that whoever wants to help you can copy them and add them to their answer. You are always in time to edit and include this information.

    BONUS
    If you want the list of languages ​​to be ordered, you have to apply an ORDER before applying the aggregation. Here I put another example query with the data ordered in another way.

    SELECT APELLIDOS, 
       NOMBRE, 
       DNI, 
       SEXO, 
       GROUP_CONCAT(IDIOMA SEPARATOR ',') AS LISTA_IDIOMAS
FROM (
SELECT C.APELLIDOS AS APELLIDOS, 
       C.NOMBRE AS NOMBRE, 
       C.DNI AS DNI, 
       C.SEXO AS SEXO, 
       ID.DESCRIPCION AS IDIOMA
FROM candidatos c
inner join idiomas_encuesta i on c.dni = i.dni_candidato
inner join idiomas id on i.id_idioma = id.id
ORDER BY IDIOMA, C.APELLIDOS, C.NOMBRE
) AS A
GROUP BY APELLIDOS, NOMBRE, DNI, SEXO;

    bonus result

    APELLIDOS   NOMBRE  DNI SEXO    LISTA_IDIOMAS
RUBIO       RICARDO 1   V       INGLES,FRANCES
TRUJILLO    JUAN    2   V       ITALIANO,FRANCES,ALEMAN
    • 1