'Mysql doesn't store properly my zerofilled values
I am having a very strange issue. I am trying to upload a little over 6800 items to a table from an xlsx file. It goes through 41 items perfectly but the 42nd item gets the wrong UPC loaded into the table. The next 890ish items all get the same UPC even though inside my PHP script the correct UPC is shown and output to the screen.
This is my script:
error_reporting(E_ALL);
require_once '../vendor/autoload.php';
require_once '../config/AI_Toolbox.php';
require_once '../config/namespacedDatabase.php';
use AI\Toolbox\AI_Toolbox;
use nsDatabase\namespacedDatabase;
use PhpOffice\PhpSpreadsheet;
$toolbox = new AI_Toolbox();
$database = new namespacedDatabase();
$db = $database->getConnection();
$reader = new PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load(__dir__ . '/insert.xlsx');
$sheet = $spreadsheet->getSheet($spreadsheet->getFirstSheetIndex());
$data = $sheet->toArray();
//$toolbox->prettyPrint($data);
foreach ($data as $datum)
{
$toolbox->prettyPrint($datum);
// var_dump($datum);
$name = $datum[0];
$model = $datum[1];
$upc = $datum[2];
$subcategory = $datum[4];
$manufacturer = $datum[3];
$productInsert = "INSERT INTO `redacted`.redacted2
(manufacturer_id, subcategory_id, product_name, product_model, product_upc)
VALUES
(:mid,:sid,:pin,:pim,:piu)";
echo "<strong>UPC </strong>";
$toolbox->prettyPrint($upc);
// var_dump($upc);
$insertPrepped = $db->prepare($productInsert);
$insertPrepped->bindValue(':mid', $manufacturer, PDO::PARAM_INT);
$insertPrepped->bindValue(':sid', $subcategory, PDO::PARAM_INT);
$insertPrepped->bindValue(':pin', $name);
$insertPrepped->bindValue(':pim', $model);
$insertPrepped->bindValue(':piu', $upc, PDO::PARAM_INT);
$toolbox->prettyPrint($upc);
// $toolbox->prettyPrint($insertPrepped);
$results = $insertPrepped->execute();
$toolbox->prettyPrint($upc);
if ($results === true)
{
echo "<div><strong>Product $name, with upc: $upc Inserted Successfully</strong></div><br/>";
} else
{
echo "<div><strong>Failed to Insert $name, with upc $upc </strong></div><br/>";
}
unset($name, $model, $upc, $subcategory, $manufacturer);
}
Here is an example of the spreadsheet right where the issue begins:
As you can see it has the correct UPC but when it Inserts into the database it inserts like this:
| ProductId | manufacturerId | subcategoryId | productName | ProductModel | productUPC |
|---|---|---|---|---|---|
| 42 | 4 | 21 | TETON 20" BOYS BIKE | 4294967295 |
I have tried everything I can think of, moving the item to the end of the file, changing the file from xlsx to csv and back, changing the columns. Nothing I have done has worked, the 42nd item gets the wrong UPC
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

