PHP Loops and MySQL

[THEK]

Hi,

 

I'm trying to create a script that imports a CSV file but gives the user 3 options:

 

1. If Exists Update

2. If Exists Keep Both

3. If Exists Ignore New

 

The keep both is easy, I just insert everything without checks.

 

However, the other 2 are a little bit tricky (in my mind anyway). I've been told I could create loops and that there isn't any easy way to do this with MySQL. I cannot use ON DUPLICATE KEY because the column that I'm checking cannot be unique (because I have the keep both option).

 

Is it efficient in PHP to create a loop that checks each row to see if it exists? I'm a little hesitant because the files could be big and I'm not sure how efficient PHP is with CPU and memory resources.

 

The App I'm making is designed to run in a business environment but it may run on servers that are handling more than one thing (not just web services).

 

If anyone could give me a few pointers I'd be most grateful.

 

Thanks.

[scootstah]

To update, just use REPLACE. It works the same way as INSERT but if the row already exists it just updates it.

[jcbones]

I cannot use ON DUPLICATE KEY because the column that I'm checking cannot be unique (because I have the keep both option).

 

To update, just use REPLACE. It works the same way as INSERT but if the row already exists it just updates it.

 

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

 

There should be no reason to keep duplicate data in a table, it will just eat up resources needlessly.  Coding the other two would just be deciding on a duplicate update, or just suppressing an error.

[Psycho]

If you could give us some examples and what you are trying to do and some sample data we may be able to help you better. For example, what are you using to consider two records as being the same. You can create uniqueness across rows! For example you can have a first_name column and a last_name column and then set the combination of those two naems as being unique. In that scenario you can have "Tom Smith", "Tom Williams" and "Dave Williams", but you can't ahve two records with the same first_name AND the same last_name. I suspect you probably want to create uniqueness across rows - then you would use ON DUPLICATE.

[kicken]

What I would do probably is first, build a SELECT query that will pull all existing records matching whatever was in the csv file.  Say your checking the Name column, then something like:

 

//Assume $csv is the data from the csv file in an array
$names = array();
foreach ($csv as $row){
   $names[] = mysql_real_escape_string($row['name']);
}

$sql = "SELECT Id, Name FROM table WHERE Name IN ('".implode("','", $names)."')";
$res=mysql_query($sql);

$existingData=array();
while ($row=mysql_fetch_array($res)){
   $existingData[$row['Name']] = $row['Id'];
}


//Now you can check if something exists by testing for it in $existingData
foreach ($csv as $row){
   $exists = isset($existingData[$row['name']]);
   if ($exists && $ignoreNew){  continue; }
   else if ($exists && $update){ 
        //do update query
   }
   else {
       //do insert query 
   }
}

[THEK]

What I would do probably is first, build a SELECT query that will pull all existing records matching whatever was in the csv file.  Say your checking the Name column, then something like:

 

//Assume $csv is the data from the csv file in an array
$names = array();
foreach ($csv as $row){
   $names[] = mysql_real_escape_string($row['name']);
}

$sql = "SELECT Id, Name FROM table WHERE Name IN ('".implode("','", $names)."')";
$res=mysql_query($sql);

$existingData=array();
while ($row=mysql_fetch_array($res)){
   $existingData[$row['Name']] = $row['Id'];
}


//Now you can check if something exists by testing for it in $existingData
foreach ($csv as $row){
   $exists = isset($existingData[$row['name']]);
   if ($exists && $ignoreNew){  continue; }
   else if ($exists && $update){ 
        //do update query
   }
   else {
       //do insert query 
   }
}

 

Thank you so much. After a bit of editing I now have a perfectly working CSV import system