php turn array in to mysql queries

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?

Answers


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);

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), ...

if (count($roles)) {
   $qry="INSERT INTO roleid (userid, roleid) VALUES ";
   $part="($user_insert_id, ";
   $qry . = $part . implode(")," . $part, roles) . ")";
   mysql_query($ary, $db_handle);
}

Need Your Help

How to format decimals

ruby-on-rails ruby

I need to format float like price for two decimal places and thousands spaces, like this:

unable to download mojarra for jsf

jsf-2 mojarra

Earlier I was also getting an empty library