uploading csv file via php script

jamesbon · 10-11-2018, 12:42 AM

Hi, there I am having a locally hosted xampp and I am trying to upload student database
the CSV file contents are

Code:

3,Tata,mahata
4,Hathaudi,matata
5,pakaudi,koliya
6,lolu,dolu

I have tested code sample from here https://phppot.com/php/import-csv-fi...sql-using-php/
to understand how uploading in database works so I was able to upload with following code
in file upload.php

Code:

<?php
include "../bucket.php";
$obDBRel = new DBRel;
    
    
$conn = $obDBRel->DBConn();
//$conn = mysqli_connect("localhost", "root", "", "studentfeedback");

$import=$_POST['import'];
if (isset($_POST["import"])) {
    
    $fileName = $_FILES["file"]["tmp_name"];
    
    if ($_FILES["file"]["size"] > 0) {
        
        $file = fopen($fileName, "r");
        
        while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
            $sqlInsert = "INSERT into student (Roll_No,Name,Pass)
                   values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "')";
            $result = mysqli_query($conn, $sqlInsert);
            
            if (! empty($result)) {
                $type = "success";
                $message = "CSV Data Imported into the Database";
            } else {
                $type = "error";
                $message = "Problem in Importing CSV Data";
            }
        }
    }
}
?>
<body>
    <h2>Import CSV file for adding students</h2>
<form class="form-horizontal" action="" method="post" name="uploadCSV"
    enctype="multipart/form-data">
    <div class="input-row">
        <label class="col-md-4 control-label">Choose CSV File</label> <input
            type="file" name="file" id="file" accept=".csv">
        <button type="submit" id="submit" name="import"
            class="btn-submit">Import</button>
        <br />

    </div>
    <div id="labelError"></div>
</form>
<?php
            $sqlSelect = "SELECT * FROM student";
            $result = mysqli_query($conn, $sqlSelect);
            
            if (mysqli_num_rows($result) > 0) {
 ?>
            <table id='userTable'>
            <thead>
                <tr>
                    <th>RollNo</th>
                    <th>Name</th>
                 </tr>
            </thead>
<?php
                
                while ($row = mysqli_fetch_array($result)) {
                    ?>
                    
                <tbody>
                <tr>
                    <td><?php  echo $row['Roll_No']; ?></td>
                    <td><?php  echo $row['Name']; ?></td>
                    
                    
                </tr>
                    <?php
                }
                ?>
                </tbody>
        </table>
        <?php } ?>
    </body>

but this was just a test script/test page to understand how uploading via csv works.The same uploading has to be done in a page addstudent.php
The code for addstudent.php page is

Code:

<?php
	
	session_start();
	include "../bucket.php";

	$obDBRel = new DBRel;
	error_reporting(0);
	$obDBRel->redirect();
	
	//Values from the Form
	$name=$_POST['sname'];
	$pass=md5($_POST['pwd']);
	
	//Connection to DB
	$conn = $obDBRel->DBConn();

	if ($_SERVER['REQUEST_METHOD'] == 'POST'){

		//Inserting values into Student Table
		if($name!='' && $pass!=''){
			$sql="INSERT INTO Student VALUES (NULL, '$name', '$pass')";
			
			if ($conn->query($sql) === TRUE)
				echo "<script> alert('Student Added!'); </script>";
			else
				echo "Error: " . $sql . "<br>" . $conn->error;
		}

		
		//Saving resources
		
	}
 //$conn = mysqli_connect("localhost", "root", "", "studentfeedback");
$import=$_POST["import"];
$_SESSION['import']=$import;
	if (isset($_POST["import"])) {
   $message = "hiii";
 echo "<script type='text/javascript'>alert('$message');</script>";
    $fileName = $_FILES["file"]["tmp_name"];
    
    if ($_FILES["file"]["size"] > 0) {
        
        $file = fopen($fileName, "r");
        
        while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
            $sqlInsert = "INSERT into student (Roll_No,Name,Pass)
                   values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "')";
            $result = mysqli_query($conn, $sqlInsert);
            
            if (! empty($result)) {
                $type = "success";
                $message = "CSV Data Imported into the Database";
            } else {
                $type = "error";
                $message = "Problem in Importing CSV Data";
            }
        }
    }
}

