HTML PHP Form Upload to PHPMyAdmin

Question

I am trying to pass users data through internal html form via Uploading a .csv with 1st row as header, but facing 2 issues:

'hashedpwords.csv' file is generated successfully on same path, but column name of Passwords gets hashed too, even though in code there's a skip first line of file.
data is not being submitted at all to my database table, I couldn't figure out why.

code below:

PHP:

<?php

require_once 'PHP/dbinfo.php';
$dbc   = new mysqli($hn,$user,$pass,$db) or die("Unable to connect");

$has_title_row = true;
$not_done = array();
if ($_SERVER['REQUEST_METHOD'] == 'POST'){
    if(is_uploaded_file($_FILES['csvfile']['tmp_name'])){
        $filename = basename($_FILES['csvfile']['name']);
        
        if(substr($filename, -3) == 'csv'){
            $tmpfile = $_FILES['csvfile']['tmp_name'];
            if (($fh = fopen($tmpfile, "r")) !== FALSE) {
                $i = 0;
                while (($items = fgetcsv($fh, 1000000, ",")) !== FALSE) {
                    if($has_title_row === true && $i == 0){ // skip the first row if there is a tile row in CSV file
                        $i++;
                        continue;
                    }
                    
                    set_time_limit(0);

                    $infile = $filename;
                    $myfile = "hashedpwords.csv";

                    $reader = fopen($infile, 'r');
                    $writer = fopen($myfile, 'w');
                    $buffer = '';

                    while ($line = fgetcsv($reader)) {
                        $line[8] = password_hash($line[8], PASSWORD_DEFAULT);

                        $buffer .= implode(',', $line) . "\n";

                        if (strlen($buffer) > 1024) {
                            fwrite($writer, $buffer);
                            $buffer = '';
                        }
                    }

                    fwrite($writer, $buffer);
                    fclose($reader);
                    fclose($writer);
                    $sql = "LOAD DATA LOCAL INFILE '".$myfile."'
                    INTO TABLE usersdata
                    FIELDS TERMINATED BY ','
                    OPTIONALLY ENCLOSED BY '\"' 
                    LINES TERMINATED BY '\n' 
                    (depid, pid, authid, mainrole, firstname, lastname, username, userinitials, password, mail, phonenumber)";
                    
                    $result = $dbc->query($sql);
                    
                    echo "Success";

                    if (!$result) die("Fatal Error");

                    if (mysqli_query($dbc, $sql)){
                        echo "New record created successfully";
                        } else {
                        echo "Error: " . $sql . "<br>" . mysqli_error($dbc);
                        }
                                $i++;
                            }
                        }
                        // if there are any not done records found:
                        if(!empty($not_done)){
                            echo "<strong>There are some records could not be inserted</strong><br />";
                            print_r($not_done);
                        }
        }
        else{
            die('Invalid file format uploaded. Please upload CSV.');
        }
    }
    else{
        die('Please upload a CSV file.');
    }
}

HTML:

    <!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Users Data Upload</title>
</head>
<body>
    <form enctype="multipart/form-data" action="userdatauploadfunction.php" method="post" id="add-users"> 
        <table cellpadding="5" cellspacing="0" width="500" border="0"> 
            <tr> 
                <td class="width"><label for="image">Upload CSV file : </label></td> 
                <td><input type="file" name="csvfile" id="csvfile" value=""/></td> 
                <td><input type="submit" name="uploadCSV" value="Upload" /></td> 
            </tr> 
        </table> 
    </form>
</body>
</html>

