connecting tables help

[jwcsk8r]

Hey everyone, I am going to try and explain what I am trying to do as clearly as possible but even I think I am a little confused.

 

I have one table in a database that is essentially stuff from a commenting script. Things that are posted in it are name, timestamp, id, etc...

 

After this I have another table in the same database that is like a comment system for the first database with id, name, time.

 

How can I set it up so that comments from the second database are only shown if it matches the id from the first database that the person commented on.

[artacus]

You need a foreign key in table2 so you can join it to the primary key in table1.

[jwcsk8r]

okay, after i set the primary and foreign keys what should i do? Should it automatically update in the tables? Do the amount of rows in each table have to be the same for this to work? (They are not) THanks again

[artacus]

No you can have 1 to 1, 1 to many or many to many relationships. I'd suggest spending a few hours wrapping your head around how relational databases work.

 

http://en.wikipedia.org/wiki/Relational_database

[jwcsk8r]

thanks artacus, i think im starting to better understand. and if nothing else works from here on out i learned the word tuple. I assigned a foreign key in table 2, and linked it to a primary key in table 1. Heres the SQL...

 

TABLE 1 -

 

CREATE TABLE `person` (

  `id` varchar(11) NOT NULL default '',

  `firstname` varchar(20) NOT NULL default '',

  `from_where` varchar(20) NOT NULL default '',

  `total_votes` int(11) NOT NULL default '0',

  `total_value` varchar(11) NOT NULL default '0',

  `used_ips` longtext NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

ANd Table 2..

 

CREATE TABLE `comtbl` (

  `postID` int(11) NOT NULL auto_increment,

  `posterNAME` text NOT NULL,

  `postTXT` text NOT NULL,

  `foreign_id_from_PERSON` varchar(11) NOT NULL default '',

  PRIMARY KEY  (`postID`),

  KEY `foreign_id_from_PERSON` (`foreign_id_from_PERSON`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

with the constraints for the second table being...

ALTER TABLE `comtbl`

  ADD CONSTRAINT `comtbl` FOREIGN KEY (`foreign_id_from_PERSON`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

____________________________________________________

 

Ok, so what I would like to happen is for when someone posts a comment into table 2, it posts the 'id' from the table person into the 'foreign_id_from_person" in table 2. this way when I go to print it on the page i can say...

 

mysql_query("SELECT * FROM comtbl WHERE foreign_id_from_PERSON='$row_id_from_table_person'")

 

this way it only prints out comments related to the post at hand. Technically, I think it should be working but it is not. I keep getting this error.."Error adding entry: Cannot add or update a child row: a foreign key constraint fails". I've been reading for the last 2-3 hours how to fix it and i cant seem to get anything to work. Thanks again for any help!

[artacus]

You're getting closer. You dont need the `name` field in comtbl because your name is already stored in the person table and you can link to it using the fk. BTW, you don't need that ugly column name. Usually your foreign key is called something like person_id.