PHP Form $_POST foreach stops after first iteration

Question

I have the below PHP code which picks up the posted form data from another file, executes SELECT queries to find all related data form the various tables (SalesDB, CustDB and ProdDB), and then executes an INSERT INTO query to add a row into the 'SalesDB' table. The form has dynamically added rows, which gives each newly added row a unique ID, for example:

...<input type="text" id="prodName_1" name="prodName[]" value="">
...<input type="text" id="prodName_2" name="prodName[]" value="">
.  
.  
...<input type="text" id="prodName_Z" name="prodName[]" value="">

However, when the PHP script runs for e.g. 3 rows of product lines, it only executes the $queryinsert query for the first iteration and inserts the first product line of the form. Why won't it loop through the array? See the php script below:

<?php

$db = new SQLite3('../xxx.db');
if(!$db){
  echo $db->lastErrorMsg();
  exit;
}

if (empty($_POST['custID'])) {
  $errorMSG = array("No customer selected");
  echo json_encode($errorMSG, JSON_PRETTY_PRINT);
  exit;
} else {

  $custID = $_POST['custID'];

  $queryInsert = $db->prepare("INSERT INTO 'SalesDB'
  (SalesID,CustID,ProdID,ProdQty,ProdPrice,ProdCurr,ProdVAT,SalesPrice,SalesVAT,SalesSum)
  VALUES (?,?,?,?,?,?,?,?,?,?)");
  $queryInsert->bindParam(1,$salesID);
  $queryInsert->bindParam(2,$custID);
  $queryInsert->bindParam(3,$prodID);
  $queryInsert->bindParam(4,$prodQty);
  $queryInsert->bindParam(5,$prodPrice);
  $queryInsert->bindParam(6,$prodCurr);
  $queryInsert->bindParam(7,$prodVAT);
  $queryInsert->bindParam(8,$salesPrice);
  $queryInsert->bindParam(9,$salesVAT);
  $queryInsert->bindParam(10,$salesSum);

  $querySalesID = "SELECT MAX(SalesID) AS max_SalesID FROM 'SalesDB'";
  $resultSalesID = $db->query($querySalesID);

  while ($row = $resultSalesID->fetchArray()) {
    $salesID = $row['max_SalesID'] + 1;
  }

  foreach($_POST['prodName'] as $prodName => $value) {
    if (!$value) {
      $errorMSG = array("Empty product fields");
      echo json_encode($errorMSG, JSON_PRETTY_PRINT);
      exit;
    } elseif ($value == "Product not found") {
      $errorMSG = array("Invalid products in order form");
      echo json_encode($errorMSG, JSON_PRETTY_PRINT);
      exit;
    }

    $queryProd = "SELECT * FROM `ProdDB` WHERE ProdName LIKE '%$value%'";
    $resultProd = $db->query($queryProd);

    while ($row = $resultProd->fetchArray()) {
      $prodID = $row['ProdID'];
      $prodPrice = $row['ProdPrice'];
      $prodQty = $row['ProdQty'];
      $prodVAT = $row['ProdVAT'];
      $prodCurr = $row['ProdCurr'];
      $salesPrice = $prodQty * $prodPrice;
      $salesVAT = number_format($prodQty * $prodPrice * $prodVAT,2);
      $salesSum = $salesPrice + $salesVAT;
    }
    $result = $queryInsert->execute();
  }
}
?>

Please also note that I am aware that I am (most likely) making a lot of mistakes when it comes to security practices or programming standards, but this whole thing (PHPDesktop > https://github.com/cztomczak/phpdesktop) will get packed into an EXE file which will run locally only (no need for an online connection as the SQLite3 DB gets packed in with the EXE), and I am still figuring out how to program this in the first place, so efficient and tidy coding are not high on my list yet ;-)

Answer 1

There are some issues in the script:

1) Instead of doing exit inside the foreach, do continue to skip the single actual iteration.

As in the official documentation:

continue is used within looping structures to skip the rest of the current loop iteration and continue execution at the condition evaluation and then the beginning of the next iteration.

Try this code:

  foreach($_POST['prodName'] as $prodName => $value) {
    if (!$value) {
      $errorMSG = array("Empty product fields");
      echo json_encode($errorMSG, JSON_PRETTY_PRINT);
      continue;
    } elseif ($value == "Product not found") {
      $errorMSG = array("Invalid products in order form");
      echo json_encode($errorMSG, JSON_PRETTY_PRINT);
      continue;
    }

    $queryProd = "SELECT * FROM `ProdDB` WHERE ProdName LIKE '%$value%'";
    $resultProd = $db->query($queryProd);

    while ($row = $resultProd->fetchArray()) {
      $prodID = $row['ProdID'];
      $prodPrice = $row['ProdPrice'];
      $prodQty = $row['ProdQty'];
      $prodVAT = $row['ProdVAT'];
      $prodCurr = $row['ProdCurr'];
      $salesPrice = $prodQty * $prodPrice;
      $salesVAT = number_format($prodQty * $prodPrice * $prodVAT,2);
      $salesSum = $salesPrice + $salesVAT;
    }
    $result = $queryInsert->execute();
  }

2) your query are using user inputs without check their contents, so your script maybe open to SQLInjection!

$queryProd = "SELECT * FROM `ProdDB` WHERE ProdName LIKE '%$value%'";

3) if the query does return nothing, the script does not enter in the while loop, so it seems that the foreach do only one iteration but instead it do all iterations without enter in the while because of empty result from that query.

I suggest to you to debug all pieces of your code by printing out variables content using var_dump, e.g.:

$a = array(1, 2, array("a", "b", "c"));
var_dump($a);