Joining tables with foreign keys

[DT107]

Hi I am working on a job board and have two tables members and jobs. How can I join the two using foreign keys.

 

 

Here is the members table

 

 

 

CREATE TABLE `members` (
`id` int(4) NOT NULL auto_increment,
`username` varchar(65) NOT NULL default '',
`password` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

 

 

 

Here is the jobs table

 

CREATE TABLE IF NOT EXISTS `Jobs` (
`id` int(11) NOT NULL auto_increment,
`Job_Title` varchar(50) NOT NULL,
 `Company` varchar(50) NOT NULL,
 `Salary` varchar(50) NOT NULL,
 `Description` varchar(500) NOT NULL,
 `Phone_Number` varchar(50) NOT NULL,
 `Requirements` varchar(200) NOT NULL,
 `Job_Category` varchar(50) NOT NULL,
 `Job_Type` varchar(50) NOT NULL,
 `Email` varchar(50) NOT NULL,
 `State` varchar(50) NOT NULL,
 `Address` varchar(50) NOT NULL,
`Country` varchar(50) NOT NULL,
`Zipcode` varchar(12) NOT NULL,
 `City` varchar(50) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;


 

 

Thanks

DT107

Edited March 3, 2013 by fenway

[Barand]

What foreign key?

[DT107]

I found an article  but not sure how to apply it to my situation. Here is the article.

 

http://www.sitepoint.com/mysql-foreign-keys-quicker-database-development/

[trq]

How would we know how to apply it to your situation if you dont?

 

Describe the relationship you are looking to create.

[DT107]

I would like users to create an account and be able to post jobs on the site and also be able to edit only the jobs that

they post.

 

Thanks

DT107

[trq]

So you need to add a `member_id` column to your Jobs table.

[DT107]

can you please give an example of how to do this ?

 

Thanks

DT107

[DT107]

I have added a members colum to the jobs.

 

Here is what I have so far.

 

 

CREATE TABLE IF NOT EXISTS `Jobs` (
 
`id` int(11) NOT NULL auto_increment,
 
`Job_Title` varchar(50) NOT NULL,
 
 `Company` varchar(50) NOT NULL,
 
 `Salary` varchar(50) NOT NULL,
 
 `Description` varchar(500) NOT NULL,
 
 `Phone_Number` varchar(50) NOT NULL,
 
 `Requirements` varchar(200) NOT NULL,
 
 `Job_Category` varchar(50) NOT NULL,
 
 `Job_Type` varchar(50) NOT NULL,
 
 `Email` varchar(50) NOT NULL,
 
 `State` varchar(50) NOT NULL,
 
 `Address` varchar(50) NOT NULL,
 
`Country` varchar(50) NOT NULL,
 
`Zipcode` varchar(12) NOT NULL,
 
 `City` varchar(50) NOT NULL,
 
PRIMARY KEY  (`id`),
FOREIGN KEY (Members_ID) REFERENCES Persons(Members_ID)
 
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
 
 

 

 

Am doing it the correct way ?

 

Thanks

DT107

Edited March 3, 2013 by fenway

[trq]

can you please give an example of how to do this ?

You just linked to an article. What part of that article do you not understand in particular?

I have added a members colum to the jobs.

I don't see any column to store the member id.

 

Forget the FOREIGN KEY contraints for now, in a simple example they are not needed.

 

Just add a member_id column to your jobs table, then store the id from the member table within this member_id column. This column is then used to JOIN the tables in SELECT queries.

[DT107]

How about this I have modified the two tables.

 

Here is modified members table:

 

CREATE TABLE `members` (
`id` int(4) NOT NULL auto_increment,
`username` varchar(65) NOT NULL default '',
`password` varchar(65) NOT NULL default '',
`Members_ID` varchar(65) NOT NULL auto_increment, ''
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

 

 

 

Here is modified jobs table:

 

CREATE TABLE IF NOT EXISTS `Jobs` (

`id` int(11) NOT NULL auto_increment,

`Job_Title` varchar(50) NOT NULL,

`Company` varchar(50) NOT NULL,

`Salary` varchar(50) NOT NULL,

`Description` varchar(500) NOT NULL,

`Phone_Number` varchar(50) NOT NULL,

`Requirements` varchar(200) NOT NULL,

`Job_Category` varchar(50) NOT NULL,

`Job_Type` varchar(50) NOT NULL,

`Email` varchar(50) NOT NULL,

`State` varchar(50) NOT NULL,

`Address` varchar(50) NOT NULL,

`Country` varchar(50) NOT NULL,

`Zipcode` varchar(12) NOT NULL,

`City` varchar(50) NOT NULL,

PRIMARY KEY (`id`),
FOREIGN KEY (Members_ID) REFERENCES Members(Members_ID)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
 

Edited March 3, 2013 by fenway

[mikosiko]

Stop using the keyboard and read carefully what trq posted for you.. what you did is not even close.

 

As he recomend you forget about the foreign key constraint and focus in the data y relationships that you want to implement firts... in a side note, foreing key in MyIsam engine are not enforced at all.. so...

[DT107]

Hi Everyone,

 

Thanks for your help I found a great article that explains more easily with examples.

 

http://www.sitepoint.com/mysql-foreign-keys-quicker-database-development/

 

regards,

DT7