Duplicate Records in DB when importing from CSV File

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

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.

Steve <ThisOne@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.