I am trying to import an excel sheet into PHPMyAdmin. So, I have used PHPExcel and I was working out. But, Insert Statement is not working and it is not throwing any error also. The cells in Excel sheets are stored in the variable and are showing the outputs when printed but not getting stored in PHPMyAdmin.
Here is my code.
connections.php
<?php
$servername="localhost";
$username="root";
$password="";
$databasename="nivya";
$columnname="faculty_name";
$columnname1="rno";
$conn=mysqli_connect($servername,$username,$password,$databasename);
if($conn)
{
echo "Connection success ";
}
else
{
die("Connection failed because".mysqli_connect_error());
}
?>
table.php
<?php
include("connections.php");
include ("Classes/PHPExcel.php");
$output = '';
if(isset($_POST["import"]))
{
$extension = end(explode(".", $_FILES["excel"]["name"])); // For getting Extension of selected file
$allowed_extension = array("xls", "xlsx", "csv"); //allowed extension
if(in_array($extension, $allowed_extension)) //check selected file extension is present in allowed extension array
{
$file = $_FILES["excel"]["tmp_name"]; // getting temporary source of excel file
include("Classes/PHPExcel/IOFactory.php"); // Add PHPExcel Library in this code
$objPHPExcel = PHPExcel_IOFactory::load($file); // create object of PHPExcel library by using load() method and in load method define path of selected file
$output .= "<label class='text-success'>Data Inserted</label><br /><table class='table table-bordered'>";
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
for($row=2; $row<=$highestRow; $row++)
{
$output .= "<tr>";
$course_code = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(0, $row)->getValue());
$course_title = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(1, $row)->getValue());
$lpu = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(2, $row)->getValue());
$sec = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(3, $row)->getValue());
$faculty_name = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(4, $row)->getValue());
$days = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(5, $row)->getValue());
$hour = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(6, $row)->getValue());
$rno = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(7, $row)->getValue());
$common_hr = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(8, $row)->getValue());
$t1 = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(9, $row)->getValue());
$t2 = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(10, $row)->getValue());
$t3 = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(11, $row)->getValue());
$compre = mysqli_real_escape_string($conn, $worksheet->getCellByColumnAndRow(12, $row)->getValue());
$query = "INSERT INTO time_table VALUES('$course_code','$course_title','$lpu','$sec','$faculty_name',
'$days','$hour','$rno','$common_hr','$t1','$t2','$t3','$compre')"; **//INSERT STATEMENT**
$data=mysqli_query($conn, $query);
if($data){
echo "data being inserted..";
}
$output .= '<td>'.$course_code.'</td>';
$output .= '<td>'.$course_title.'</td>';
$output .= '<td>'.$lpu.'</td>';
$output .= '<td>'.$sec.'</td>';
$output .= '<td>'.$faculty_name.'</td>';
$output .= '<td>'.$days.'</td>';
$output .= '<td>'.$hour.'</td>';
$output .= '<td>'.$rno.'</td>';
$output .= '<td>'.$common_hr.'</td>';
$output .= '<td>'.$t1.'</td>';
$output .= '<td>'.$t2.'</td>';
$output .= '<td>'.$t3.'</td>';
$output .= '<td>'.$compre.'</td>';
$output .= '</tr>';
}
}
$output .= '</table>';
}
else
{
$output = '<label class="text-danger">Invalid File</label>'; //if non excel file then
}
}
?>
<html>
<head>
<title>Import Master Time Table</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:700px;
border:1px solid #ccc;
background-color:#fff;
border-radius:5px;
margin-top:100px;
}
</style>
</head>
<body>
<div class="container box">
<h3 align="center">Please Import the Time Table</h3><br />
<form method="post" enctype="multipart/form-data">
<label>Select Excel File</label>
<input type="file" name="excel" />
<br />
<input type="submit" name="import" class="btn btn-info" value="Import" />
</form>
<br />
<br />
<?php
echo $output;
?>
</div>
</body>
</html>
Please find the output attached in the link output and the database properties Database column properties and I have commented where the insert statement is. It would be really helpful if you guys help me out.
