By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,714 Members | 1,299 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,714 IT Pros & Developers. It's quick & easy.

Duplicate Records in DB when importing from CSV File

P: n/a
Hello,

The code below is used to grab each row from a CSV file (except the
first row) and insert it into a database, as long as it's not already
in the DB.

If I echo each row's data, it appears only once on the page. But, for
some reason it inserts each row into the database 3 times. The code
that echos the data, and the DB insertion code all appear within the
same for loop.

The database insertion code (below) works perfectly in another script.
It's just copied/pasted into the for loop.

Any ideas would be greatly appreciated! :-)

$row = 0;
$handle = fopen ("some-file.csv","r");
while ($data = fgetcsv ($handle, 1000, ",")) {
if($row == 0){
// set the varible array
$num = count ($data);
$csvNames = array();
for ($c=0; $c < $num; $c++) {
$csvNames[$c] = $data[$c];
eval("$" . $data[$c] . " = array();");
}
$row = 1;
}else{
$num = count ($data);
$row++;
for ($c=0; $c < $num; $c++) {
$buildEval = "$" . $csvNames[$c] . " = \"" . $data[$c] .
"\";";
eval($buildEval);
echo $buildEval . "<br>"; // this properly displays only one
row's data from the CSV file, then displays the next row in the CSV
file when it loops again.
// BUT FOR SOME REASON, THE DB CODE BELOW INSERTS EACH ROW FROM THE
CSV FILE 3 TIMES.

// Set variables with current date and time to be inserted into DB
later
$appDate = date('Y-m-d');
$appTime = date('H:i:s');

// Prepare the phone numbers for easy input into the database further
down this page.
$fHomePhoneCombined = $fHomePhone1 . "-" . $fHomePhone2 . "-" .
$fHomePhone3;
$fBusinessPhoneCombined = $fBusinessPhone1 . "-" . $fBusinessPhone2 .
"-" . $fBusinessPhone3;

// Connect to the database
$db = mysql_connect ("localhost", "username", "password");
mysql_select_db ("mydatabase");

// Check to see if the record is new,
$newRecord = 1;
$queryNewRecord = mysql_query("SELECT * FROM cust_test WHERE
first_name = '$fFirstName' AND last_name = '$fLastName' AND email =
'$fEmail'");
if (mysql_num_rows($queryNewRecord) > 0){
$newRecord = 0;
}

// If it's NOT a new record, insert the contents into cust_test_bad DB
// I'll insert this code later. It's not important for this test.
// Write the information to the customers table, if it's a new record.

if ($newRecord == 1){
$query = "INSERT INTO cust_test SET
application_date = '$appDate',
application_time = '$appTime',
first_name = '$fFirstName',
last_name = '$fLastName',
email = '$fEmail',
address = '$fAddress',
state = '$fState',
zip = '$fZip',
home_phone = '$fHomePhoneCombined',
best_time_to_call = '$fBestTime',
lead_buyer = '$lbuy'";
$result = mysql_query($query) or die('Failed because:
'.mysql_error());
}


} // end of for ($c=0; $c < $num; $c++)
}
}
fclose ($handle);
Jul 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Eric Linders wrote:
Hello,

The code below is used to grab each row from a CSV file (except the
first row) and insert it into a database, as long as it's not already
in the DB.

If I echo each row's data, it appears only once on the page. But, for
some reason it inserts each row into the database 3 times. The code
that echos the data, and the DB insertion code all appear within the
same for loop.

The database insertion code (below) works perfectly in another script.
It's just copied/pasted into the for loop.

Any ideas would be greatly appreciated! :-)

$row = 0;
$handle = fopen ("some-file.csv","r");
while ($data = fgetcsv ($handle, 1000, ",")) {
if($row == 0){
// set the varible array
$num = count ($data);
$csvNames = array();
for ($c=0; $c < $num; $c++) {
$csvNames[$c] = $data[$c];
eval("$" . $data[$c] . " = array();");
}
$row = 1;
}else{
$num = count ($data);
$row++;
for ($c=0; $c < $num; $c++) {
$buildEval = "$" . $csvNames[$c] . " = \"" . $data[$c] .
"\";";
eval($buildEval);
echo $buildEval . "<br>"; // this properly displays only one
row's data from the CSV file, then displays the next row in the CSV
file when it loops again.
// BUT FOR SOME REASON, THE DB CODE BELOW INSERTS EACH ROW FROM THE
CSV FILE 3 TIMES.

// Set variables with current date and time to be inserted into DB
later
$appDate = date('Y-m-d');
$appTime = date('H:i:s');

// Prepare the phone numbers for easy input into the database further
down this page.
$fHomePhoneCombined = $fHomePhone1 . "-" . $fHomePhone2 . "-" .
$fHomePhone3;
$fBusinessPhoneCombined = $fBusinessPhone1 . "-" . $fBusinessPhone2 .
"-" . $fBusinessPhone3;

// Connect to the database
$db = mysql_connect ("localhost", "username", "password");
mysql_select_db ("mydatabase");

// Check to see if the record is new,
$newRecord = 1;
$queryNewRecord = mysql_query("SELECT * FROM cust_test WHERE
first_name = '$fFirstName' AND last_name = '$fLastName' AND email =
'$fEmail'");
if (mysql_num_rows($queryNewRecord) > 0){
$newRecord = 0;
}

// If it's NOT a new record, insert the contents into cust_test_bad DB
// I'll insert this code later. It's not important for this test.
// Write the information to the customers table, if it's a new record.

if ($newRecord == 1){
$query = "INSERT INTO cust_test SET
application_date = '$appDate',
application_time = '$appTime',
first_name = '$fFirstName',
last_name = '$fLastName',
email = '$fEmail',
address = '$fAddress',
state = '$fState',
zip = '$fZip',
home_phone = '$fHomePhoneCombined',
best_time_to_call = '$fBestTime',
lead_buyer = '$lbuy'";
$result = mysql_query($query) or die('Failed because:
'.mysql_error());
}


} // end of for ($c=0; $c < $num; $c++)
}
}
fclose ($handle);

I got so hacked off with this that I made the import a two phase task.
First I inserted into a temp table the same shape, then I inserted into
the real one using a select distinct where not exists () from the temp
table.

Also had the advantage that the data in the live tables was out of step
for about a second, which is ok when you're driving a website ( well, I
reckon anyway ).

Steve.
Jul 17 '05 #2

P: n/a
Steve <Th*****@Aint.valid> wrote in message
<SNIP>

fclose ($handle);
I got so hacked off with this that I made the import a two phase task.
First I inserted into a temp table the same shape, then I inserted into
the real one using a select distinct where not exists () from the temp
table.

Also had the advantage that the data in the live tables was out of step
for about a second, which is ok when you're driving a website ( well, I
reckon anyway ).

Steve.

You would think that my check of code that checks if it's a new record
would accomplish the task -- at least it does in my other page where
this code is found. So strange! I just do NOT get why it's happening.
Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.