I just don t see how to link fileds of one table to another table

[franklos]

Hi I ve been trying this for a while but seem to get nowhere.

A have two tables one composer and one publishers, the latter publishes sheet music from the fist.

 

composers contains

id

fname

lname

date of birth

name of composition

publisher

 

publishers contains

id

name

website

address

phone

email

 

I would like to diplay a link to the publisher details (stored in the publisher table) on the web page of a composer.

So when you EG click dvorack opus 9 in bes, the name of the publisher diplayed on that particular page would link to the details of that publisher.

I thinks I read I need to do a JOIN ar use a foreign key, tried all to no avail.

Can someone give me a wee push in the right direction or even better write a little PHP example so I can continue this search.

Google and the likes have been tried off course, cheers.

[mikosiko]

first... you should modify your table composers... look your current definition and thinks about it....  one composer can compose multiples pieces right?... how your current table can manage that ?  ... you should take out to another table the columns "name of composition" and "publisher id" and establish the relation between the composers table and this new table.

[franklos]

okay but thing is the tables have one field in common. The "publisher field in the table composer" equals the "name field in the table publisher". I was hoping these two could be eh linked?

[mikosiko]

...... The "publisher field in the table composer" equals the "name field in the table publisher". ...

 

That was obvious  .... but I'm trying to help you to have a better DB design....  your design is not good.

but answering your question... yes it is possible to JOIN both tables.

examples here: http://dev.mysql.com/doc/refman/5.0/en/join.html

 

But parodying someone that I don't remember  : "With your model, do that under your own risk... you have been Warned"

 

[franklos]

so you mean a table for the compositions, one for the composers and one for publishers?

Compositions holding an ID of the composer and an ID of the publisher.

Composer holding a ID of the compositions?

 

Something like that

[mikosiko]

so you mean a table for the compositions, one for the composers and one for publishers?

Yes

 

Compositions holding an ID of the composer and an ID of the publisher.

Yes

 

Composer holding a ID of the compositions?

No... only the previous one is necessary

 

Something like that

[franklos]

Hmm is there a way to do stuff like that with a querry, or should I just split 900 composer/compostion records manually and add composer ID s manually? Sound like a hell of a job.

[mikosiko]

Hmm is there a way to do stuff like that with a querry,

Of course.... INSERT .... SELECT...

 

or should I just split 900 composer/compostion records manually and add composer ID s manually? Sound like a hell of a job.

manually a hell of a job?... sure.... using ^^^ maybe 2 minutes max.

 

Sure....

[franklos]

Been busy, finally found some time

Here s tha table with the compostions:

	composers

Field	Type	Null	Default	Comments	MIME
idcomp	int(10)	No 	 	 	 
Composer	varchar(255)	Yes 	NULL 	 	 
Birth	varchar(255)	Yes 	NULL 	 	 
Nationality	varchar(255)	Yes 	NULL 	 	 
Opus	varchar(255)	Yes 	NULL 	 	 
Publisher	varchar(255)	Yes 	NULL 	 	 
Oddities	varchar(255)	Yes 	NULL 	 	 
Fname	varchar(255)	Yes 	NULL 	 	 
Lname	varchar(255)	Yes 	NULL 	 	 
information	varchar(25000)	No 	 	 	 
Indexes: 

Keyname	Type	Unique	Packed	Field	Cardinality	Collation	Null	Comment
idcomp	BTREE	No	No	idcomp	0	A		
Publisher	BTREE	No	No	Publisher	323	A	YES	

 

Here the one with the publshing companies:

 

publisher

Field	Type	Null	Default	Comments	MIME
idpub	int(10)	No 	 	 	 
abbreviation	varchar(30)	Yes 	NULL 	 	 
country	varchar(255)	Yes 	NULL 	 	 
street	varchar(255)	Yes 	NULL 	 	 
postcode	varchar(255)	Yes 	NULL 	 	 
city	varchar(255)	Yes 	NULL 	 	 
address	varchar(255)	Yes 	NULL 	 	 
name	varchar(255)	Yes 	NULL 	 	 
website	varchar(255)	Yes 	NULL 	 	 
email	varchar(255)	Yes 	NULL 	 	 
oddities	varchar(255)	Yes 	NULL 	 	 
phone	text	Yes 	NULL 	 	 
Indexes: 

Keyname	Type	Unique	Packed	Field	Cardinality	Collation	Null	Comment
idpub	BTREE	No	No	idpub	0	A		
abbreviation	BTREE	No	No	abbreviation	0	A	YES	

 

So I created a table called compositions:

 

composition

Field	Type	Null	Default	Comments	MIME
id	int(10)	No 	 	 	 
abbreviation	varchar(255)	No 	 	 	 
composer	varchar(255)	No 	 	 	 
idcomp	int(11)	No 	 	 	 
idpub	int(11)	No 	 	 	 
Opus	varchar(255)	No 	 	 	 
Indexes: 

Keyname	Type	Unique	Packed	Field	Cardinality	Collation	Null	Comment
id	BTREE	No	No	id	2	A		

 

I started to try and construct a query and ended up with this:

INSERT INTO composition (idcomp,Opus)
SELECT idcomp,Opus  
FROM quartets

 

Off course it just copied all the data into the latter table, and In think I need to somehow split the composers and their compostions -> the compostitions table all having the same ID on that table? Is that correct? How do I do that with a query?