On Duplicate Key UPDATE pb

[avia767]

Hello guys, my ON DUPLICATE KEY UPDATE  doesn't work correctely; 

 

data are uploaded every day AND many times for updates, from CSV files

 

the CSV files look is 

 

csv file of yesterday:

1  " date"               , "  route  ",
2  " 2013.06.10"         , "    A    ",
3  " "                   , "    D    ",
4  " "                   , "    G    ",

csv file of today:

1  " date"               , "  route  ",
2  " 2013.06.11"         , "    C    ",
3  " "                   , "    D    ", 
4  " "                   , "    M    ",
5  " "                   , "    Z    ",

My table in MySQL must be like this :

 

id |     date             | route |    

----------------------------

1 |    2013.06.10   |    A    |

2 |    2013.06.10   |    D    |          // data of day : 2013.06.10

3 |    2013.06.10   |    G    |

4 |    2013.06.11   |    C    |

5 |    2013.06.11   |    D    |

6 |    2013.06.11   |    M    |

7 |    2013.06.11   |    Z    |

 

there are no unique columns, are are duplicated for each record, and route might have duplicate entries

 

For each day there are insert and update,

if ($date == $today OR $date==" ")
{
​$sql = mysql_query ("INSERT INTO my_table 
(date, route) VALUES ('$date' , '$route') ON DUPLICATE KEY UPDATE date='$date', route ='$route' ");
}

I can insert and update any columns but here are the issue :

 

2)  when I refrech my browser, data are loader again as much as I refrech my web-page, lines are duplicated 

 

1 |    2013.06.10   |    A    |

2 |    2013.06.10   |    D    |          

3 |    2013.06.10   |    G    |

4 |    2013.06.10   |    A    |          // same data  for 1st refresh

5 |    2013.06.10   |    D    |          

6 |    2013.06.10   |    G    |         

1 |    2013.06.10   |    A    |          // same data  for 2nd refresh

2 |    2013.06.10   |    D    |          

 

3 |    2013.06.10   |    G    |

Edited June 11, 2013 by avia767

[Barand]

Make the id column auto_increment primary key.

 

Make (date, route) a unique key.

[avia767]

Make the id column auto_increment primary key.

 

Make (date, route) a unique key.

my table columns 

 

- id        [iNT (11), AUTO INCREMENT] PRIMARY KEY

- date    [DATE]

- route   [VARCHAR (9)]

 

the entries for each columns could be duplicated, because every day I make instert and update. 

 

once data are correctly uploaded must be like this:

 

id |     date             | route |    

----------------------------

1 |    2013.06.10   |    A    |

2 |    2013.06.10   |    D    |          // data of day : 2013.06.10

3 |    2013.06.10   |    G    |

4 |    2013.06.11   |    C    |

5 |    2013.06.11   |    C    |

6 |    2013.06.11   |    M    |

7 |    2013.06.11   |    Z    |

 

As you can see, date and route are duplicated for each day depending on data uploaded from my csv file.

[AbraCadaver]

ALTER TABLE `my_table` ADD UNIQUE KEY (`date`, `route`)

[avia767]

ALTER TABLE `my_table` ADD UNIQUE KEY (`date`, `route`)

it doesn't work, I got

 

#1062 - Duplicate entry '2013-06-11-6233' for key 'date'

 

because 'date' and 'route' can be duplicated, not as Unique !! 

[Barand]

I can insert and update any columns but here are the issue :

 

2)  when I refrech my browser, data are loader again as much as I refrech my web-page, lines are duplicated 

 

1 |    2013.06.10   |    A    |

2 |    2013.06.10   |    D    |          

3 |    2013.06.10   |    G    |

4 |    2013.06.10   |    A    |          // same data  for 1st refresh

5 |    2013.06.10   |    D    |          

6 |    2013.06.10   |    G    |         

1 |    2013.06.10   |    A    |          // same data  for 2nd refresh

2 |    2013.06.10   |    D    |          

 

3 |    2013.06.10   |    G    |

 

 

it doesn't work, I got

 

#1062 - Duplicate entry '2013-06-11-6233' for key 'date'

 

because 'date' and 'route' can be duplicated, not as Unique !! 

 

First you complain about duplicates then you say they can be duplicated. I'm out of here.

[avia767]

hi every body, my pb is solved, 

 

I have this SQL statement 

 

 

$sql_insert = mysql_query (
"INSERT INTO test (
date, 
route, 
timeDEPARTURE, 
timeACTUALISED, 
delay
) 
VALUES (
'$datecsv_format', 
'$routeCSV', 
'$depCSV', 
'$actCSV', 
'$delayCSV' 
) 
ON DUPLICATE KEY UPDATE 
date ='$datecsv_format',
route = '$routeCSV', 
timeDEPARTURE = '$depCSV', 
timeACTUALISED = '$actCSV', 
delay = IF('$actCSV' == '00:00:00', '$delaycsv', timediff ( timeACTUALISED , timeDEPARTURE ))
");

my probleme I'am sur is in the the IF condition here

delay = IF('$actCSV' == '00:00:00', '$delaycsv', timediff( timeACTUALISED , timeDEPARTURE ))

any help ?