Inserting multiple input values as different rows in a different table

Question

I am working on a single product page. There are colors table in the database to define colors of each product. It looks like this;

| key | product_id | color |
+-----+------------+-------+
|   1 |          4 | black |
|   2 |          7 | red   |
|   3 |          8 | blue  |
|   4 |          8 | black |
+-----+------------+-------+

I insert the product via productController.php. No problem with this. Moreover, I want to insert colors to their own table with the same auto increment id. As you can imagine, there are multiple inputs for each color in the form. What is the right method and which functions should I use?

A simplified view of the form is below.

<form>
  <div class="control-group">
    <label class="control-label">Product Name</label>
    <div class="controls">
      <input type="text" name="productname" />
    </div>
  </div>
  <div class="control-group">
    <label class="control-label">Colors</label>
    <div class="controls" id="color">
      <input type="text" name="colors[]" />
      <a href="javascript:void(0)" class="addcolor"> + Add</a>
    </div>
  </div>
  <div class="form-actions">
    <button type="submit" class="btn btn-success">Add Product</button>
  </div>
</form>

user3526204 · Accepted Answer · 2017-01-07 12:10:42Z

1

Try this...

After INSERTING into products table, get the last insert id

$last_id = $db->insert_id;

$sql="";
$colours=$_POST['colours'];
foreach($colours as $colour)
{
    $sql.="INSERT INTO colours_table (product_id,color) values ('$last_id','$color');"
}

$db->query($sql);

Of course take all precautions against sql injection

edited Jan 7, 2017 at 12:10

answered Jan 7, 2017 at 12:03

user3526204

5095 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jaymin Panchal · Accepted Answer · 2017-01-07 12:34:05Z

0

Normalize your DB structure like

color_master

| colorId | colorName |
+---------+-----------+
|     1   |   black   |
|     2   |   red     |
|     3   |   blue    |
+---------+-----------+

product_color

| product_color_id | product_id | color_id |
+------------------+------------+----------+
|        1         |     4      |    1     |
|        2         |     7      |    2     |
|        3         |     8      |    3     |
|        4         |     4      |    3     |
+------------------+------------+----------+

I suggest you to use <select> with multiple selection option.

 <select name="color[]" multiple></select>

at the PHP end you will get the Color array in post and you can then insert in your database

edited Jan 7, 2017 at 12:34

answered Jan 7, 2017 at 12:09

Jaymin Panchal

2,8642 gold badges29 silver badges32 bronze badges

1 Comment

Melih Over a year ago

Thanks for the advice! I should use text inputs as there will be unlimited number of colors. Actually color is just an example among a lot of inputs.

Collectives™ on Stack Overflow

Inserting multiple input values as different rows in a different table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related