I've created a simple system to upload my .xlsx file into a database. But I have a problem when setting/reading the column as, right now, it works only if I work on a fixed column (sku always in column A for example). But I need to upload the data using the first column value as column name. The first column value of each .xlsx files is the same I used to create my database fields. This is how it works right now:
session_start();
$con = mysqli_connect("localhost", "root", "password", "database_name");
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
if(isset($_POST['import_file_btn']))
{
$allowed_ext = ['xls', 'xlsx'];
$fileName = $_FILES['import_file']['name'];
$checking = explode(".", $fileName);
$file_ext = end($checking);
if (in_array($file_ext, $allowed_ext))
{
$targetPath = $_FILES['import_file']['tmp_name'];
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($targetPath);
$data = $spreadsheet-> getActiveSheet()->toArray();
foreach ($data as $row)
{
$sku = $row['0'];
$p_name = $row['1'];
$id_category = $row['2'];
$id_brand = $row['3'];
$checkProduct = "SELECT sku FROM ita WHERE sku='$sku'";
$checkProduct_result = mysqli_query ($con, $checkProduct);
if (mysqli_num_rows($checkProduct_result) > 0)
{
$up_query ="UPDATE ita SET p_name='$p_name', id_category='$id_category', id_brand='$id_brand' WHERE sku='$sku' ";
$up_result = mysqli_query($con, $up_query);
$msg = 1;
}
else
{
$in_query = "INSERT INTO ita (sku, p_name, id_category, id_brand) VALUES ('$sku','$p_name', '$idcategory', '$id_brand')";
$in_result = mysqli_query ($con, $in_query);
$msg = 1;
}
}
if(isset($msg))
{
$_SESSION['status'] = "All data where imported right";
header("Location: index.php");
}
else
{
$_SESSION['status'] = "Error during file import";
header("Location: index.php");
}
}
}
else
{
$_SESSION['status'] = "Invalid file format";
header("Location: index.php");
exit(0);
}
As I said some rows are fixed but others could be not present (for example variable values like color, measures and so on) or could be everywhere (column U or Z).
Any advice on how to change the row system using the first value of each column of my .xlsx file?
Thank you very much!
- - UPDATE - -
Following advices I made this changes:
$data = $spreadsheet-> getActiveSheet()->toArray();
$firstR = array_values($data[0]);
$flipped = array_flip($firstR);
foreach ($flipped as $row) {
$sku = $row[ $flipped['sku'] ];
and making a var_dump of $flipped it works correctly but the foreach seems not reading correctly the values as, when I import, I get no error but no data (only 0 on INT db column)
Thanks!