Hi experts I have a big problem

I have a table with multiple rows with item, price, quantity and sum now i want to save all of this into a sql database with php.

Unfortunately it only saves the last entry and not the whole table.

PHP code

<?php
    session_start();
?>
<?php
    $con= mysqli_connect('localhost','root','123456', 'orders',);
    if ($con->connect_error) {
        die("Connection failed: " . $con->connect_error);
    }
    
    for($i = 1; $i < count($_POST['item']); $i  ) {
        $username = $_SESSION['username'];
        $item = $_POST['item'][$i];
        $quantity = $_POST['quantity'][$i];
        
        $sql = "INSERT INTO riesenhuber_bestellung (orderer, item, quantity)
        VALUES ('$username', '$item', '$quantity')";
    }
    if ($con->query($sql) === TRUE) {
        header("Location: Startseite.php");
    } else {
        echo "Error: " . $sql . "<br>" . $con->error;
    }
    $con->close();
?>

HTML Cutout

<form action="order_save.php" method="POST">  
    <table class="u-table-entity" src="jquery.js">
      <script scr="calculation.js"></script>

      <colgroup>
        <col width="20%">
        <col width="2.1%">
        <col width="22%">
        <col width="21.7%">
        <col width="34.2%">
      </colgroup>
      
      <tbody class="u-table-alt-grey-5 u-table-body">
        <tr style="height: 55px;">
          <b>
          <th class="u-table-cell u-table-cell-1"><b>Produkt</b><span style="font-weight: 700;"></span>
          </th>
          <th class="u-table-cell"></th>
          <th class="u-table-cell u-table-cell-3"><b>Einzelpreis</b></th>
          <th class="u-table-cell u-table-cell-4"><b>Menge</b></th>
          <th class="u-table-cell u-table-cell-5"><b>Gesamtpreis</b></th>
          </b>
        </tr>
        <tr style="height: 55px;">
          <td class="u-table-cell">
              <input type="text" class="item" value="Kornspitz" name="item[]" placeholder="Kornspitz" readonly />
          </td>
          <td class="u-table-cell"></td>
          <td class="u-table-cell">
              <input type="text" class="price" value="1.39" name="price[]" readonly/>
          </td>
          <td class="u-table-cell">
            <input type="text" class="quantity" name="quantity[]"/>
          </td>
          <td class="u-table-cell">
            <input type="text" class="total" name="total[]" readonly/>
          </td>
        </tr>
        <tr style="height: 55px;">
          <td class="u-table-cell">
              <input type="text" class="item" value="Chillistangerl" name="item[]" placeholder="Chillistangerl" readonly />
          </td>
          <td class="u-table-cell"></td>
          <td class="u-table-cell">
              <input type="text" class="price" value="5.39" name="price[]" readonly/>
          </td>
          <td class="u-table-cell">
            <input type="text" class="quantity" name="quantity[]"/>
          </td>
          <td class="u-table-cell">
            <input type="text" class="total" name="total[]" readonly/>
          </td>
        </tr>
        <tr style="height: 55px;">
          <td class="u-table-cell">
              <input type="text" class="item" value="Semmel" name="item[]" placeholder="Semmel" readonly />
          </td>
          <td class="u-table-cell"></td>
          <td class="u-table-cell">
              <input type="text" class="price" value="7.39" name="price[]" readonly/>
          </td>
          <td class="u-table-cell">
            <input type="text" class="quantity" name="quantity[]"/>
          </td>
          <td class="u-table-cell">
            <input type="text" class="total" name="total[]" readonly/>
          </td>
        </tr>

It would be perfect if it would only enter the values where a qantity is entered. Item and price are fixed.

CodePudding user response：

Your query is being run outside the for loop, hence only the last record is getting recorded, and inserted into the database.

CodePudding user response：

if you want to run query outside loop you can use multi_query instead. for more information check this

$sql = '';
for($i = 1; $i < count($_POST['item']); $i  ) {
    $username = $_SESSION['username'];
    $item = $_POST['item'][$i];
    $quantity = $_POST['quantity'][$i];
    $sql .= "INSERT INTO riesenhuber_bestellung (orderer, item, quantity)
    VALUES ('$username', '$item', '$quantity');";
}
if ($con->multi_query($sql) === TRUE) {
    header("Location: Startseite.php");
} else {
    echo "Error: " . $sql . "<br>" . $con->error;
}
$con->close();

CodePudding user response：

To mitigate the threat from SQL injection you should really use a Prepared Statement - the beauty of this is that the SQL command is prepared once before any loops but is executed multiple times within the loop when the values of assigned/bound variables changes which makes for more efficient processing and prevents sql injection.

"It would be perfect if it would only enter the values where a qantity is entered" - simple, use isset to test whether there is a value for the quantity before running the command!

<?php

    error_reporting( E_ALL );
    session_start();

    if( $_SERVER['REQUEST_METHOD']=='POST' && isset(
        $_POST['item'],
        $_POST['quantity'],
        $_SESSION['username']
    ) && !empty( $_POST['quantity'] ) ){
    
        mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
        $con=new mysqli('localhost', 'root', '123456', 'orders');
        
        # set the sql cmd with placeholders for bound parameters
        $sql='insert into `riesenhuber_bestellung` ( `orderer`, `item`, `quantity` ) values (?,?,?);';
        
        # create the prepared statement in the db engine
        $stmt=$con->prepare( $sql );
        
        # bind the placeholders to variables that will be populated later
        $stmt->bind_param('sss',$username,$item,$qty);
        
        # assign the 1st variable to the session var
        $username=$_SESSION['username'];
        
        
        # assign other variables in the loop
        foreach( $_POST['item'] as $i => $item ){
            $qty=$_POST['quantity'][ $i ];
            
            # execute the prepared statement
            $stmt->execute();
        }
        
        $stmt->close();
        $con->close();
        
        exit( header('Location: Startseite.php') );
    }
?>