CODEWITHSUNDEEP
mysqlsqlcreate-table
ProgrammerPotato
ProgrammerPotato

Reputation: 515

Is it possible to have table in table (mysql)?

Suppose I defined music as a table and gave it 3 columns: album, artist, and track:

CREATE TABLE music (
    id int auto_increment primary key,
    album varchar(45) not null,
    artist varchar(30) not null, 
    track int(8)
);

Now I want another table releasedy which contains a column:

'Year' and the table music

I suppose for that I have to bind some row from table music with year from table releasedy in another table. So I can know which year contains which musics. If I'm not wrong I have to do that with foreign key. How should I proceed?

Upvotes: 2

Views: 15659

Answers (3)

Petru Sandru
Petru Sandru

Reputation: 1

class Functions { 
    public function getRows($sql){ 
         $result = mysql_query($sql); 
         while($row = mysql_fetch_assoc($result)){ 
               $data[] = $row; 
         } 
         return $data; 
    } 

    public function getMusicData(){ 
         $data = $this->getRows("SELECT * FROM music"); 
         return base64_encode(serialize($data)); 
    } 
} 

$func = new Functions(); 
$music = $func->getMusicData(); 
$sql = "insert into releasedy (id,Year) values (NULL,'".$music."')";
mysql_query($sql);

$id = 'your necessary id'; $read = "select * from releasedy where id = ".(int)$id; $data = mysql_query($read); $musics = unserialize(base64_decode($data['Year'])); foreach($musics as $music){ // do something }

Upvotes: 0

xtds
xtds

Reputation: 2513

There is no "table in a table", only tables and relations between them.

Table releasedy will have 2 columns, musicId & year. musicId is the foreign key to your music table.

Join (as you called bind) these two:

SELECT * 
FROM music m
INNER JOIN releasedy r ON m.id = r.musicId
WHERE year = ..

Which is all overkill in this example but it illustrates the "binding" you want.

Upvotes: 2

RandomSeed
RandomSeed

Reputation: 29769

You do not want "a table in a table", instead you want to match records from one table with records from another table in a query. You can create a view on top of this query and use the view later as it if were a regular table.

As you guessed, establish a foreign key relationship, then JOIN the two tables.

Upvotes: 4

Related Questions