using REPLACE with one to many relationships

[angrytuna]

Hello the forum,

My problem is rather involved, so I really appreciate any of you who manage to bear with me to the end.

To describe the problem, let me first present a hypothetical database, perhaps used to track students. This database consists of two tables.

The first table "students" has two attributes: a primary key called "ssn"(primary key unique not null) that tracks the student's SSN, and a second attribute "name" that tracks their name.

The second, "classes", also has two attributes, "fk_ssn"(not null references students(ssn)), and "class" (Varchar) which represents the classes they have taken. This essentially represents a one-to-many relationship.

The problem is this: I am doing updates to a similar database in php, inserting table by table. That is, I first insert into students, and then into classes. When using REPLACE syntax, MySQL will notice that the primary key in students exists already, and replace the existing record. However, since the classes.fk_ssn cannot be unique (recall: one to many), it cannot be a primary key, and thus, MySQL simply adds another duplicate record rather than replace the existing one.

My question is, does anyone know an elegant way to deal with this problem?

AT

[TheIceman5]

deelte duplicates first then add new ones

[bastien]

why not simply query the two tables to see if the value is represent?

if the record is present do nothing, if it is not:

1. then see if the student is in the names table, insert if not
2. insert the value into the classes table

so:

$sql="select a.ssn, a.name, b.class from 
          students a,
          classes b
          where 
          a.ssn = b.fk_ssn and a.ssn= ".$_POST['ssn']. "
          and class = ".$_POST['class'];

$result=mysql_query($sql) or die ("can't query because ".mysql_error());

if (mysql_num_rows($result)==1){
     echo "record present";
     die();
}else{

[b]//check to see if the student is present in the db[/b]
$sql2="select * from students where ssn=".$_POST['ssn'];

$result2=mysql_query($sql2);

if (mysql_num_rows($result2)==1){
    [b]//student is present so 
     //just update the class list only[/b]
     $sql3="insert into classes values ($ssn, '".$_POST['class'].")";

     $result3=mysql_query($sql3) or die ("can't query because ".mysql_error());

 }else{

      [b]//student is not present so update both tables[/b]

     $sql3="insert into students values ($ssn, '".$_POST['name']."');

     $sql4="insert into classes values ($ssn,'".$_POST['class']."')";

     $result3=mysql_query($sql3) or die ("can't query because ".mysql_error());

     $result4=mysql_query($sql4) or die ("can't query because ".mysql_error());

  }

}   

Note: this needs to be adapted to however you achive the $ssn values...