Import csv file into mysql via php

Question

I am importing a csv file into my database (Mysql) using php. This code below inserts only the first row (data in the csv file) into the database. But it is supposed to insert more than a row into the database.

$info = pathinfo($_FILES['file']['name']);

/**
 * This checks if the file is a csv file
 */
if(strtolower($info['extension']) == 'csv'){
    $filename=$_FILES["file"]["tmp_name"];

    if($_FILES["file"]["size"] > 0){

        //Open the file
        $file = fopen($filename, "r");

        while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE){
            $num = count($emapData);

            /**
             * Insert data into the database if the columns are 
             *  exactly in three columns
             */ 
            if ($num == 3){
                $sql = "INSERT 
                        INTO Users (firstName,surName,indexNo)
                    VALUES('$emapData[0]','$emapData[1]','$emapData[2]')";

                $result=$db->query($sql);


                if($result){
                    echo "<script type=\"text/javascript\">
                            alert(\"CSV File has been successfully Imported.\");
                            window.location = \"../administrator/bulkStudentReg.php\"
                        </script>";
                }
                else{
                    echo "<script type=\"text/javascript\">
                    alert(\"Please Upload CSV File was not successful.\");
                    window.location = \"../administrator/bulkStudentReg.php\"
                    </script>";
                }
            }
            else{
                echo "<script type=\"text/javascript\">
                    alert(\"UPLOAD FAILED: Please your csv file contains incomplete column/s. Column should be 3 columns\");
                    window.location = \"../administrator/bulkStudentReg.php\"
                    </script>";
            }
        fclose($file);
        }
    }
}
else{
    echo "<script type=\"text/javascript\">
                    alert(\"UPLOAD FAILED: Please the file should be in a csv file, eg. students.csv \");
                    window.location = \"../administrator/bulkStudentReg.php\"
                    </script>";
}

What is $emapData? What sort of data in it?

Keep Coding
– Keep Coding

2015-04-06 11:24:49 +00:00
Commented Apr 6, 2015 at 11:24 — Keep Coding
– Keep Coding, Commented Apr 6, 2015 at 11:24

hexerei software · Accepted Answer · 2015-04-06 11:28:37Z

5

You could try using the MySQL built-in function for inserting CSV data, if the structure of your data is equal to your table structure it is pretty simple:

$sql = "LOAD DATA INFILE '$filename' INTO TABLE Users
FIELDS TERMINATED BY ','"

check MySQL LOAD DATA Documentation for more details

answered Apr 6, 2015 at 11:28

hexerei software

3,1802 gold badges17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Keep Coding · Accepted Answer · 2015-04-06 11:31:09Z

3

Are you looking for something like this.

I didn't test is as I don't have sample data from you but please give it a go.

<?php
$info = pathinfo($_FILES['file']['name']);

/**
 * This checks if the file is a csv file
 */
if(strtolower($info['extension']) == 'csv'){
    $filename=$_FILES["file"]["tmp_name"];

    if($_FILES["file"]["size"] > 0){

        //Open the file
        $file = fopen($filename, "r");

        while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE){
            $num = count($emapData);

            /**
             * Insert data into the database if the columns are 
             *  exactly in three columns
             */ 
            if ($num == 3){
                $sql = "INSERT 
                        INTO Users (firstName,surName,indexNo)
                    VALUES('$emapData[0]','$emapData[1]','$emapData[2]')";

                $result=$db->query($sql);
            }
        }
        if($result){
                    echo "<script type=\"text/javascript\">
                            alert(\"CSV File has been successfully Imported.\");
                            window.location = \"../administrator/bulkStudentReg.php\"
                        </script>";
                }else{
                    echo "<script type=\"text/javascript\">
                    alert(\"Please Upload CSV File was not successful.\");
                    window.location = \"../administrator/bulkStudentReg.php\"
                    </script>";
                }
    }
    fclose($file);
}
else{
    echo "<script type=\"text/javascript\">
                    alert(\"UPLOAD FAILED: Please the file should be in a csv file, eg. students.csv \");
                    window.location = \"../administrator/bulkStudentReg.php\"
                    </script>";
}
?>

answered Apr 6, 2015 at 11:31

Keep Coding

6029 silver badges26 bronze badges

2 Comments

George Over a year ago

Yeah, This works well.. But please, what is the maximum number i can upload per a request. Thanks

Keep Coding Over a year ago

That depends on your need. There are no maximum numbers apparently that is need to upload per a request. It will work well for your case.

Collectives™ on Stack Overflow

Import csv file into mysql via php

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related