foreign key values

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

foreign key values

Post 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.");
Last edited by Benjamin on Thu Jan 13, 2011 3:05 am, edited 1 time in total.
Reason: Added [syntax=php] tags.
kalpesh.mahida
Forum Commoner
Posts: 36
Joined: Wed Oct 06, 2010 7:09 am

Re: foreign key values

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: foreign key values

Post 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)
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply