mysql - importing multiple rows of data at once

[Drongo_III]

Hi Guys

 

Just need some advice to go in the right direction.

 

I'm working on a csv upload script (part of a bigger thing i'm building), so i read in the csv to a multipdimensional array and then build a query that inputs all rows in one query - i read this is the most efficient way to import multiple rows of data at once(rather than multiple insert statements). Just for illustration here's the code i use to build the query so you understand what i'm on about:

 

$sql = "INSERT INTO teams (company, teamname, teamnum, amountraised, country, president) VALUES ";

	// $rows is a count of the rows in the csv

	for($i=1; $i<$rows; $i++){

		$sql.="('{$myarray[$i][0]}','{$myarray[$i][1]}','{$myarray[$i][2]}','{$myarray[$i][3]}','{$myarray[$i][4]}','{$myarray[$i][5]}')";
		echo $i . "<br/>";

		if($i >= 1 && $i < $rows - 1)
			{
				$sql.= ",";
			}
	}

 

Anyway, the issue is that one of the fields("teamnum") needs to be unique - so i've set this as unique on the table in mysql.

 

But when i run my query it doesn't import anything if one of the records isn't unique. What i really want is for it to import the ones it can and catch the ones it cant import to present to the user.

 

So my question is - to acheive the above would i need to rewrite the query so that it inserts each row one at a time, instead of all together?

 

Or can someone point me in the right direction for a better solution?

 

Probably something very simple i've missed i am sure...

 

Thanks chaps!

 

 

[Psycho]

Well, what DO you want to happen for the records where the value is not unique? Do you want to update the record with the new values or skip it?

 

By, the way, your loop is more complicated than it needs to be. Simply store the "values" for the query in an array and do an implode at the end to add the commas. Plus, if your values are in an array, you should be using a foreach() loop, not a for() loop

 

//Process the array into insert values
$values = array();
foreach($myarray as $record)
{
    $values[] ="('{$record[0]}','{$record[1]}','{$record[2]}','{$record[3]}','{$record[4]}','{$record[5]}')";
}

//Create query to IGNORE duplicates on unique field
$sql = "INSERT INTO teams
            (company, teamname, teamnum, amountraised, country, president)
        VALUES " . implode(', ', $values) . "
        ON DUPLICATE KEY IGNORE";

//Create query to UPDAtE duplicates on unique field
//Include any fields you want updated in a comma separated list
$sql = "INSERT INTO teams
            (company, teamname, teamnum, amountraised, country, president)
        VALUES " . implode(', ', $values) . "
        ON DUPLICATE KEY UPDATE
            amountraised=VALUES(amountraised)" ;

[Drongo_III]

Hi Psycho

 

That helps a lot.  And now you've tidied up the query i see how it could have been more efficient!

 

Ideally i would want to pass the failed rows to an array so i can output it to the user.

 

So would that require that each row is inserted one at a time? Rather than inserting them all as one statement?

 

 

Well, what DO you want to happen for the records where the value is not unique? Do you want to update the record with the new values or skip it?

 

By, the way, your loop is more complicated than it needs to be. Simply store the "values" for the query in an array and do an implode at the end to add the commas. Plus, if your values are in an array, you should be using a foreach() loop, not a for() loop

 

//Process the array into insert values
$values = array();
foreach($myarray as $record)
{
    $values[] ="('{$record[0]}','{$record[1]}','{$record[2]}','{$record[3]}','{$record[4]}','{$record[5]}')";
}

//Create query to IGNORE duplicates on unique field
$sql = "INSERT INTO teams
            (company, teamname, teamnum, amountraised, country, president)
        VALUES " . implode(', ', $values) . "
        ON DUPLICATE KEY IGNORE";

//Create query to UPDAtE duplicates on unique field
//Include any fields you want updated in a comma separated list
$sql = "INSERT INTO teams
            (company, teamname, teamnum, amountraised, country, president)
        VALUES " . implode(', ', $values) . "
        ON DUPLICATE KEY UPDATE
            amountraised=VALUES(amountraised)" ;

