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.