What have you done to debug this yourself, and where is the script failing? Can you confirm `if ($_SERVER['REQUEST_METHOD'] == 'POST')` is true, as expected? And confirm `if(is_uploaded_file($_FILES['csvfile']['tmp_name']))` is true? Etc. You have many nested `if`s, any of which might fail and stop further execution. Narrowing down the scope of where this isn't working will be a great first step to debugging it. — WOUNDEDStevenJones, Nov 07 '22 at 20:11
I mentioned in my description that there's an output file but the outputs are not as expected, and it's not submitted to the DB. but to answer your question, yes POST is true i tried it before with simple echo "success"; separately. as for the file, the file hashpwords.csv gets created from the original file uploaded users.csv with same data & password hashed. all of these signs say the code is almost doing the job. I just can't debug/modify any further because it's not my area of expertise and I need to perform certain task urgently. thanks for taking the time to reply. — Samy, Nov 07 '22 at 20:40
Do https://stackoverflow.com/questions/31793756/mysql-php-load-data-infile, https://stackoverflow.com/questions/18915104/php-import-csv-file-to-mysql-database-using-load-data-infile, or https://stackoverflow.com/questions/14348257/using-mysql-load-statment-in-php-fails-but-doing-it-via-command-line-works help? It might be an issue with your `mysqli` connection. — WOUNDEDStevenJones, Nov 07 '22 at 21:13
thanks for the subtle help, Steven. I tried one solution after debugging more, I found the LOAD DATA... was forbidden and generating an error, I tried the solution in the first link and I get the same error per # of records to be inserted, error: mysqli_query(): LOAD DATA LOCAL INFILE forbidden, LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile below is my connection modification: require_once 'PHP/dbinfo.php'; $dbc = mysqli_init(); mysqli_options($dbc, MYSQLI_OPT_LOCAL_INFILE, true); mysqli_real_connect($dbc,$hn,$user,$pass,$db); more subtle hints please :D — Samy, Nov 07 '22 at 22:51
also php.ini I checked and it was set to mysqli.allow_local_infile = On — Samy, Nov 07 '22 at 22:53
after lots of trials and errors and lots of debugging I got it working. thanks for the subtle help again, I appreciate it. — Samy, Nov 08 '22 at 10:06

score 0 · Accepted Answer · answered Nov 08 '22 at 10:09

I fixed the code and it runs properly now. Modified code is below:

PHP:

<?php
$has_title_row = true;
$not_done = array();
if ($_SERVER['REQUEST_METHOD'] == 'POST'){
    //echo "success";
    if(is_uploaded_file($_FILES['csvfile']['tmp_name'])){
        //echo "success";
        $filename = basename($_FILES['csvfile']['name']);        
        if(substr($filename, -3) == 'csv'){
            //echo "success";
            $tmpfile = $_FILES['csvfile']['tmp_name'];
            if (($fh = fopen($tmpfile, "r")) !== FALSE) {
                //echo "success";
                $i = 0;
                set_time_limit(0);

                $infile = $filename;
                $myfile = "hashedpwords.csv";

                $reader = fopen($infile, 'r');
                $writer = fopen($myfile, 'w');
                $buffer = '';
                    
                while ($line = fgetcsv($reader)) {
                    $line[9] = password_hash($line[9], PASSWORD_DEFAULT);

                    $buffer .= implode(',', $line) . "\n";

                    if (strlen($buffer) > 1024) {
                        fwrite($writer, $buffer);
                        $buffer = '';
                    }
                }                               

                fwrite($writer, $buffer);
                fclose($reader);
                fclose($writer);

                $sql = "LOAD DATA LOCAL INFILE '".$myfile."'
                INTO TABLE usersdata
                FIELDS TERMINATED BY ','
                OPTIONALLY ENCLOSED BY '\"' 
                LINES TERMINATED BY '\n' 
                IGNORE 1 LINES
                (uid, depid, pid, authid, mainrole, firstname, lastname, username, userinitials, password, mail, phonenumber)";
                    
                require_once 'PHP/dbinfo.php';
                $dbc   = new mysqli($hn,$user,$pass,$db) or die("Unable to connect");
                mysqli_options($dbc, MYSQLI_OPT_LOCAL_INFILE, true);
                $result = mysqli_query($dbc, $sql);

                //echo "Success";
            }
                // if there are any not done records found:
                if(!empty($not_done)){
                    echo "<strong>There are some records could not be inserted</strong><br />";
                    print_r($not_done);
                }
        }
        else{
            die('Invalid file format uploaded. Please upload CSV.');
        }
    }
    else{
        die('Please upload a CSV file.');
    }
}

How about pointing out what parts needed fixing. – Rick James Nov 08 '22 at 22:25 — Rick James, Nov 08 '22 at 22:25

HTML PHP Form Upload to PHPMyAdmin

1 Answers1