1

I am new with PHP and MySQL and need help...

Im cant seem to get my head around how to automatically insert a foreign key into a table in my database. The primary key gets added as 'id' in my 'mem' table but I want this same id to be added to a foreign key in my 'location' table. Can anyone please help?

I have two tables...

'mem table' id, username, email.

  |    1    |    2    |    3    |
  |   paul  |   john  | francis |       
  | paul@gm.| john@gm.|francis@.|

'location table' id, location, user_id << forign key

  |    1    |    2    |    3    |
  |  leeds  |liverpool| london  |       
  |    ?    |    ?    |    ?    |

how do I get the 'id' in the user table to automatically get inserted here as a foreign key?

Im using this php code to insert the data...

$sql = mysql_query("INSERT INTO mem (username, country, state, city, accounttype, email, password, signupdate) 
        VALUES('$username','$country','$state','$city','$accounttype','$email','$hashedPass', now())")      or die(mysql_error());
$id = mysql_insert_id();

$sql = mysql_query("INSERT INTO location (location) 
VALUES('$location')") or die(mysql_error());
$user_id = mysql_insert_id();

Im getting this error when I submit the form... Cannot add or update a child row: a foreign key constraint fails (mem.location, CONSTRAINT location_ibfk_1 FOREIGN KEY (user_id) REFERENCES location (id) ON DELETE CASCADE ON UPDATE CASCADE)

Can anyone help?

Raidri
  • 17,258
  • 9
  • 62
  • 65
user2003341
  • 73
  • 1
  • 9

1 Answers1

1

your FK is in the wrong direction. In the moment it says: "Every entry in user table MUST have a entry in location table." You try to insert in the user table, without creating a location entry first. So the FK fails.

solution: Set the FK on the location table "user_id -> users.user_id" so it means "every user_id on the location table should have an entry in the users table.". The only Problem is: the "ON DELETE CASCADE" is no longer working, when you delete a entry in the users table... - This can be solved with a database trigger (PSEUDOCODE) "BEFORE delete FROM users DELETE FROM locations WHERE user_id" or you just do 2 queries in PHP. (which is the easier solution. 1st delete location row, 2nd the user row.)

waflija
  • 177
  • 8
  • Thanks for your answer but can I do this using the php code or do I use a sql query? – user2003341 Jan 23 '13 at 10:34
  • this is a database Problem. You have to change on the database. Use: http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html OR: PHP-solution: mysql_query('DELETE FROM location WHERE user_id ='. $user_id); mysql_query('DELETE FROM user WHERE user_id = '. $user_id); – waflija Jan 23 '13 at 10:46
  • Im not as concerned about that just yet, im still stuck on setting the foreign key.. how do I set it as users.user_id? – user2003341 Jan 23 '13 at 10:57
  • do you use phpMyAdmin or MySQL Workbench? that makes it a lot easier. Otherwise you have to use this: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html – waflija Jan 23 '13 at 12:25
  • Im currently using phpMyAdmin, still very confused! – user2003341 Jan 24 '13 at 23:27
  • navigate in PHP my ad to the "structure" tab of "location" table. Click on "connection / foreign key." left of the "print" button. (sorry i have the german version so I can't check the correct english) now you see a list with the field of the current table and a dropdown with fields of other tables. - This means the field of the current table could ONLY contain values of the referenced field. – waflija Jan 27 '13 at 12:47
  • a short example of FKs: Table "user" contains ids "1, 2, 3, 4, 5". You have a FK from locations.id referencing "user.id" - So inserting a row with id "3" would be fine. Inserting onw with id "6" would result in an FK error. In phpmyadmin this is easy to check: click the "insert" button. Fields with an FK will automagicly show a dropdown with possible values from the referenced table. – waflija Jan 27 '13 at 12:51