Saving multiple form data to mysql using php

Question

I have a table form which has a add new row button which upon clicked adds a new row to the table. I am trying to save all the rows in MySQL on clicking save button.

The code I wrote saves only one row no matter how many row I add. Could someone please tell my what am I doing wrong.

I searched Google but couldn't get anywhere.

Here are my codes:

save.php

<?php
include('connection.php');

if(isset($_POST['submit'])){

    $row_data = array();
    foreach($_POST['category'] as $row=>$category){
        $category = mysql_real_escape_string($category);
        $itemName = mysql_real_escape_string($_POST['itemName'][$row]);
        $brand = mysql_real_escape_string($_POST['brand'][$row]);
        $model = mysql_real_escape_string($_POST['model'][$row]);
        $sellingPrice = mysql_real_escape_string($_POST['sellingPrice'][$row]);

        $row_data[] = "('$category','$itemName','$brand','$model','$sellingPrice')";
    }
}
if(!empty($row_data)){
    $insert_query = mysql_query("INSERT INTO sale(Category,ItemName,Brand,Model,SellingPrice) VALUES".implode(',', $row_data));

    if(!$insert_query){
        echo "Error: " . mysql_error();
    }else{
        echo "Data Saved Successfully";
    }
}
?>

and this is my html form

<form name="form1" id="myForm" action="saveSale.php" method="post">
                    <tr class="cloneme">
                        <td><input type="text" name="category[]"></td>
                        <td><input type="text" name="itemName[]"></td>
                        <td><input type="text" name="brand[]"></td>
                        <td><input type="text" name="model[]"></td>
                        <td><input type="text" name="sellingPrice[]"></td>
                    </tr>
                </tbody>
            </table>
        </div>
        <div class="eventButtons">
            <input type="submit" name="submit" id="submit" value="Save">
            <input type="reset" name="reset" id="reset" value="Clear"  class="btn">
        </div>
    </form>

Answer 1

You need to run your query in for loop by counting the array value using count($_POST['category'])

if(isset($_POST['submit'])){

    $row_data = array();
    for($i= 0; $i <count($_POST['category']);$i++){
        $category = mysql_real_escape_string($_POST[$i]['category']);
        $itemName = mysql_real_escape_string($_POST[$i]['itemName']);
        $brand = mysql_real_escape_string($_POST[$i]['brand']);
        $model = mysql_real_escape_string($_POST[$i]['model']);
        $sellingPrice = mysql_real_escape_string($_POST[$i]['sellingPrice']);
        $insert_query = mysql_query("INSERT INTO sale(Category,ItemName,Brand,Model,SellingPrice) VALUES ('$category','$itemName','$brand','$model','$sellingPrice')"); 
        if(!$insert_query){
            echo "Error: " . mysql_error();
        }else{
           echo "Data Saved Successfully";
        }
    }
}

Answer 2

As I dont have enough reputation I am adding my comment as answer.

Your code is fine. It should work. There might be problem while you are cloning the row, may be it is not getting added under the form tag. You can verify it by dumping the $row_data variable.

Please share your javascript code which makes clone of the row, it will help us to solve your problem.

Answer 3

You are inserting data outside the for loop so it inserts only the last row or data.. What you have to do is to place insert query within foreach or for loop

if(isset($_POST['submit'])){

    $row_data = array();
    for($i = 0 ; $i < count($_POST['category']);$i++){
        $category = mysql_real_escape_string($_POST[$i]['category']);
        $itemName = mysql_real_escape_string($_POST[$i]['itemName']);
        $brand = mysql_real_escape_string($_POST[$i]['brand']);
        $model = mysql_real_escape_string($_POST[$i]['model']);
        $sellingPrice = mysql_real_escape_string($_POST[$i]['sellingPrice']);
        $insert_query = mysql_query("INSERT INTO sale(Category,ItemName,Brand,Model,SellingPrice) VALUES ('$category','$itemName','$brand','$model','$sellingPrice')"); 
        if(!$insert_query){
            echo "Error: " . mysql_error();
        }else{
           echo "Data Saved Successfully";
        }
    }
}