Dynamic sql insert for exporting excel into sqlserver using php?

Viewed 58

I have been exporting an Excel file to a database using PHP, which is working properly. I want to use a dynamic INSERT SQL query where I do not need to specify column names and column numbers.

I have done enough research and I am unable to come up with a solution. This is my attempt so far:

    <?php
    session_start();
    $serverName = "192.168.22.68"; //serverName\instanceName
    $connectionInfo = array( "Database"=>"INTEROPDB", "UID"=>"uatkrasqluser", "PWD"=>"CV!u@t2018");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);
    
    if($conn) {
        
        
    
    }else{
     echo "Connection could not be established.<br />";
        die( print_r( sqlsrv_errors(), true));
    }
    
    
    require 'vendor/autoload.php';
    
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    
    if(isset($_POST['save_excel_data']))
    {
        $fileName = $_FILES['import_file']['name'];
        $file_ext = pathinfo($fileName, PATHINFO_EXTENSION);
    
        $allowed_ext = ['xls','csv','xlsx'];
    
        if(in_array($file_ext, $allowed_ext))
        {
            $inputFileNamePath = $_FILES['import_file']['tmp_name'];
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileNamePath);
            $data = $spreadsheet->getActiveSheet()->toArray();
    
            $count = "0";
            foreach($data as $row)
            {
                if($count > 0)
                {
                    $fullname = $row['0'];
                    $email = $row['1'];
                    $phone = $row['2'];
                    $course = $row['3'];
    
                    $studentQuery = "INSERT INTO PRIYATIWARI (fullname,email,phone,course) VALUES ('$fullname','$email','$phone','$course')";
                    $result = sqlsrv_query($conn, $studentQuery);
                    $msg = true;
                }
                else
                {
                    $count = "1";
                }
            }
    
            if(isset($msg))
            {
                $_SESSION['message'] = "Successfully Imported";
                header('Location: index.php');
                exit(0);
            }
            else
            {
                $_SESSION['message'] = "Not Imported";
                header('Location: index.php');
                exit(0);
            }
        }
        else
        {
            $_SESSION['message'] = "Invalid File";
            header('Location: index.php');
            exit(0);
        }
    }
    ?>

In place of insert tablename(column name), values() I want something dynamic where I do not need to specify the column names.

0 Answers
Related