How to Upload Excel File in Php
How to Import Excel/CSV file to MySQL Database using PHP
Submitted by joken on Tuesday, Feb ii, 2021 - 11:02.
In this tutorial im going to testify yous how to create n application that can be used to Import CSV/Excel file using PHP. Nosotros will create a simple PHP spider web application that has a form for our import feature and table to display our imported data to the database.
*Upgrade Info
The tutorial source lawmaking was originally written in PHP MySQL and I have updated information technology into PHP MySQLi.
To start with this project, please brand certain that you accept already installed a local spider web server that can run PHP script and MySQL Database such as "XAMPP" and "WAMP". Then besides make sure that the "Apache" and "MySQL" is started (for XAMPP/WAMP, open the XAMPP/WAMP Control Console and start the Apache and MySQL).
Now, nosotros will create a Database in your PHPMyAdmin named "studentdb" and then execute this SQL query to create a new table called "bailiwick".
CREATE Tabular array IF Non EXISTS `subject` (
`SUBJ_ID` INT ( 11 ) NOT Cypher AUTO_INCREMENT ,
`SUBJ_CODE` VARCHAR ( 30 ) Non NULL ,
`SUBJ_DESCRIPTION` VARCHAR ( 255 ) NOT NULL ,
`Unit of measurement` INT ( ii ) NOT NULL ,
`PRE_REQUISITE` VARCHAR ( thirty ) Non NULL DEFAULT 'None' ,
`COURSE_ID` INT ( 11 ) Non Zero ,
`AY` VARCHAR ( thirty ) NOT Nil ,
`SEMESTER` VARCHAR ( 20 ) NOT Aught ,
Main Key ( `SUBJ_ID` )
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT = 500 ;
Then inside you htdocs or document root folder create a new folder and proper noun it as "excel". Next, nosotros need to create a PHP file naming "db.php" that will agree our database connexion and here'south the following lawmaking:
<?php
$conn = mysqli_connect ( "localhost" , "root" , "" ) or die ( "Could not connect" ) ;
?>
Next, create another PHP file named "index.php",and this PHP file volition be the first page that will load to our browser when we will access/browse the "excel" folder from our web directory. And this file will load all the listing of subjects if the subject tabular array is non empty well as this page will permit the user to import the CSV/Excel file and upload the data to MySQL Database.
The page Interface will be look similar the paradigm below.
And hither's the code for the "alphabetize.php" file:
<!DOCTYPE html>
<?php
include 'db.php' ;
?>
<html lang="en">
<head>
<meta charset="utf-viii">
<title>Import Excel To MySQL Database Using PHP </title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="clarification" content="Import Excel File To MySQL Database Using php">
<link rel="stylesheet" href="css/bootstrap.min.css">
<link rel="stylesheet" href="css/bootstrap-responsive.min.css">
<link rel="stylesheet" href="css/bootstrap-custom.css">
</head>
<body>
<!-- Navbar
================================================== -->
<div class="navbar navbar-inverse navbar-fixed-top">
<div form="navbar-inner">
<div form="container">
<a class="btn btn-navbar" data-toggle="collapse" information-target=".nav-collapse">
<span class="icon-bar"></bridge>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</a>
<a class="make" href="#">Import Excel To MySQL Database Using PHP</a>
</div>
</div>
</div>
<div id="wrap">
<div class="container">
<div class="row">
<div form="span3 hidden-phone"></div>
<div class="span6" id="course-login">
<form form="form-horizontal well" action="import.php" method="post" name="upload_excel" enctype="multipart/course-information">
<fieldset>
<legend>Import CSV/Excel file</legend>
<div class="control-group">
<div grade="command-characterization">
<label>CSV/Excel File:</characterization>
</div>
<div class="controls">
<input type="file" name="file" id="file" class="input-large">
</div>
</div>
<div class="command-group">
<div form="controls">
<button type="submit" id="submit" name="Import" form="btn btn-master button-loading" data-loading-text="Loading...">Upload</push button>
</div>
</div>
</fieldset>
</grade>
</div>
<div class="span3 hidden-telephone"></div>
</div>
<tabular array grade="tabular array table-bordered">
<thead>
<tr>
<th>ID</th>
<thursday>Subject</th>
<th>Description</th>
<th>Unit</th>
<th>Semester</th>
</tr>
</thead>
<?php
$SQLSELECT = "SELECT * FROM subject " ;
{
?>
<tr>
<td><?php echo $row [ 'SUBJ_ID' ] ; ?></td>
<td><?php echo $row [ 'SUBJ_CODE' ] ; ?></td>
<td><?php echo $row [ 'SUBJ_DESCRIPTION' ] ; ?></td>
<td><?php echo $row [ 'Unit of measurement' ] ; ?></td>
<td><?php repeat $row [ 'SEMESTER' ] ; ?></td>
</tr>
<?php
}
?>
</table>
</div>
</div>
</body>
</html>
Next, we're going to create another PHP file naming "import.php" that nosotros will use to procedure the data from CSV/Excel to MySQL Database. Here'south the following code:
<?php
include 'db.php' ;
if ( isset ( $_POST [ "Import" ] ) ) {
echo $filename = $_FILES [ "file" ] [ "tmp_name" ] ;
if ( $_FILES [ "file" ] [ "size" ] > 0 )
{
$file = fopen ( $filename , "r" ) ;
while ( ( $emapData = fgetcsv ( $file , 10000 , "," ) ) !== False )
{
//It wiil insert a row to our bailiwick table from our csv file`
$sql = "INSERT into subject (`SUBJ_CODE`, `SUBJ_DESCRIPTION`, `Unit of measurement`, `PRE_REQUISITE`,COURSE_ID, `AY`, `SEMESTER`)
values('$emapData[1]','$emapData[ii]','$emapData[3]','$emapData[4]','$emapData[5]','$emapData[6]','$emapData[7]')" ;
//we are using mysql_query function. information technology returns a resource on truthful else Faux on fault
if ( ! $result )
{
echo "<script type=\"text/javascript\">
warning(\"Invalid File:Please Upload CSV File.\");
window.location = \"index.php\"
</script>" ;
}
}
//throws a message if information successfully imported to mysql database from excel file
echo "<script type=\"text/javascript\">
alert(\"CSV File has been successfully Imported.\");
window.location = \"index.php\"
</script>" ;
//shut of connection
}
}
?>
After this, you tin can endeavour it on your own, and I accept attached with this sourcecode the example CSV/Excel file used for this tutorial, or you can also create a new CSV file to Import using below the below cavalcade orders.
| ID | Subject Code | Field of study Clarification | Unit of measurement | Pre Requisite | Form ID | AY (Academic Yr) | Semester |
DEMO
That'due south it! That would be the finish of our tutorial on Importing CSV/Excel File data to MySQL Database using PHP. By the mode in this application I am using the Twitter bootstrap framework, you lot tin can download and employ it like in this application.
If you lot want to see some new Source Code or Awarding and Tutorials Just click here.
Happy Coding :)
- 15121 views
Source: https://www.sourcecodester.com/tutorials/php/6989/how-import-excelcsv-file-mysql-database-using-php.html
Belum ada Komentar untuk "How to Upload Excel File in Php"
Posting Komentar