[Psycho]

Ideally i would want to pass the failed rows to an array so i can output it to the user.

 

So would that require that each row is inserted one at a time? Rather than inserting them all as one statement?

 

You know, I fond it frustrating when I provide a solution based upon the requirements given only to have them changed

 

No, it would not require you to insert them individually, it would only require two queries. First run one qeury to find the duplicate records, then run a second query to insert the new records.

 

I don't think you ever stated what the unique field it, I will assume it is the 'teamnum' field. Anyway, if you need to determine which ones are duplicates, then you don't even need to use one of the ON DUPLICATE KEY methods. None of this is tested, but the process should work

//Process the array into insert values
$values = array(); //Array to hold insert values
$teamNums = array(); //Array to hold team numbers

//Process data into the temp arrays
foreach($myarray as $record)
{
    $teamNums[] = $record[2];
    $values[$record[2]] ="('{$record[0]}','{$record[1]}','{$record[2]}','{$record[3]}','{$record[4]}','{$record[5]}')";
}

//Run query to find existing duplicates
$query = "SELECT `teamnum`
          FROM `teams'
          WHERE `teamnum` IN (" . implode(',', $teamNums) . ")";
$result = mysql_query($query);

//process existing team nums into temp array
$existingTeamNums = array();
while($row = mysql_fetch_assoc($result))
{
    $existingTeamNums[] = $row['teamnum'];
}

//Calculate array of existing records
$existingRecords = array_diff_keys($values, $existingTeamNums);

//Calculate array of new records to insert
$insertRecords = array_diff_keys($values, $existingTeamNums);

//Create query to insert new records
$query = "INSERT INTO teams
              (company, teamname, teamnum, amountraised, country, president)
          VALUES " . implode(', ', $insertRecords);

 

 

[Drongo_III]

Hi Psycho

 

Thanks for taking the time to provide that!

 

It wasn't my intention to mislead...i was more looking for some advice on the best way to do this (oringally) but your going the extra mile and providing the code is obviously extremely welcome and it helps loads to see the process used by someone who knows their onions

 

Thank you.

 

Drongo

 

Ideally i would want to pass the failed rows to an array so i can output it to the user.

 

So would that require that each row is inserted one at a time? Rather than inserting them all as one statement?

 

You know, I fond it frustrating when I provide a solution based upon the requirements given only to have them changed

 

No, it would not require you to insert them individually, it would only require two queries. First run one qeury to find the duplicate records, then run a second query to insert the new records.

 

I don't think you ever stated what the unique field it, I will assume it is the 'teamnum' field. Anyway, if you need to determine which ones are duplicates, then you don't even need to use one of the ON DUPLICATE KEY methods. None of this is tested, but the process should work

//Process the array into insert values
$values = array(); //Array to hold insert values
$teamNums = array(); //Array to hold team numbers

//Process data into the temp arrays
foreach($myarray as $record)
{
    $teamNums[] = $record[2];
    $values[$record[2]] ="('{$record[0]}','{$record[1]}','{$record[2]}','{$record[3]}','{$record[4]}','{$record[5]}')";
}

//Run query to find existing duplicates
$query = "SELECT `teamnum`
          FROM `teams'
          WHERE `teamnum` IN (" . implode(',', $teamNums) . ")";
$result = mysql_query($query);

//process existing team nums into temp array
$existingTeamNums = array();
while($row = mysql_fetch_assoc($result))
{
    $existingTeamNums[] = $row['teamnum'];
}

//Calculate array of existing records
$existingRecords = array_diff_keys($values, $existingTeamNums);

//Calculate array of new records to insert
$insertRecords = array_diff_keys($values, $existingTeamNums);

//Create query to insert new records
$query = "INSERT INTO teams
              (company, teamname, teamnum, amountraised, country, president)
          VALUES " . implode(', ', $insertRecords);