same id in two tables...

torva

Hello!
I've got two tables. Let's say its called Person(person_id, name, address) and Sports(sports_id, person_id). The person_id is auto_increment in the Person table, but not in the Sports table. My problem is that I want the the person_id to be the same in the two tables. The problem is that I do not know what it will be in the Person table, since it is auto_increment.

$result = mysql_query("INSERT INTO Person values(0000, 'John', 'Whatever')",$db);

Now I need the person_id to put it into the Sports table.

$result = mysql_query("INSERT INTO Sports values(0000, person_id)",$db);

How can one solve this problem?
T.

laserlight

Look into [man]mysql_insert_id/man

Sxooter

What you're really looking for here is a foreign key, most likely. I'm assuming here that every entry in sports should have a match in thepersons table, so the foreign key for sports will be persons.

Can you use innodb tables here (or a database with fk support failing that?)

scraff

Hey,

Try:

$query = "INSERT INTO Person  (productcode, oprice) VALUES ('$name', '$Whatever')";
$result = mysql_query($query);
///OR $result = mysql_query("INSERT INTO Person values(0000, 'John', 'Whatever')",$db); 

$query = mysql_query("SELECT * FROM Person WHERE name= '$name' AND address= '$address' ORDER BY $person_id DESC");
//This SHOULD get the highest ID (if its auto increase will be the last person in the database) and to be extra safe get the highest ID with the persons name and address etc.
$person_id = mysql_result($query,0,"person_id");

$query = "INSERT INTO Sports (sports_id, person_id) VALUES ('$sports_id', '$person_id')";

$result = mysql_query($query);

And if u want to pull the data from the 2 tables look at this post:

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10285888

Hope it helps 🙂