Upload CSV to MySQL with PHP script. Issues

Question

Im having a bit of trouble getting my PHP script working.

What I'm trying to accomplish: On a subpage I want a very simple CSV fileupload to a MySQL database. It needs to either DROP or TRUNCATE a table and either CREATE or INSERT INTO, depending on previous solution, some data within this CSV file.

What my problem is now: I'm quite a beginner with PHP and MySQL, and as such are having trouble figuring out why my script is not working.

My code:

The PHP:

$connect = mysql_connect("host","user","pass");  
mysql_select_db("database",$connect); //select the table 

if ($_FILES[csv][size] > 0) { 

$file = $_FILES[csv][tmp_name]; 
$handle = fopen($file,"r"); 

mysql_query (" 
CREATE TABLE IF NOT EXISTS `database`.`table` (
`item_number` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_danish_ci NOT NULL ,
`item_desq` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_danish_ci NOT NULL ,
`item_img_path` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_danish_ci NULL ,
`item_id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (  `item_id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_danish_ci                      
        ");

do { 
if ($data[0]) { 
   mysql_query("
                INSERT INTO strand_items1 (item_number, item_desq, item_img_path) 
                VALUES 
                      ( 
                      '".addslashes($data[0])."', 
                      '".addslashes($data[1])."',
          '".addslashes($data[2])."
                      ) 
              "); 
} 
} while ($data = fgetcsv($handle,1000,";","\"")); 

header('Location: import_old.php?success=1'); die; 
}

The HTML:

<body>
    <form action="" method="post" enctype="multipart/form-data" name="form1" id="form1">
        Choose your file: <br />
        <input name="csv" type="file" id="csv" />
        <input type="submit" name="Submit" value="Submit" />
    </form>
</body>

The CSV: Your ordinary line in the CSV looks like this

"13371337";"Some description of the item";"NULL";

And now, the results: From this, I get a "succesfull" flow, yet no data in the table, and from what I can guess, an infinite loop of some sort on the database, since it creates an increasing amount of empty entries in the table with only the item_id auto-incrementing on and on. Where am I going wrong and is there an easy way to fix this?

This is my first question on here, and I hope I meet all requirements. (To point out my newb-ness; question was first posted on meta.stackoverflow.com ;) ) If not, please be patient - I will monitor this question closely and answer quickly on any replies and requiests.

Thank you in advance :)

You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). `addslashes` is insufficient to protect against [SQL injection attacks](http://bobby-tables.com/) (and a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from them). — Quentin, Jun 18 '13 at 09:07
Thank you for your reply Quentin. I am aware of my lack of supported SQL and PHP. However, since this is not my field of competence, I have not, and will not be able to update my knowledge on these topics. Therefore I wish that you ignore all security and old-school function-uses. I (and my employer) have decided not to hire a developer and hence are not concerned about these issues :-) — Simon Jespersen, Jun 18 '13 at 09:12

score 1 · Answer 1 · answered Jun 18 '13 at 09:13

Just truncate your table every time its far easier.

This is a basic importer I wrote last night for a friend its only simplistic but you should get the idea. Obviously I use mysqli here so you need to change that bit to suit yours and you also have to add in the truncate at the top (just after the starting import bit will be fine.

if (isset($_POST['action']) && $_POST['action']=="beginimport") {
    echo "<h4>Starting Import</h4><br />";
    // Ignore user abort and expand time limit 
    //ignore_user_abort(true);
    set_time_limit(60);
    $result = @move_uploaded_file($_FILES['clientimport']['tmp_name'], getcwd()."/tmp/siccodes.csv");
    if(empty($result)){
        echo "<span style=\"font-weight:bold;color:red;\">There was an error moving the uploaded file</span><br />";
    } else {
        echo "<span style=\"font-weight:bold;color:green;\">Temp file created begining data parse<br /><br /></span>";
        if (($handle = fopen(getcwd()."/tmp/siccodes.csv", "r")) !== FALSE) {
            $row = 0;
            while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {  
                $client = $db->queryUniqueObject("SELECT ClientID FROM tblcreditchecks WHERE RegNumber='".mysqli_real_escape_string($db->mysqli,$data[0])."'",ENABLE_DEBUG);
                if ($data[2]>0) {
                    $db->execute("UPDATE tblcreditchecks SET TurnOver='".mysqli_real_escape_string($db->mysqli,$data[2])."' WHERE RegNumber='".mysqli_real_escape_string($db->mysqli,$data[0])."'",ENABLE_DEBUG);
                } else {
                    echo "Turnover fail - ".$data[0];
                }
                if ($client->ClientID>0) {
                    $db->execute("UPDATE tblclients SET SICCodes='".mysqli_real_escape_string($db->mysqli,$data[1])."' WHERE ID=".$client->ClientID,ENABLE_DEBUG);
                } else {
                    echo " SIC fail - ".$data[0];
                }

                echo " - IMPORTED - ".$data[0]."<br />";
                fcflush();
                set_time_limit(60); // reset timer on loop
            } 
        }
        fclose($handle);
        unlink(getcwd()."/tmp/siccodes.csv");
    }
    echo "COMPLETE";
}

Thanks for your answer Dave. I'm trying to make it work as we speak. Will be back with feedback :) — Simon Jespersen, Jun 18 '13 at 09:39
HINT looks like you're using a semicolon separator so change the fgetcsv($handle, 10000, ";") — Dave, Jun 18 '13 at 12:17

score 0 · Accepted Answer · answered Jun 18 '13 at 12:43

you are using ; separator so fix that i with something like this it should work, it is not the best way to do it, but thanks @Dave for the hint in how to do it...

if (isset($_POST['process'])) 
    {
        echo "<h2>Importing...</h2>";

        set_time_limit(60);
        $fileResult = move_uploaded_file($_FILES['csv']['tmp_name'], getcwd()."/tmp/somefile.csv");
        if(empty($fileResult))
        {
            echo "<h1>Some error</h1>";
        }
        else
        {
            if(($handle = fopen(getcwd()."/tmp/somefile.csv", "r")) !== false)
            {
                require_once 'dbController.php';
                $db_controller = new dbController();
                $db_connection = $db_controller->connectToDb();
                if($db_controller->conections_check == true)
                {   
                    $truncateTable = "
                        truncate table strand_items3
                    ";
                    $truncateTable = mysql_query($truncateTable);
                    $row = 1;
                    while (($data = fgetcsv($handle, 1000, ";")) !== false) 
                    {
                      $itiemId = $data[0];
                      $itemDescription = $data[1];

                      $insertQuery = "
                        INSERT INTO `tablename` (`value`, `value`, `value`) 
                        VALUES ('$itiemId', '$itemDescription', NULL)
                      ";

                       $insertQuery = mysql_query($insertQuery);
                    }
                }
                else
                {
                    echo "Error connecting to the server";
                }

            }
            fclose($handle);
            unlink(getcwd()."/tmp/somefile.csv");
        }
        echo "Done...";
    }
?>

Upload CSV to MySQL with PHP script. Issues

2 Answers2