php

Import csv file in MySQL using PHP

Import csv file in MySQL using PHP with example :

Let us tell you how to Import csv file in MySQL using PHP with example by steps –

Step : 1– First create Database named “studentdb” & table named “student_details“.

Database Table Structure

Step : 2- Create dbconnection.php file add bellow codes for database connection –

<?php 
	// crete database connection
	$hostname = 'localhost';
	$dbUsername = 'root';
	$dbPassword = '';
	$dbName = 'studentdb'; // Database name
	
	// select database
	$con = new mysqli($hostname, $dbUsername, $dbPassword, $dbName);

	if ($con->connect_error) {
		die("Unable to connect database: " . $con->connect_error);
	}
?>

Step : 3- Create importForm.php file and add bellow codes –

<?php 
	include("dbconnection.php"); // database connection
?>
<!DOCTYPE html>
<html>
    <head>
	<style>
	.card_design {
	  background: #fff6f6 !important
	  border: 1px solid #358fff;
	  box-shadow: 0 0 5px rgba(0, 0, 0, 0.49);
	}
	</style>
		<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
		<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
		
		<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
		<link href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css" rel="stylesheet">
		
		<script type="text/javascript"> 
		
        $(document).ready(function() {
			// for datatable
			$('#studentTable').DataTable(); 
			
			// check file blank or not
            $('#importSubmit').bind("click",function() {   
                var imgVal = $('#fileUpload').val(); 
                if(imgVal==''){ 
                    alert("Please select csv file.");					
                    return false; 
                } else {
                    return true;
                }           
            });
			
			// check file type
			$("#fileUpload").change(function () {
				var validExtensions = ["csv"]
				var file = $(this).val().split('.').pop();
				if (validExtensions.indexOf(file) == -1) {
					alert("Only csv file is allowed !");
					$("#fileUpload").val("");
					return false;
				}
			});			
        });
    </script>  
	</head>
	<body>
		<div class="clearfix">               
			<div class="row justify-content-center">
				<div class="col-lg-6 col-md-6 col-sm-12 col-xs-12 text-center p-5">                    
					<div class="card card_design p-4">
					<div class="header row">
						<h4 class="heading-new">Import CSV File</h4> <hr>
					</div>
					<div class="row the-wrap">
						<div class="form-horizontal bootstrap-validator-form">
							<div class="users form">
								<form action="importProcess.php" method="post" enctype="multipart/form-data" id="importFrm">
								<div class="row justify-content-between">
									<div class="col-sm-4">
										<input type="file" name="file" id="fileUpload" class="file" />
									</div>
									<div class="col-sm-4 submit">
										<input type="submit" class="btn btn-primary" name="importSubmit" id="importSubmit" value="Import"/>
									</div>                                            
								</div>
								</form>
							</div>
						</div>
					</div>                  
					</div>                   
				</div>
			</div>

      <!--  Code for after import csv file successfully show the records -->
			<div class="row justify-content-center">
				<div class="col-lg-8 col-md-8 col-sm-12 col-xs-12 text-center mt-3">
				<div class="table-responsive" >
				<table class="table-bordered table table-striped" id="studentTable">
				<thead>
				<tr bgcolor="#d9f2fa">
					<th>Sr. No.</th>
					<th>Student Name</th>
					<th>DOB</th>
					<th>Mobile No.</th>
					<th>E-mail ID</th>
					<th>Reg. No.</th>
					<th>Fee Status</th>					
				</tr>
				</thead>
				
				<tbody>
				<?php 
				$srNo=1;
				$dataQrs = mysqli_query($con,"SELECT * FROM student_details");
				while($results = mysqli_fetch_array($dataQrs)){				
				?>
				<tr>
					<td><?php echo $srNo++; ?></td>
					<td><?php echo !empty($results['name']) ? $results['name'] : '-';?></td>
					<td><?php echo !empty($results['date_of_birth']) ? $results['date_of_birth'] : '-';?></td>
					<td><?php echo !empty($results['mobile_no']) ? $results['mobile_no'] : '-';?></td>
					<td><?php echo !empty($results['email_id']) ? $results['email_id'] : '-';?></td>
					<td><?php echo !empty($results['registration_no']) ? $results['registration_no'] : '-';?></td>
					<td><?php 
						if(!empty($results['is_fee_paid'])) {
							if($results['is_fee_paid']=="1"){
								echo "Paid";
							} else {
								echo "Unpaid";
							}
						} else {
							echo "Unpaid";
						}
					?></td>									
				</tr>
				<?php } $srNo++;?>
				</tbody>				
				</table>				
				</div>
				</div>
			</div>				
		</div>
	</body>
</html>
  • In this example, js & css file have been added for form validation & design.
  • Code has been added here for show the records after import the data.

Step : 4- Create importProcess.php file and add bellow codes –

<?php
	include("dbconnection.php"); // Database conection file include		
		   
	if (isset($_POST['importSubmit'])) { 
		//validate whether uploaded file is a csv file
		$csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
		if (!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)) {
			if (is_uploaded_file($_FILES['file']['tmp_name'])) {
				//open uploaded csv file with read only mode
				$csvFile = fopen($_FILES['file']['tmp_name'], 'r');
				fgetcsv($csvFile);
				
				while (($line = fgetcsv($csvFile)) !== FALSE) {				
					$noRec = CheckCatRegNocheck($line[4], $con);					
					if ($noRec > 0) {								    
						mysqli_query($con,"UPDATE student_details SET name =  '" . $line[0] . "', date_of_birth = '" . $line[1] . "', mobile_no = '" . $line[2] . "', email_id = '" . $line[3] . "' , is_fee_paid = '" . $line[5] . "'WHERE registration_no = '" . $line[4] . "'");
					} else {						
						if (!empty($line[0])) {						
						   mysqli_query($con,"INSERT INTO student_details (name, date_of_birth, mobile_no, email_id, registration_no, is_fee_paid) VALUES ('" . $line[0] . "','" . $line[1]. "','" . $line[2]. "','" . $line[3]. "','" . $line[4]. "','" . $line[5]."')");
						}
					}
				}
				header("Location: importForm.php");				
				fclose($csvFile);				
			}
		}
	}
	
	// check exiting records from registration no.
	function CheckCatRegNocheck($regno, $con) {
		$res_sql = "SELECT * FROM student_details WHERE registration_no = '$regno'";	   
		$results = mysqli_query($con, $res_sql);
		$totalRec = mysqli_num_rows($results);
		return $totalRec;  
    }
?>

Step : 5- Create csv file as bellow format and import it –

  • You can add/remove student data according to your choice.

Source Code Output

  • After import data, form will look like as –
Import csv file in MySQL using PHP

Click here to more …

Import csv file in MySQL using PHP

Leave a Reply

Your email address will not be published. Required fields are marked *

× How can I help you?