PhpSpreadsheet insert data in a DB from .xls/.xlsx using the first column value as db column name

Viewed 173

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!

0 Answers
Related