Upload CSV and Import into MySQL Database

Question

I am trying to create a form that uploads a CSV file that then inserts the data into a MYSQL database. with my code, I don't get any error message, it just doesn't insert. Here is my code:

Here is the form code:

<!DOCTYPE html>
<html>
       <head>
               <title>CSV Upload</title>
       </head>
       <body>
                <form method="POST" enctype="multipart/form-data" action="import.php">
                         <div align="center">
                                  <p>Select CSV file: <input type="file" name="file"  /></p>
                                  <p><input type="submit" name="csv_upload_btn" value="Upload"  /></p>
                         </div>
                </form>
       </body>
</html>

//Process form
if(isset($_POST["csv_upload_btn"])){
if($_FILES['file']['name']){
$filename = explode("",$_FILES['file']['name']);
if($filename[1] == "csv"){
$handle = fopen($_FILES['file']['tmp_name'], "r");
while($data = fgetcsv($handle)){
$item1 = mysqli_real_escape_string($connection, $data[0]);
$item2 = mysqli_real_escape_string($connection, $data[1]);
$item3 = mysqli_real_escape_string($connection, $data[2]);
$item4 = mysqli_real_escape_string($connection, $data[3]);
$item5 = mysqli_real_escape_string($connection, $data[4]);

$query = " INSERT INTO data(softwareID,districtID,statusID,date_approved,date_expired) VALUES('$item1', '$item2', '$item3', '$item4', '$item5') ";
$run_query = mysqli_query($connection, $query);
}
fclose($handle);
if($run_query == true){
echo "File Import Successful";
}else{
echo "File Import Failed";
}
}
}
}

//Close Connection
mysqli_close($connection);
?>

if you not seen any error message,you could echo something at every step to check the program stuck at where — nay, Jul 13 '21 at 02:45

Crimin4L · Answer 1 · 2021-07-13T03:30:24.467

Your current code would be vulnerable to SQL Injections, I suggest using prepared statements or parameterized queries and it would probably fix your problem also. Ill show you an example on how I connect to databases (using PDO):

# You can also set this up in a function, but this is how I use it as it works best for me.
# Also best if you keep this class (or function if you choose to change it) out of the `public_html` folder and just include/require it.
class DB extends PDO{
    public $connect;
    public function __construct(string $db_name){
        try{
            # Declare your mysql credentials 
            $cred = [
                "db_user" => "localhost",
                "db_user" => "root",
                "db_pass" => "xxx"
            ];
            $this->connect = new \PDO("mysql:host=".$cred['db_host'].";dbname=".$db_name, $cred['db_user'], $cred['db_pass']);
            $this->connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }catch(PDOException $e){
            # You can include the $e variable from above in the echo below to show the error, but I chose not to 
            # just incase someone is trying to attack your website. That error can give them a lot of information
            # about your SQL query, which can be very useful to an attacker; giving them an idea on how to formulate 
            # an injection (if possible).
            echo("Error");
        }
    }
}

# Start a connection to the declared database name
$db = new DB("database_name");

# Prepare the query but refrain from inputting variables directly into it. Instead put a :placeholder in its spot like so:
$queryPrep = $db->connect->prepare("INSERT INTO `data` (softwareID, districtID, statusID, date_approved, date_expired) VALUES (:softwareID, :districtID, :statusID, :date_approved, :date_expired)");

# You then bind your value(s) into your query like so (make sure to declare what datatype your variable is in the 3rd parameter):
$queryPrep->bindValue(':softwareID', $softwareID, PDO::PARAM_STR);
$queryPrep->bindValue(':districtID', $districtID, PDO::PARAM_STR);
$queryPrep->bindValue(':statusID', $statusID, PDO::PARAM_STR);
$queryPrep->bindValue(':date_approved', $date_approved, PDO::PARAM_INT);
$queryPrep->bindValue(':date_expired', $date_expired, PDO::PARAM_INT);
# Full list of PDO::PARAM_ Predefined Constants
# https://www.php.net/manual/en/pdo.constants.php

# Now you can finally execute your query
$queryPrep->execute();

# Check to see if any rows have been added to the database from the last SQL statement
if($queryPrep->rowCount() > 0) echo "true - Row Added";
else echo "false - Row Not Added";

I also have a function that I created a while back to parse CSV files/strings into an easier useable array (always assuming the first line will be the column names though):

function csvParse($input, $callback = false){
    $results = [];
    $raw_array = (is_file($input)) ? array_map('str_getcsv', file($input)) : array_map('str_getcsv', explode("\n", $input));
    $array = array_splice($raw_array, 1, count($raw_array));
    foreach($raw_array[0] as $c) $columns[] = $c;
    foreach($array as $key0 => $val0) foreach($val0 as $key1 => $val1) $results[$key0][$columns[$key1]] = $val1;

    if(is_callable($callback) && !empty($results)) call_user_func_array($callback, array($results));
    elseif(!empty($results)) return $results;
    else throw new Exception("Results Empty: Can not read the string or open file.");
}

# Can also be file location
$input = "animal,name,age\n
        goat,crimin4l,24\n
        deer,henry,11\n
        bear,teddy,15";

csvParse($input, function ($arr){
    print_r($arr);
});

Output:

Array
(
    [0] => Array
        (
            [animal] => goat
            [name] => crimin4l
            [age] => 24
        )

    [1] => Array
        (
            [animal] => deer
            [name] => henry
            [age] => 11
        )

    [2] => Array
        (
            [animal] => bear
            [name] => teddy
            [age] => 15
        )

)

You could put both of them together to complete your task successfully like so:

$db = new DB("database_name");

if(isset($_POST["csv_upload_btn"]) && !empty($_FILES['file'])){
    $file['base'] = basename($_FILES['file']['name']);
    $file['path'] = $_FILES['file']['tmp_name'];
    $file['mime'] = strtolower(pathinfo($file['base'], PATHINFO_EXTENSION));

    if($file['mime'] === "csv" || $file['mime'] === "txt"){
        csvParse($file['path'], function ($arr){
            # Start the $queryPrep here;
            # If for each CSV row you want to add a MySQL row then 
            # you will need a foreach loop to iterate through each 
            # of the array(s).
        });
    }else echo("Error: File must be .CSV or .TXT");
}

So, how would my final page look then? I am not as good at PHP as you. :) — lwaters, Jul 15 '21 at 23:26
@lwaters I cant tell you; I don't know the data you are working with or even the full code of your page? I provided you with everything you need to successfully complete your task. You can literally start building off of the last block of code I provided (as long as you have the class and function present or imported) — Crimin4L, Jul 16 '21 at 00:28

Upload CSV and Import into MySQL Database

1 Answers1