insert values from checkbox in table using php + mysqli

Question

Hello! I have 3 tables (actors, categories, actor_cats) in my db.

I'm using a form to insert new info in the database (mysql). Most of the information goes into the actors table. But in a label "categories", i have a checkbox input type, with 3 fields that i get from the categories table [action (id1), comedy(id2), drama(id3)]. I want to insert this information in my actor_cats table. What i want to do is create a new actor in actors table, and also insert the checkbox selection (might be all 3 options) in the actors_cats table. This table as the actors id and the categories id, so what i want would be something like this:

Table actor_cats

row 1 | actorid (1) | categoriesid (1)

row 2 | actorid (1) | categoriesid (2)

as an example...

how can i achieve this using mysqli?

thanks in advance!

I still can't write to the actor_cats table. This is what i'm doing:

//After inserting the new actor

$jid = $mysql->insert_id;

if (isset($_POST['cats'])) {
$cats = $_POST['cats'];
} else {
$cats = array();
}   
$numCats = 0;
foreach ($cats as $catID) {

$cats_sql = "INSERT INTO actor_cats VALUES(?,?)";    
$stmt = $mysql->stmt_init();

if ($stmt->prepare($cats_sql)) {
$stmt->bind_param('ii', $jid, $catID);
$stmt->execute();

    if ($ok) {
      $numCats = $numCats + 1;
    } else {
      echo "<p>Error inserting $catID: " .
          $mysql->error . '</p>';

    } 
  }

<?php
else:
$cats = 'SELECT id, type FROM categories';
$stmt = $mysql->prepare($cats);
$stmt->execute();
$stmt->bind_result($catid,$cattype);

?>

And in my form in the checkbox area, i fetch my categories table and get the values, so when i submit the form, my selection would be inserted

 //excerpt from form
 <label>Category:</label><br />
 <?php while($row = $stmt->fetch()) :  ?> 
  <input type="checkbox" name="cats" value="<?php echo $catid; ?>" /><?php echo $cattype;   ?>
 <br /><br />   <?php  endwhile; ?>

Hope this is clear! Thanks all the guys for all the help!

Answer 1

You give the checkboxes all the same name attribute, and set the value attribute to the id of the category. Then you insert the actor and get its id with $mysqli->insert_id. Then you execute another query to insert a row in the actor_cats table with the actor's id and the category's id.

Answer 2

$mysqli = new mysqli("host", "user", "password", "db");

$mysqli->query("INSERT INTO actors ..."); // your query to insert new actor

$id = $mysqli->insert_id;

// replace 1 with the category id
$mysqli->query("INSERT INTO actor_cats (actor_id, cat_id) VALUES ($id, 1)");