如何将多行插入数据库PHP MYSQL

Question

i need help for my Invoice input

I need some help for inserting multipe rows with different values to database using php. i already have button to insert new row

here is my insert query :

  $insert     = "INSERT INTO subtrans (idproduk,namaproduk,harga,qty,sub) 
  VALUES";
      for ($i=0; $i < $count; $i++) {
        $insert .= "('{$idproduk[$i]}','{$namaproduk[$i]}','{$harga[$i]}','{$qty[$i]}','{$sub[$i]}')";
        $insert .= ",";
      }
    $insert = rtrim($insert,",");
    $sql = $con->query($insert);

heres my HTML code :

<tbody>
                  <tr>
                    <td><input class="case" type="checkbox"/></td>
                    <td><input type="text" data-type="idproduk" name="itemNo[]" id="itemNo_1" class="form-control form-control-sm autocomplete_txt" autocomplete="off"></td>
                    <td><input type="text" data-type="namaproduk" name="itemName[]" id="itemName_1" class="form-control form-control-sm autocomplete_txt" autocomplete="off"></td>
                    <td><input type="number" name="price[]" id="price_1" class="form-control form-control-sm changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>
                    <td><input type="number" name="quantity[]" id="quantity_1" class="form-control form-control-sm changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>
                    <td><input type="number" name="total[]" id="total_1" class="form-control form-control-sm totalLinePrice" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>
                  </tr>
                </tbody>

And here is my js code :

//adds extra table rows
var i=$('table tr').length;
$(".addmore").on('click',function(){
 html = '<tr>';
 html += '<td><input class="case" type="checkbox"/></td>';
 html += '<td><input type="text" data-type="idproduk" name="itemNo[]" id="itemNo_'+i+'" class="form-control form-control-sm autocomplete_txt" autocomplete="off"></td>';
 html += '<td><input type="text" data-type="namaproduk" name="itemName[]" id="itemName_'+i+'" class="form-control form-control-sm autocomplete_txt" autocomplete="off"></td>';
 html += '<td><input type="text" name="price[]" id="price_'+i+'" class="form-control form-control-sm changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;"onpaste="return false;"></td>';
 html += '<td><input type="text" name="quantity[]" id="quantity_'+i+'" class="form-control form-control-sm changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;"  onpaste="return false;"></td>';
 html += '<td><input type="text" name="total[]" id="total_'+i+'" class="form-control form-control-sm totalLinePrice" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;"  onpaste="return false;"></td>';
 html += '</tr>';
 $('table').append(html);
 i++;
 });

Answer 1

Just for the record I do not believe this to be the best way to approach this particular problem - the better way would be to use a prepared statement which is prepared once and then executed multiple times in the loop using different values each time. However, the following generates viable sql ( did in testing anyway )

$insert = "insert into `subtrans` ( `idproduk`, `namaproduk`, `harga`, `qty`, `sub` ) values ";
$params = array();

foreach( $_POST['itemName'] as $i => $value ){
    $params[]="'". implode( "','", 
        array( 
                $_POST['itemNo'][ $i ],
                $_POST['itemName'][ $i ],
                $_POST['price'][ $i ],
                $_POST['quantity'][ $i ],
                $_POST['total'][ $i ]
            )
        ) ."'";
}
$sql = sprintf("%s ( %s )", $insert, implode( '),(', $params ) );

echo $sql;
$result = $con->query( $sql );

The better method to use would be, like I mentioned, a prepared statement executed multiple times.

$sql='insert into `subtrans` ( `idproduk`, `namaproduk`, `harga`, `qty`, `sub` ) values (?,?,?,?,?)';
$stmt=$con->prepare( $sql );
if( $stmt ){
    $stmt->bind_param('ssddd',$id,$name,$price,$qty,$total);

    foreach( $_POST['itemName'] as $i => $value ){
        $id=$_POST['itemNo'][ $i ];
        $name=$_POST['itemName'][ $i ];
        $price=$_POST['price'][ $i ];
        $qty=$_POST['quantity'][ $i ];
        $total=$_POST['total'][ $i ];

        $stmt->execute()
    }
}