I'm trying to update a database on one submit button. The code seem to work and see the changes but giving an error of (You have an error in your SQL syntax...use near '1' at line 1) I have searched this site and others all night for the same error but still can't fix it. What is causing this error and how can I fix it? Please help!

<?php
require_once 'config.php';

// Create connection
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$sql = "UPDATE Bank SET

Quantity=?, 
Category =?, 
Item=?, 
UnitPrice=?, 
TOTAL=?, 
MiscMat=?, 
LaborHours=?, 
LaborRate=? 

WHERE QID=?";

$stmt = $conn->prepare($sql);

$stmt->bind_param('ssssssssd',
 
$db02, 
$db03, 
$db04, 
$db05, 
$db06, 
$db07, 
$db08, 
$db09, 
$db18);
// set parameters and execute

        $db02 = $_POST['inp02']; // Quantity 
        $db03 = $_POST['inp03']; // Category 
        $db04 = $_POST['inp04']; // Item        
        $db05 = $_POST['inp05']; // UnitPrice 
        $db06 = $_POST['inp06']; // TOTAL       
        $db07 = $_POST['inp07']; // MiscMat     
        $db08 = $_POST['inp08']; // LaborHours
        $db09 = $_POST['inp09']; // LaborRate
        $db18 = $_POST['inp18']; // QID 

$stmt->execute();

$i = 0;
$count = count($db02);

for($i=0; $i < $count; $i  ){
    $currentQty      = ($db02[$i]);
    $currentDesc     = ($db03[$i]);
    $currentPrice    = ($db04[$i]);
    $currentID       = ($db18[$i]);


    $update = mysqli_query($conn, "UPDATE `Bank` SET 
    `Quantity`  = '".$currentQty."', 
    `Category`  = '".$currentDesc."', 
    `Item`      = '".$currentPrice."' 
    
    WHERE `QID` = '".$currentID ."' ");
}

if (mysqli_query($conn, $update)) {
  echo "<script>location.href = 'somewhere.php';</script>";
} else {
  echo "Error: " . $update . "<br>" . mysqli_error($conn);
}

$stmt->close();
$conn->close();
?>

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        
        
        
        echo "<tr>
        
        <td><input type='hidden' class='db01' name='inp01[]' value='".$row["ID"]."' readonly>                   </td>
        <td><input type='text' class='db02' name='inp02[]' value='".$row["Quantity"]."'>                    </td>
        <td><input type='text' class='db03' name='inp03[]' value='".$row["Category"]."'>                    </td>
        <td><input type='text' class='db04' name='inp04[]' value='".$row["Item"]."'>                        </td>
        <td><input type='number' step='any' class='db05' name='inp05[]' value='".$row["UnitPrice"]."'>      </td>
        <td><input type='number' step='any' class='db06' name='inp06[]' value='".$row["TOTAL"]."'>          </td>
        <td><input type='number' step='any' class='db07' name='inp07[]' value='".$row["MiscMat"]."'>        </td>
        <td><input type='number' step='any' class='db08' name='inp08[]' value='".$row["LaborHours"]."'>     </td>
        <td><input type='number' step='any' class='db09' name='inp09[]' value='".$row["LaborRate"]."'>      </td>
        <td>
        <input type='hidden' class='db10' name='inp10[]' value='".$row["QNumber"]."'>
        <input type='hidden' class='db11' name='inp11[]' value='".$row["Date"]."'>
        <input type='hidden' class='db12' name='inp12[]' value='".$row["Company"]."'>
        <input type='hidden' class='db13' name='inp13[]' value='".$row["Address1"]."'>
        <input type='hidden' class='db14' name='inp14[]' value='".$row["Address2"]."'>
        <input type='hidden' class='db15' name='inp15[]' value='".$row["ATTN"]."'>
        <input type='hidden' class='db16' name='inp16[]' value='".$row["Project"]."'>
        <input type='hidden' class='db17' name='inp17[]' value='".$row["Email"]."'>
        <input type='hidden' class='db18' name='inp18[]' value='".$row["QID"]."'>
                                                </td>
        </tr>";
        
        
        
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?>

CodePudding user response：

Prepared statements can be created once and have their parameters bound by reference, meaning you can bind them before any values are assigned.

You can then assign values to those parameter variables and execute the statement as many times as you want.

1. Make sure MySQLi is set to throw exceptions. This means you won't need to manually check for errors.

   mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
   

2. Prepare your statement

   $update = <<<_SQL
   UPDATE `Bank`
   SET
     `Quantity` = ?,
     `Category` = ?,
     `Item` = ?,
     `UnitPrice` = ?,
     `TOTAL` = ?,
     `MiscMat` = ?,
     `LaborHours` = ?,
     `LaborRate` = ? 
   WHERE `QID` = ?
   _SQL;
   
   $stmt = $conn->prepare($update);
   

3. Bind parameters

   $stmt->bind_param(
       'ssssssssd',
       $quantity, 
       $category, 
       $item, 
       $unitPrice, 
       $total, 
       $miscMat, 
       $laborHours, 
       $laborRate,
       $qid
   );
   

4. Loop, assign variable values and execute

   $count = count($_POST['inp18']);
   for ($i = 0; $i < $count; $i  ) {
       $quantity = $_POST['inp02'][$i];
       $category = $_POST['inp03'][$i];
       $item = $_POST['inp04'][$i];
       $unitPrice = $_POST['inp05'][$i];
       $total = $_POST['inp06'][$i];
       $miscMat = $_POST['inp07'][$i];
       $laborHours = $_POST['inp08'][$i];
       $laborRate = $_POST['inp09'][$i];
       $qid = $_POST['inp18'][$i];
   
       $stmt->execute();
   }
   
   header('Location: somewhere.php');
   exit;