...

View Full Version : upload csv file and import into mysql



steamngn
06-09-2007, 08:46 PM
Me again!:p
I have this code to import a csv file into mysql:

<?php
include "includes/stconfig.php";
if(isset($_POST['submit']))
{
$filename=$_POST['filename'];
$handle = fopen("$filename", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{

$import="INSERT into table1(test1,test2,test3) values('$data[0]','$data[1]','$data[2]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
print "Import done";

}
else
{

print "<form action='csv_importer.php' method='post'>";
print "Type file name to import:<br>";
print "<input type='file' name='filename' size='20'><br>";
print "<input type='submit' name='submit' value='submit'></form>";
}
?>
What I need is to be able to select a LOCAL file, have it uploaded to a temp directory and imported into mysql, all from a web page. I don't dare turn this loose with myadmin, as the users will be nubes of the most extreme!
Anyway, is there a simple way to get what I have working?
Andy

Ed Barnard
06-09-2007, 09:20 PM
The php manual pages provide examples of what you need. Check the page for is_uploaded_file():

http://us2.php.net/manual/en/function.is-uploaded-file.php

and confinue from there to the link on handling file uploads (chapter 38) for complete examples on getting to the uploaded file. Since you're dealing with noobs of the most extreme, you really need to do some serious validation of the incoming data before slamming it into your database. There is a PEAR module for dealing with incoming CSV data. At a minimum, run that data through the mysql escape function after replacing left angle bracket '<' with the html entity '&lt;'. (Don't know if they get stripped out of this posting.)

If you write your code assuming some hacker will use this web page to attack your database, you won't be disappointed! Clean and validate your data prior to insertion.

Ed Barnard

steamngn
06-11-2007, 04:02 PM
Hey Ed,
yeah, I know it needs to be validated to death; I'm just starting this whole mess. I fixed the upload so I can select the local file, but there are still issues:

))
{
if ($_FILES["filename"]["error"] > 0)
{
echo "Error: " . $_FILES["filename"]["error"] . "<br />";
}
else
{
echo "Upload: " . $_FILES["filename"]["name"] . "<br />";
echo "Type: " . $_FILES["filename"]["type"] . "<br />";
echo "Size: " . ($_FILES["filename"]["size"] / 1024) . " Kb<br />";
echo "Stored in: " . $_FILES["filename"]["tmp_name"];

$length = 1000;
$array = $_FILES["filename"]["tmp_name"];
for($i=0;$i<count($array);$i++)
{
if ($length < strlen($array[$i]))
{
$length = strlen($array[$i]);
}
}
unset($array);

$handle = fopen("$filename", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)

{
$import="INSERT into table1(test1,test2,test3,test4,test5) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
mysql_query($import) or die(mysql_error());


}
}
fclose($handle);

print "Import done";
echo $filename;

}
else
{

print "<form enctype='multipart/form-data' action='csv_importer2.php' method='post'>";
print "Type file name to import:<br>";
print "<input type='file' name='filename' size='20'><br>";
print "<input type='submit' name='submit' value='submit'></form>";
}
?>
This will allow the local file to be uploaded and inserted.
NOW...
some of the array rows will be actual HTML code, and I would like to store them as-is. Plus, all of the array fields should be escaped. isn't there a way to do this using array_walk or something like that?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum