Page 1 of 1

foreign key values

Posted: Wed Jan 12, 2011 2:04 am
by drayarms
If the primary key of an sql table is being used as a foreign key of another table, when entering the values of the columns of the second table in an insert query, what would the value of the foreign key be. Say for example we had a table called members whose primary key (member_id) is a foreign key column of another table blogs. If we wrote a query to insert values into the blogs table like the one below, how do we account for the member_id column?

Code: Select all

 $query = "INSERT INTO blogs (blog_id, title, entry, blog_date) VALUES (0, '{$_POST['title']}', '{$_POST['entry']}', NOW())";



Just in case it might be helpful, here is the query for inserting values into the members table:

Code: Select all

$query = mysql_query("INSERT INTO members 
	(id, username, firstname, lastname, password, date, ip)
	VALUES	(0, '$_POST[username]','$_POST[firstname]','$_POST[lastname]','$_POST[password]','$date','$ip')")
	or die ("Error - Couldn't register user.");

Re: foreign key values

Posted: Wed Jan 12, 2011 2:28 am
by kalpesh.mahida
who can create a blog? a member right? if a member if going to create a blog you can have reference to your member id field that you can insert into blogs table

Re: foreign key values

Posted: Fri Jan 14, 2011 10:47 am
by social_experiment
drayarms wrote:If the primary key of an sql table is being used as a foreign key of another table, when entering the values of the columns of the second table in an insert query, what would the value of the foreign key be.
The value should be the same or the records cannot be linked together, that is why you use a foreign key.
drayarms wrote:...how do we account for the member_id column?
If you don't auto_increment your primary key, you can use a custom number. Maybe count the number of rows from the blog table then use that value

Code: Select all

<?php
$queryC = "SELECT * FROM blogs";
$sql = mysql_query($queryC);
$rows = mysql_num_rows($sql);

$id = $rows + 1;

$query1 = "INSERT INTO blogs (blog_id, title, entry, blog_date) VALUES ('$id', '{$_POST['title']}', '{$_POST['entry']}', NOW())";
 // and
$query2 = mysql_query("INSERT INTO members 
        (id, username, firstname, lastname, password, date, ip)
        VALUES  ('$id', '$_POST[username]','$_POST[firstname]','$_POST[lastname]','$_POST[password]','$date','$ip')")
        or die ("Error - Couldn't register user.");
?>
Both tables contain the same value for primary key (blog_id) and foreign key (id)