?>
<!DOCTYPE html>
	<head>
		<title>Add Student - Admin</title>
		<link rel="stylesheet" type="text/css" href="addstudent.css">
	</head>
	<body>
		<header>
			<a href='admin.php'><img src="../images/back.png"></a>
			<img src ="../images/tellus-logo.png"/>
			<span>
				<a href="../logout.php">Logout</a>
			</span>
		</header>
		<article>
			<h1>Add a Sutudent:</h1>
			<form action=addstudent.php method=post>
				<div class=input>
					<input type=text name=sname placeholder="Student Name">
					<input type=text name=pwd placeholder="Student Password">
					<button type=submit>Append</button>
					<h2>Import CSV file for adding students</h2>
					<div class="input-row">
        <label class="col-md-4 control-label">Choose CSV File</label> <input
            type="file" name="file" id="file" accept=".csv">
        <button type="submit" id="import" name="import"
            class="btn-submit">Import</button>
        <br />

    </div>
    <div id="labelError"></div>
					<!-- <a href="upload.php" ><button type=submit >Upload CSV</button></a> -->
				</div>
				<div class=output>
					<?php
						$obDBRelb = new DBRel;
						$conn=$obDBRelb->DBConn();
						$sql="Select * from Student order by Roll_No asc";
						$result = $conn->query($sql);

						echo "<table class=slist>";
						echo "<tr>";
						echo 	"<th>Student Roll_No</td>";
						echo 	"<th>Student Name</td>";
						echo 	"<th>Password</td>";
						echo "</tr>";

						if($result->num_rows > 0)
							while($row = $result->fetch_assoc()){
								echo "<tr>";
								echo 	"<td>" . $row["Roll_No"] . "</td>";
								echo 	"<td>" . $row["Name"] . "</td>";
								echo 	"<td>" . $row["Pass"] . "</td>";
								echo "</tr>";
						}

						echo "</table>";

						$conn->close();
					?>
				</div>
			</form>
<!-- <form class="form-horizontal" action="" method="post" name="uploadCSV"
    enctype="multipart/form-data">
    
</form> -->

		</article>
		<?php
            $sqlSelect = "SELECT * FROM student";
            $result = mysqli_query($conn, $sqlSelect);
            
            if (mysqli_num_rows($result) > 0) {
 ?>
            <table id='userTable'>
            <thead>
                <tr>
                    <th>RollNo</th>
                    <th>Name</th>
                 </tr>
            </thead>
<?php
                
                while ($row = mysqli_fetch_array($result)) {
                    ?>
                    
                <tbody>
                <tr>
                    <td><?php  echo $row['Roll_No']; ?></td>
                    <td><?php  echo $row['Name']; ?></td>
                    
                    
                </tr>
                    <?php
                }
                ?>
                </tbody>
        </table>
        <?php } ?>
	</body>
</html>

but this code is not adding the values inside csv to database I have tried to upload it many times and modified the code many times.
bucket.php is just a few classes to handle sql
here you can see bucket.php

Code:

<?php 
	class DBRel{
		
		function DBConn(){
			$conn = new mysqli("localhost", "root", "", "studentfeedback");
			if ($conn->connect_error)
				die("Connection failed: " . $conn->connect_error);
			
			return $conn;
		}

		function redirect(){
			$flag=0;
			$conn=$this->DBConn();
			//if($conn == TRUE)
				//echo "wwwwwwwwww";
			$sql=" Select Name from student ";
			$result = $conn->query($sql);
			//$message1 = "$conn";
			//$rowcount=mysqli_num_rows($result);
			//$message = "$rowcount";
 // echo "<script type='text/javascript'>alert('$message');</script>";

			if($_SESSION["user"] == "admin")
				$flag=1;

			if($result->num_rows >0)
				while($row = $result->fetch_assoc())
					if($_SESSION["user"] == $row["Name"]){
						//$message = $row["Name"];
  //echo "<script type='text/javascript'>alert('$message');</script>";
						$flag=1;
						break;
					}

			if($flag==0)
				header("Location: ../login/login.php");

			$conn->close();
		}

		function redirectlogin(){
			$flag=0;
			$conn=$this->DBConn();
			$sql="Select Name from student";
			$result = $conn->query($sql);

			if($_SESSION["user"] == "admin")
				$flag=1;

			if($result->num_rows >0)
				while($row = $result->fetch_assoc())
					if($_SESSION["user"] == $row["Name"]){
						$flag=2;
						break;
					}

			else if($flag==1)
				header("Location: ../admin/admin.php");
			else if($flag==2)
				header("Location: ../student/student.php");

			$conn->close();
		}
	} 
