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

Question

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?

Solution 1

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.

Solution 2

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.

Comments

[Amir Shabani]

Please edit your answer and explain why your code works.

[Admin]

$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 }