2. PHPRunner General questions
  3. [SOLVED]  Insert into 2 relational tables in one form submission
This topic is locked
[SOLVED]

 Insert into 2 relational tables in one form submission

 		10/31/2012 2:06:57 PM
PHPRunner General questions
khalil author

Hello,
I have 3 tables, "users", "users_intersts", and "interests", users_interests contains the user_id and the interest_id (a typical many to many relationship situation)
I want to have the "add user" page adding info to both users and users_interests table at the same time, I can do this if I'm hard coding easily, but I'm trying to get the way PHPRunner works, the trick is to process the form submission as follow:

  • add the user and get the inserted user ID using mysql_insert_id
  • add the proper selected values from the form to the relational table users_interests along with the value of mysql_insert_id
    How can I establish that in PHPRunner?
C
 		cgphp 10/31/2012

You can get rid of the mysql_insert_id function. Follow these steps:

  1. I'm assuming the interest_id is an alias for the user table not a field (it is a lookup for the interest table). In the "Before record added" event of the user table enter the following code:

$_SESSION['interest_id'] = $values['interest_id'];

unset($values['interest_id']);

return true;


2. In the "After record added" event of the user table the $keys array points to the key column of the new added record:



global $conn;

db_exec("INSERT INTO users_interests (user_id, interest_id) VALUES (".$keys['user_id'].",".$_SESSION['interest_id'].")",$conn);

unset($_SESSION['interest_id']);
khalil author 10/31/2012



You can get rid of the mysql_insert_id function. Follow these steps:

  1. I'm assuming the interest_id is an alias for the user table not a field (it is a lookup for the interest table). In the "Before record added" event of the user table enter the following code:

$_SESSION['interest_id'] = $values['interest_id'];

unset($values['interest_id']);

return true;


2. In the "After record added" event of the user table the $keys array points to the key column of the new added record:



global $conn;

db_exec("INSERT INTO users_interests (user_id, interest_id) VALUES (".$keys['user_id'].",".$_SESSION['interest_id'].")",$conn);

unset($_SESSION['interest_id']);



That's perfect Cristian! exactly what I needed!
Thank you!