How to insert multiple rows in mysql database using php?

Hi,
Can any one help me on how to insert multiple rows in mysql database using php,
Below is how my code looks

form.php

<?php include ('control/function.php'); ?>
<HTML>
<HEAD>
    <TITLE> Add/Remove dynamic rows in HTML table </TITLE>
    <SCRIPT language="javascript">
        function addRow(tableID) {

            var table = document.getElementById(tableID);

            var rowCount = table.rows.length;
            var row = table.insertRow(rowCount);

            var colCount = table.rows[0].cells.length;

            for(var i=0; i<colCount; i++) {

                var newcell    = row.insertCell(i);

                newcell.innerHTML = table.rows[0].cells[i].innerHTML;
                //alert(newcell.childNodes);
                switch(newcell.childNodes[0].type) {
                    case "text":
                            newcell.childNodes[0].value = "";
                            break;
                    case "checkbox":
                            newcell.childNodes[0].checked = false;
                            break;
                    case "select-one":
                            newcell.childNodes[0].selectedIndex = 0;
                            break;
                }
            }
        }

        function deleteRow(tableID) {
            try {
            var table = document.getElementById(tableID);
            var rowCount = table.rows.length;

            for(var i=0; i<rowCount; i++) {
                var row = table.rows[i];
                var chkbox = row.cells[0].childNodes[0];
                if(null != chkbox && true == chkbox.checked) {
                    if(rowCount <= 1) {
                        alert("Cannot delete all the rows.");
                        break;
                    }
                    table.deleteRow(i);
                    rowCount--;
                    i--;
                }


            }
            }catch(e) {
                alert(e);
            }
        }

    </SCRIPT>
</HEAD>
<BODY>
<div class="container-fluid">
  <INPUT type="button" value="Add Row" onclick="addRow('dataTable')" />

  <INPUT type="button" value="Delete Row" onclick="deleteRow('dataTable')" />
    <form action="" method="POST">

        

  <TABLE id="dataTable" width="350px" border="1">
    <TR>
      <TD><INPUT type="checkbox" name="chk[]"/></TD>
         
      <TD>
         <select name="name[]">
            <option>--Select StockName---</option>
            <?php Select Name(); ?>
        </select>
      </TD>
      
       <TD><label>Quantity</label><input type="number" name="amount[]"></TD>
      <TD><label for="wholesale">Wholesale Price:</label>     
        <input type="text" name="wholesale"  required="text"></TD>
       
         <TD><label>Retail Price</label><input type="text" name="retail[]"></TD>
         <TD>
        <select name="username[]">
            <option>--Select Servedby--</option>
                        <?php servedby(); ?>
            </select>
      </TD>
    </TR>
  </TABLE>
  <button type="submit" name="submit">Sale</button>
    </form>

        <?php save(); ?>
            </div>
</BODY>
</HTML>

function.php

<?php
function save(){
    include ('connect.php');

        if (isset($_POST['submit'])) {
            $name=$_POST['name'];
            $quantity=$_POST['amount'];
            $wholesale=$_POST['wholesale'];
            $retail=$_POST['retail'];
            $username=$_POST['username'];

$select_data="select * from drugs where name='$name'";
                $run_data=mysql_query($select_data);
         $row=mysql_fetch_array($run_data);
            $newname=$row['name'];

            $select_username="select * from pharmacist where username='$username'";
    $run_username=mysql_query($select_username);
    $row=mysql_fetch_array($run_username);
    $servedby=$row['username'];

        $insert="insert into amount_required (name,quantity,wholesale,retail,tarehe,servedby)
 values('$newname','$quantity','$wholesale','$retail','$username',NOW(),'$servedby')";
        $run_insert=mysql_query($insert);

        if ($run_insert) {
            echo "<script>alert('Item Entered Successful')</script>";
            echo "<script>window.open('submit.php','_self')</script>";
}
}
?>
1 Like

And what is the problem with your code?
Apart from the fact that you shouldn’t be using the deprecated mysql_ commands?

@DarthGuido
The problem is,it only insert one row only if you put data in more than one row.

You only run one insert query, how do you expect it to insert more than one row with a insert single query?
If you have arrays of form data, you must iterate through your array.

But in all honesty, the whole system looks flawed and in need of a complete rethink.
It’s outdated, mysql isn’t just deprecated, it is completely obsolete and no longer a part of PHP, is should not still be used.
It’s insecure and wide open to SQL injection by passing unsanitised user data directly into your queries.
It’s sub-optimal, using * when you only want one column from a table.
In fact I don’t even understand why you have those two SELECT queries. You appear to be selecting data that you already have:-
If the query says: where name='$name'
Then you say: $newname=$row['name'];
$newname will be exactly the same as $name
So what is the point of running that query?
The exact same applies to the second SELECT query where you select the username column that equals a value you already have. :confused:
It would make sense if you were selecting IDs from the other tables and saving them as foreign keys in the other table. But even then, because the inputs are <select> the ID could be the value in the first place, negating the need for extra queries.

Your best bet would be to use PDO to prepare an insert query once, then iterate through your array of form data, executing the query with data from each row. That would be both more secure and efficient.

2 Likes

@SamA74

Thanks, I will use PDO.

2 Likes

There are examples of inserting multiple rows in this topic:-

1 Like

Did you got the answer. I’m also have the same problem like you. Please post it if you have

You might be better starting a new topic and posting your code so that people can have a look at what’s wrong with it, rather than re-activating a post from two months ago.

2 Likes

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.