Why is Updating Data In MySQL Table throws Error 1 SQL syntax at line 1

Question

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);
?>

Please [edit] your question to include the **full** error message and which line of your code it refers to — Phil, Oct 03 '22 at 00:01
1) I strongly suggest you use a prepared statement for your secondary `$update` queries. 2) Why are you treating `$db02`, `$db03`, etc as arrays after the first query? — Phil, Oct 03 '22 at 00:04
here is the full error message: Error: 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 — JCprog, Oct 03 '22 at 00:10
@Phil, I honestly have no idea how else to write it, this is my first stab at database query. — JCprog, Oct 03 '22 at 00:12
[advice] Better not mixing Procedural style and OO Style. Hence change `$stmt = $conn->prepare($sql);` to `$stmt = mysqli_prepare($conn, $sql);` please — Ken Lee, Oct 03 '22 at 00:27
@KenLee mixing styles won't cause any errors and if anything, I would advise preferring OO style — Phil, Oct 03 '22 at 00:29
@JCprog it's really unclear what you're trying to do with this code. Why do you run one `UPDATE` query on the `Bank` table (via prepared statement ✔) then try and run further `UPDATE` queries in a loop on the same table? Why do you first treat the `$db*` parameters (from `$_POST`) as scalar values then treat some of them as arrays? What exactly are you trying to do with this code? — Phil, Oct 03 '22 at 00:32
My database consist of multiple rows and columns. I fetch data through php with input so each individual data can be edited. When the user is done editing one submit button will update all data back to database. I originally came up with the loop to update all rows then tried to do a prepared statements as everyone suggests. — JCprog, Oct 03 '22 at 00:40
Got it, so you have multiple input boxes as array.... But in that case you should edit your query (now your 2nd query) to make it a prepared statement instead of adding another query on top of it. (If you still have questions please post your HTML code containing the input box arrays too so that we can further advise) — Ken Lee, Oct 03 '22 at 00:42
@Ken I have added the php that fetched data with input boxes — JCprog, Oct 03 '22 at 00:53
@KenLee the statement should be prepared before looping. Only the execution needs to be within the loop — Phil, Oct 03 '22 at 01:07
I moved execution like this: for($i=0; $i < $count; $i++){ $stmt->execute(); .... — JCprog, Oct 03 '22 at 01:17

Phil · Accepted Answer · 2022-10-03T01:48:51.013

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.

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);
```

Prepare your statement

$update = <<<_SQL
UPDATE `Bank`
SET
  `Quantity` = ?,
  `Category` = ?,
  `Item` = ?,
  `UnitPrice` = ?,
  `TOTAL` = ?,
  `MiscMat` = ?,
  `LaborHours` = ?,
  `LaborRate` = ? 
WHERE `QID` = ?
_SQL;

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

Bind parameters

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

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;

Thanks for the code, now I get this error: Error: UPDATE `Bank` SET `Quantity` = ?, `Category` = ?, `Item` = ?, `UnitPrice` = ?, `TOTAL` = ?, `MiscMat` = ?, `LaborHours` = ?, `LaborRate` = ? WHERE `QID` = ? You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, `Category` = ?, `Item` = ?, `UnitPrice` = ?, `TOTAL` = ?, `Mis' at line 3 — JCprog, Oct 03 '22 at 01:44
@JCprog that to me says you're still using `mysqli_query()` which you should definitely not be doing — Phil, Oct 03 '22 at 01:47
It worked! I would have never come close to your code. This is a great learning experience, Thanks a million! — JCprog, Oct 03 '22 at 02:07

Why is Updating Data In MySQL Table throws Error 1 SQL syntax at line 1

1 Answers1