Update Bulk Items with MySQL PHP

Question

I'm trying to update over 3,000 records in my db table with one single bulk query, however I have run into a road block with my id array. Wondering what I'm doing wrong here?

Here is the error I receive:

check the manual that corresponds to your MySQL server version for the right syntax to use near ') END WHERE InventoryID IN (recs8AFaXZWvAlDIP,recvwDSutfQ66Jtj8,recQ1LODI7ED1FRT'

Code below:

$sql_connect = mysqli_connect("blah", "blah", "blah", "blah");

$sql = "SELECT inventory.MCategoryID, inventory.InventoryID, inventory.SCategoryID, inventory.ManufacturerID, maincategory.CategoryName as mainCategoryNameMatch, subcategory.SubCategoryName as subCategoryNameMatch, manufacturer.ManufacturerName as manufacturerNameMatch FROM inventory LEFT JOIN maincategory ON inventory.MCategoryID = maincategory.AirTableID LEFT JOIN subcategory ON inventory.SCategoryID = subcategory.AirTableSubCategoryID LEFT JOIN manufacturer ON inventory.ManufacturerID = manufacturer.AirTableManufacturerID ORDER BY CambridgeID";
$doit = mysqli_query($sql_connect, $sql) or die(mysqli_error($sql_connect));
$updateSQL = '';
$uniqueIDArray = [];
$bulkUpdate = '';
$bulkID = '';
while($results = mysqli_fetch_object($doit)){
    $mainCategoryNameResult = $results->mainCategoryNameMatch;
    $subCategoryNameResult = $results->subCategoryNameMatch;
    $manufacturerNameResult = $results->manufacturerNameMatch;
    $uniqueID = $results->InventoryID;      
    $uniqueIDArray[] = $uniqueID;


    $updateSQL .= "WHEN '$uniqueID' THEN '$mainCategoryNameResult'\n";


}
//echo $updateSQL;
$id_list = implode(',', $uniqueIDArray);    
echo "UPDATE inventory SET MainCategoryNameMatch = (CASE InventoryID $updateSQL END) WHERE InventoryID IN ($id_list)<br/>";
if (mysqli_query($sql_connect, "UPDATE inventory SET MainCategoryNameMatch = (CASE InventoryID $updateSQL) WHERE InventoryID IN ($id_list)") or die(mysqli_error($sql_connect))){
    echo $updateSQLStatement;
}

Any help would be greatly appreciated.

you are vulnerable to [sql injection attacks](http://bobby-tables.com), and you need to show the query you generated. given that error snippet, you forgot to quote the values you're stuffing into that `in` clause, amongst other errors. — Marc B, Jul 06 '16 at 15:16

score 2 · Accepted Answer · answered Jul 06 '16 at 15:16

2

The IDs are strings - they must be enclosed by quotes:

$id_list = '"' . implode('","', $uniqueIDArray) . '"';

answered Jul 06 '16 at 15:16

dhh

4,289
8
42
59

Update Bulk Items with MySQL PHP

1 Answers1