?>

what is the error or problem in addstudent.php or additionally what can I do to debug it I am not able to understand if $_POST["import"] contains any thing or not just to make sure if it has got the csv file or there are no connection errors in database.

astrogeek · 10-11-2018, 01:29 AM

Quote:

Originally Posted by jamesbon

Hi, there I am having a locally hosted xampp and I am trying to upload student database

I have tested code sample from here ...
to understand how uploading in database works so I was able to upload with following code
in file upload.php
...
but this was just a test script/test page to understand how uploading via csv works.The same uploading has to be done in a page addstudent.php
The code for addstudent.php page is
...

but this code is not adding the values inside csv to database I have tried to upload it many times and modified the code many times
....
what is the error or problem in addstudent.php or additionally what can I do to debug it I am not able to understand if $_POST["import"] contains any thing or not just to make sure if it has got the csv file or there are no connection errors in database.

Only you can debug your code, it is not generally acceptable to post whole code and ask others why it is not working.

Quote:

What is the error?

I must turn that question around - what are the error messages you are seeing? We cannot really tell anything from the information you have given.

Please provide any error codes being generated when the script is run. If you are not seeing any errors then I would first verify that the server is not suppressing them. I have no familiarity with xampp but you should locate the php.ini file used by PHP under Apache and set...

Code:

display_errors = on
error_reporting = E_ALL

...and introduce an intentional error to be certain it is sending errors to your screen.

Also check the Apache error logs for the platform.

Based on what you see, try to figure out what is happening then return here if when you have more information!

Also, please review the Site FAQ for guidance in posting your questions and general forum usage. Especially, read the link from that page, How To Ask A Question The Smart Way. The more effort you put into understanding your problem and framing your question, the better others can help.

The programming forum is open to all platforms and languages, but the audience here is decidedly Unix/Linux oriented. If your platform is no Linux or a Unix variant, as suggested by xampp, you might want to post your question to a forum frequented by users of the OS you are using.

Good luck!

individual · 10-12-2018, 08:20 PM

I can't comment on your error, but I can tell you that you should be using PDO for all of your SQL calls.

parvez1487 · 06-28-2020, 01:56 AM

I am new here and just joined , I am just sharing php script that help to other user,

The original post is https://www.phpflow.com/php/import-csv-file-into-mysql/

PHP Code:



<?php 
if(isset($_POST["submit"])) 
{ 
$host="localhost"; // Host name. 
$db_user="root"; //mysql user 
$db_password=""; //mysql pass 
$db='phpDB'; // Database name. 
//$conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error()); 
//mysql_select_db($db) or die (mysql_error()); 
$con=mysqli_connect($host,$db_user,$db_password,$db); 
// Check connection 
if (mysqli_connect_errno()) 
{ 
  echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
} 
 
 
echo $filename=$_FILES["file"]["name"]; 
$ext=substr($filename,strrpos($filename,"."),(strlen($filename)-strrpos($filename,"."))); 
 
//we check,file must be have csv extention 
if($ext=="csv") 
{ 
  $file = fopen($filename, "r"); 
         while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE) 
         { 
            $sql = "INSERT into tableName(name,email,address) values('$emapData[0]','$emapData[1]','$emapData[2]')"; 
            mysqli_query($con, $sql); 
         } 
         fclose($file); 
         echo "CSV File has been successfully Imported."; 
} 
else { 
    echo "Error: Please Upload only CSV File"; 
} 
 
 
} 
?>