PHP script to insert into mysql

[dugindog]

I am having trouble getting a script to work reading a .csv file and inserting it line by line to the database.

error:

csv file contains all fields for upload, and should work, I am unsure what the issue is.

if someone could look thru it it would be greatly appreciated.

SQL create statement:

# CREATE statement for poll_contact
CREATE TABLE `_contact` (
  `fname` varchar(250) NOT NULL default '',
  `lname` varchar(250) NOT NULL default '',
  `email` varchar(250) NOT NULL default '',
  `phone` varchar(250) NOT NULL default '',
  `saddr` varchar(250) NOT NULL default '',
  `city` varchar(250) NOT NULL default '',
  `state` varchar(250) NOT NULL default '',
  `pcode` varchar(250) NOT NULL default '',
  `ukey` int(11) NOT NULL auto_increment,
  `a1` tinytext,
  `a2` tinytext,
  `a3` tinytext,
  `a4` tinytext,
  `a5` tinytext,
  `a6` tinytext,
  `a7` tinytext,
  `a8` tinytext,
  `a9` tinytext,
  `a10` tinytext,
  `a11` tinytext,
  `a12` tinytext,
  `a13` tinytext,
  `a14` tinytext,
  `a15` tinytext,
  `a16` tinytext,
  `a17` tinytext,
  `a18` tinytext,
  `a19` tinytext,
  `a20` tinytext,
  `a21` tinytext,
  `a22` tinytext,
  `a23` tinytext,
  `a24` tinytext,
  `a25` tinytext,
  `a26` tinytext,
  `a27` tinytext,
  `a28` tinytext,
  `a29` tinytext,
  `a30` tinytext,
  `a31` tinytext,
  PRIMARY KEY  (`ukey`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

php code for script (as I have it):

<?


// SCRIPT TO IMPORT CVS TO SQL


// Database variables

$dbserver = "";
$dbuser = "";
$dbpass = "";
$dbname = "";
$tablename = "";  


// Database connection string, and start connection to database.

$dbh=@mysql_connect ($dbserver, $dbuser, $dbpass) or die ('I cannot connect to the database because: ' . mysql_error());

if (!@mysql_select_db ($dbname)) {die('could not connect to database!'); }

$row = 1;

// Opening the CSV file

$handle = fopen ("./test.CSV","r");

//  max value per line 1000

while ($data = fgetcsv ($handle, 1000, ",")) {


// insert into the table

$sql = "INSERT INTO `" . $tablename . "` (`fname`, `lname`, `email`, `phone`, `saddr`, `city`, `state`, `pcode`, `a1`, `a2`, `a3`, `a4`, `a5`, `a6`, `a7`, `a8`, `a9`, `a10`, `a11`, `a12`, `a13`, `a14`, `a15`, `a16`, `a17`, `a18`, `a19`, `a20`, `a21`, `a22`, `a23`, `a24`, `a25`, `a26`, `a27`, `a28`, `a29`, `a30`, `a31`) VALUES ('" . $data[1] . "', '" . $data[2] .  "', '" . $data[3] ."', '" . $data[4] . "', '" . $data[5] . "', '" . $data[6] . "', '" . $data[7] . "', '" . $data[8]  . "', '" . $data[9] . "', '" . $data[10] . "', '" . $data[11] . "', '" . $data[12] . "', '" . $data[13] . "', '" . $data[14] . "', '" . $data[15] . "', '" . $data[16] . "', '" . $data[17] . "', '" . $data[18] . "', '" . $data[19] . "', '" . $data[20] . "', '" . $data[21] . "', '" . $data[22] . "', '" . $data[23] . "', '" . $data[24] . "', '" . $data[25] . "', '" . $data[26] . "', '" . $data[27] . "', '" . $data[28] . "', '" . $data[29] . "', '" . $data[30] . "', '" . $data[31] . "', '" . $data[32] . "', '" . $data[33] . "', '" . $data[34] . "', '" . $data[35] . "', '" . $data[36] . "', '" . $data[37] . "', '" . $data[38] . "', '" . $data[39] . "');";

//echo "-->$sql<--";

mysql_query($sql) or die ('I cannot insert the query because: ' . mysql_error());

$result = mysql_query($query) or die("Invalid query: " . mysql_error().__LINE__.__FILE__);


$row++;

}

fclose ($handle);

echo "<BR><BR>uploaded<BR><BR>";

?>

attached is a copy of the php script, and of the csv file in a zip

any recommendations would be greatly appreciated.

Eric

[Roger_Ramjet]

Try using load data infile for this. It will read the whole file into your table in one go.

[dugindog]

most uploads will only be about 100 records or less at a time.

also I am planning to add in the ability to compare whether the firstname&lastname of each record is already in the database, this is why I would like the ability to run it from a php script. unless you / someone knows a way to query and insert only the records that are currently not in the table & if they are already in the database then I want to update the record (idea it to keep the duplicates out).

if this makes sense.

[Roger_Ramjet]

Load the data into a work table then use either REPLACE or ON DUPLICATE KEY UPDATE syntax to insert from work table to main table. If you just want to prevent duplicates then a unique index will bounce duplicate values off.

Which you choose depends on what is required for your app.

Always a good idea to load data into a work table first so that it can be validated.