My code cannot import the data inside the Excel file to phpMySql. I spent alot of time to find the mistake but I can't, so please, can anyone help me pass this bad time
after I ran this code, the web-browser that shown me no error but when I access to my phpMyAdmin, there is no any data come from Excel.
example.php
<?php
include ("PHPExcel/IOFactory.php");
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "dashboard_db";
$tbname_act = "average_cycle_time";
$inputFileType = 'Excel2007';
$inputFileName = 'example.xlsx';
$sheetnames = 'ACT' ;
$connect = mysqli_connect($servername, $username, $password, $dbname);
if ($connect -> connect_error){
die ("connection failed: " . $conn -> connect_error);
}
echo "Connection successful ! <br>" ;
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
$objReader->setLoadSheetsOnly($sheetnames);
$html="<table border='1'>";
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
for ($row=2; $row<=$highestRow; $row++)
{
$html.="<tr>";
$TruckID = mysqli_real_escape_string($connect, $worksheet- >getCellByColumnAndRow(0, $row)->getValue());
$Date = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());
$ArrivalTime = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(2, $row)->getValue());
$DepartTime = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(3, $row)->getValue());
$sql = "INSERT INTO $tbname_act(excel_TruckID, excel_Date, excel_ArrivalTime, excel_DepartTime)
VALUES ('".$TruckID."', '".$Date."', '".$ArrivalTime."', '".$DepartTime."')";
mysqli_query($connect, $sql);
$html.= '<td>'.$TruckID.'</td>';
$html .= '<td>'.$Date.'</td>';
$html .= '<td>'.$ArrivalTime.'</td>';
$html .= '<td>'.$DepartTime.'</td>';
$html .= "</tr>";
}
}
$html .= '</table>';
echo $html;
echo '<br />Time for calculation of Average Cycle Time ... Inserted !!';
?>
create.sql
CREATE TABLE IF NOT EXISTS `average_cycle_time` (
`TruckID` int(10) NOT NULL,
`Date` date NOT NULL,
`ArrivalTime` timestamp NOT NULL,
`DepartTime` timestamp NOT NULL,
PRIMARY KEY (`TruckID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
Its possible that your sql query is not correct. You can debug the sql query by displaying the value of $sql and then running the sql query from Phpmyadmin