I want to display data from a foreign key table in my main table, how do I do?

0

In PHP how do I instead of the foreign key id, the field that has the data appears?

I have a room table that has column id , column nome and column tipo .

And also I have the movie table, the movie rooms I've registered in another table and I want to call the room type, but when I put it to display in php, instead of tipo is appearing id of the room.

Below the table code ...

CREATE TABLE IF NOT EXISTS 'filmes' (

'id' int(5) NOT NULL AUTO_INCREMENT,

'banner' mediumblob NOT NULL,

'nome' varchar(50) NOT NULL,

'genero' varchar(50) NOT NULL,

'tempo' varchar(50) NOT NULL,

'classificacao' varchar(50) NOT NULL,

'sala_id' int(5) NOT NULL,

'sinopse' varchar(1090) NOT NULL,

PRIMARY KEY ('id'),

KEY  'sala_id_fk' ('sala_id')

) 

CREATE TABLE IF NOT EXISTS 'sala' (

'id' int(5) NOT NULL AUTO_INCREMENT,

'nome' varchar(20) NOT NULL,

'tipo' varchar(20) NOT NULL,

 PRIMARY KEY ('id')

)
    
asked by anonymous 10.11.2017 / 20:54

1 answer

1

Well, basically, it's just a matter of relationship between tables ( Joins ):

SELECT FILMES.ID  
     , FILMES.NOME  
     , SALA.NOME  
     , SALA.TIPO   
  FROM FILMES  
  LEFT JOIN SALA ON (FILMES.SALA_ID = SALA.ID)

Concatenating only the Type column value:

SELECT GROUP_CONCAT(SALA.TIPO) AS TIPO   
  FROM FILMES  
  LEFT JOIN SALA ON (FILMES.SALA_ID = SALA.ID)

In this way, you will only have the Type field without the need for a while.

    
10.11.2017 / 20:59