up vote -1 down vote favorite
share [g+] share [fb]

I have a form which is submitted, where a user is created and can be assigned a number of roles: for example (Admin, User etc.)

The form posts the data as an array roles[].

When only one role was selectable I had one mysqli_query() create the user (in the users table). Then the second create a row in the roleid table which matched up a userid with a roleid using mysqls LAST_INSERT_ID, will the mysql LAST_INSERT_ID work if there is more than one role assigned to the user?

The three tables are as follows:

users (name, email, password, id)
roleid (userid, roleid)
roles(id, description)

I need to create an entry in the roleid table for each checkbox that is selected by the user creation form. I thought about imploding the array (implode(',',$roles)) but im not sure mysql accepts values in that form. Any ideas?

link|improve this question
1  
Save the LAST_INSERT_ID in a PHP variable. Then use the variable in your queries. – Rocket 2 days ago
1  
You can do INSERT INTO table(field1,field2,field3) VALUES(1,2,3),(4,5,6),(7,8,9). – Rocket 2 days ago
ok so ive got php to construct the sql statement as INSERT INTO roleid (userid, roleid) VALUES(LAST_INSERT_ID(),'1') (LAST_INSERT_ID(),'2') will LAST_INSERT_ID() work if its called twice in the same statement? – user1093553 2 days ago
Not sure. Try it :-P – Rocket 2 days ago
feedback

3 Answers

up vote 2 down vote accepted

You will have to either query once per role, or insert multiple records in one query. With MySQLi you could even use prepared statements for this.

$user_id = mysqli_insert_id();
$query = "INSERT INTO `roleid` (userid, roleid) VALUES ";

foreach($roles as $index => $role_id) {
  $roles[$index] = "($user_id," . intval($role_id) . ")";
}

$query .= implode(', ', $roles);
link|improve this answer
feedback
up vote 1 down vote

If You once insert a user into the users table and save that LAST_INSERT_ID into a variable, e.g. $last_insert_id You can then perform as many queries using this $last_insert_id as You want (while it is not overwrited :-) ).

So:

  1. insert new user
  2. retrieve $last_insert_id
  3. for each role ID run insert into roleid values ($role_id, $last_insert_id)
    OR
    run one insert into roleid values ($role_id_1, $last_insert_id), ($role_id_2, $last_insert_id), ($role_id_3, $last_insert_id), ...
link|improve this answer
feedback
up vote 1 down vote 
if (count($roles)) {
   $qry="INSERT INTO roleid (userid, roleid) VALUES ";
   $part="($user_insert_id, ";
   $qry . = $part . implode(")," . $part, roles) . ")";
   mysql_query($ary, $db_handle);
}
link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.