CODEWITHSUNDEEP
phpmysqlcsvupload
Simon Jespersen
Simon Jespersen

Reputation: 11

Upload CSV to MySQL with PHP script. Issues

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 :)

Upvotes: 0

Views: 1980

Answers (2)

Jorge Y. C. Rodriguez
Jorge Y. C. Rodriguez

Reputation: 3449

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...";
    }
?>

Upvotes: 0

Dave
Dave

Reputation: 3288

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";
}

Upvotes: 1

Related Questions