Csv upload to mysql

[rengaraj]

Hi i am a middle level php prgrammer i want to upload csv file to mysql and all rows have to store in mysql using php. Do u know any script sites and any ideas for.

Thanks for all replys.

[longneck]

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

[rengaraj]

I am using the following code to upload csv file to mysql using php but i am getting error the following error.

Error: Query failed: Column count doesn't match value count at row 1

But in csv file i used two columns name and email only, and i want to store these two columns only in mysql table

<?php

//we need function to get the csv
function getcsv($filename, $delim =","){

$row = 0;
$dump = array(); //create new array for hold the data csv

$f = fopen ($filename,"r");
$size = filesize($filename)+1;
while ($data = fgetcsv($f, $size, $delim)) {
$dump[$row] = $data; //put the data to array
//echo $data[1]."<br>";
$row++;
}
fclose ($f);

return $dump;
}

//this function for insert data to csv
function makeINSERTS($text, $table){
global $linkdata; //make global database connection
$insert = array(); //make array for hold data insert
$i = 0;

while (list($key, $val) = @each($text)){

$insert[$i] = "INSERT INTO `csv` ( `id` , `email` , `name` )
VALUES ('', '";$insert[$i] .= implode("', '", $val);$insert[$i] .= "')\n";
$result = mysql_query($insert[$i], $linkdata) or die('Query failed: ' . mysql_error());
$i++;
}

return $insert;

}

//this verify is the file csv upload
if ($_POST["submit"]=="submit") {
if (is_uploaded_file($_FILES['userfile']['tmp_name'])) {

$file=$_FILES['userfile']['tmp_name'];
$linkdata = mysql_connect("localhost", "root", "")
or die('Could not connect: ' . mysql_error());

mysql_select_db("csv") or die('Could not select database');


$CSVarray = getcsv($file);
$CSVarray = makeINSERTS($CSVarray, "$tbl");


} else {
echo "error upload file";
exit;
}


} else {
//form upload
echo "
<FORM ENCTYPE=\"multipart/form-data\" ACTION=\"".$_SERVER['PHP_SELF']."\" METHOD=POST>
Upload this file: <INPUT NAME=\"userfile\" TYPE=\"file\">
<INPUT TYPE=\"submit\" VALUE=\"submit\" name=submit></FORM>
";
}
?>

Also let me know if any code like this to upload csv file to mysql using php.


Thanks for reply

[r937]

Error: Query failed: Column count doesn't match value count at row 1

at the point you get this error, echo the exact query that was sent to mysql, and paste it here

[rengaraj]

Result when i echo insert query

INSERT INTO `csv` ( `id` , `email` , `name` ) VALUES ('', 'Query failed: Column count doesn't match value count at row 1

[r937]

well, something is wrong with your php code, then, because it looks like you're trying to insert the error message itself!!!

moving thread to php forum

[gareth_alexander]

Is it something to do with trying to insert 2 columns from your csv file into 3 columns in your mysql table.

If you make sure that your

id

field is set to

auto_increment

in your Mysql table, then change the line

$insert[$i] = "INSERT INTO `csv` ( `id` , `email` , `name` ) 

to

$insert[$i] = "INSERT INTO `csv` ( `email` , `name` ) 

[Raju Gautam]

Yes I also doubt in the number of columns you have in your CSV file. Check whether your CSV have only two fields 'Email' and 'Name' to fit your current code. Otherwise this seems pretty much working.