How To Import csv Data in To Mysql Database
Hello Dear friends here i'm describe to How To Import csv Data in To Mysql Database using core php functionality .
Create a file db.php
in this file im write database connection code using php
<?php
function getdb(){
$servername = "localhost";
$username = "root";
$password = "";
$db = "csv";
try {
$conn = mysqli_connect($servername, $username, $password, $db);
//echo "Connected successfully";
}
catch(exception $e)
{
echo "Connection failed: " . $e->getMessage();
}
return $conn;
}
?>
Create a main file index.php to our project
this file is load our website and form to help of import Import csv Data in To Mysql Database
<?php
include 'functions.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
</head>
<body>
<div id="wrap" style="margin-top: 200px;">
<div class="container">
<div class="row">
<form class="form-horizontal" action="functions.php" method="post" name="upload_excel" enctype="multipart/form-data">
<fieldset>
<!-- Form Name -->
<legend>Form Name -Deepak Kumar Bind </legend>
<!-- File Button -->
<div class="form-group">
<label class="col-md-4 control-label" for="filebutton">Select File</label>
<div class="col-md-4">
<input type="file" name="file" id="file" class="input-large" required>
</div>
</div>
<!-- Button -->
<div class="form-group">
<label class="col-md-4 control-label" for="singlebutton">Import data</label>
<div class="col-md-4">
<button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Upload</button>
</div>
</div>
</fieldset>
</form>
</div>
<div>
<a href="student.php">check Record</a>
</div>
</div>
</div>
</body>
</html>
Create a file function.php
this file to contain all our user define function to help for project
<?php
include 'db.php';
$con = getdb();
if(isset($_POST["Import"])){
$filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
while (($getData = fgetcsv($file, 536870912, ",")) !== FALSE)
{
// $sql = "INSERT into details(name,email,address)
// values ('".$getData[0]."','".$getData[1]."','".$getData[2]."')";
$sql = "INSERT INTO students SET
academic= '$getData[2]',
session='$getData[3]',
allote_category='$getData[4]',
voucher_no='$getData[5]',
roll_no='$getData[6]',
admno='$getData[8]',
status='$getData[9]',
free_category='$getData[10]',
faculty='$getData[11]',
program='$getData[12]',
department='$getData[13]',
batch='$getData[14]',
receipt_no='$getData[15]',
free_hand='$getData[16]',
due_ammount='$getData[17]',
paid_ammount='$getData[18]',
concession='$getData[19]',
scholership='$getData[20]',
reverse_act='$getData[21]',
write_off='$getData[22]',
adjust='$getData[23]',
refund_money='$getData[24]',
fund_transctions='$getData[25]'";
$result = mysqli_query($con, $sql);
if(!isset($result))
{
echo "<script type=\"text/javascript\">
alert(\"Invalid File:Please Upload CSV File.\");
window.location = \"index.php\"
</script>";
}
else {
echo "<script type=\"text/javascript\">
alert(\"CSV File has been successfully Imported.\");
window.location = \"index.php\"
</script>";
}
}
fclose($file);
}
}
function alldatadel(){
$servername = "localhost";
$username = "root";
$password = "";
$db = "csv";
$con = mysqli_connect($servername, $username, $password, $db);
$alldel = "TRUNCATE TABLE students";
$resultdel = $con->query($alldel);
}
Create a file students.php - to show record on page
this file show all over record to Import csv Data in To Mysql Database
<?php
include 'functions.php';
$servername = "localhost";
$username = "root";
$password = "";
$db = "csv";
$con = mysqli_connect($servername, $username, $password, $db);
if (isset($_GET['alldatadel'])) {
alldatadel();
echo "<script>window.location.href='student.php'</script>";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
<style>
tr:nth-child(odd) {
color: #777;
background-color: #66cbba;
}
tr:nth-child(even) {
background-color: #ffde80;
}
</style>
</head>
<body>
<div id="wrap">
<div class="container">
<h1>Students Record</h1>
<?php
$Sql = "SELECT * FROM students";
$result = $con->query($Sql);
$total_records = mysqli_num_rows($result);
$totolpaid_ammount="select SUM(paid_ammount) from students";
$totolpaid_ammount1 = $con->query($totolpaid_ammount);
$wonresdatareds = $totolpaid_ammount1->fetch_assoc();
echo '<br/><h3>Total Paid Ammount = ₹'. $wonresdatareds['SUM(paid_ammount)'].'</h3>';
?>
<h3>Total Record Found : <?php echo $total_records; ?></h3>
<h5><a href='student.php?alldatadel=true'>All Data Delete</a></h5>
<table class="table table-striped " border="1">
<thead>
<tr>
<th>ID</th>
<th>academic</th>
<th>session</th>
<th>allote_category</th>
<th>voucher_no</th>
<th>roll_no</th>
<th>admno</th>
<th>status</th>
<th>free_category</th>
<th>faculty</th>
<th>program</th>
<th>department</th>
<th>batch</th>
<th>receipt_no</th>
<th>free_hand</th>
<th>due_ammount</th>
<th>paid_ammount</th>
<th>concession</th>
<th>scholership</th>
<th>reverse_act</th>
<th>write_off</th>
<th>adjust</th>
<th>refund_money</th>
<th>fund_transctions</th>
</tr>
</thead>
<tbody>
<?php
$Sql = "SELECT * FROM students limit 5";
$result = $con->query($Sql);
$total_records = mysqli_num_rows($result);
$i= 1;
while( $data = $result->fetch_assoc()){
?>
<tr>
<td><?php echo $i; ?></td>
<td><?php echo $data['academic'] ?></td>
<td><?php echo $data['session'] ?></td>
<td><?php echo $data['allote_category'] ?></td>
<td><?php echo $data['voucher_no'] ?></td>
<td><?php echo $data['roll_no'] ?></td>
<td><?php echo $data['admno'] ?></td>
<td><?php echo $data['status'] ?></td>
<td><?php echo $data['free_category'] ?></td>
<td><?php echo $data['faculty'] ?></td>
<td><?php echo $data['program'] ?></td>
<td><?php echo $data['department'] ?></td>
<td><?php echo $data['batch'] ?></td>
<td><?php echo $data['receipt_no'] ?></td>
<td><?php echo $data['free_hand'] ?></td>
<td><?php echo $data['due_ammount'] ?></td>
<td><?php echo $data['paid_ammount'] ?></td>
<td><?php echo $data['concession'] ?></td>
<td><?php echo $data['scholership'] ?></td>
<td><?php echo $data['reverse_act'] ?></td>
<td><?php echo $data['write_off'] ?></td>
<td><?php echo $data['adjust'] ?></td>
<td><?php echo $data['refund_money'] ?></td>
<td><?php echo $data['fund_transctions'] ?></td>
</tr>
<?php $i++; }?>
</tbody>
</table>
</div>
</div>
</body>
</html>
0 Comments