Im having some issues here and im not sure whether it represents a lack of insight into my code or db structure.
Here is my db structure explained
qualityadmin
qualityadmin_id
quality_name
address1
address2
etc......
and the following tables.....
activity_participant
activity_participant_id
activity
expert_participant
expert_participant_id
expert
quality_systems
quality_system_id
quality_system
.....contain the values for each quality system, expertise and activity, each having a title/description and a unique ID. These will be used
for generating checkboxes dynamically for a user to choose from.
The below tables:
activity_participant_rel
activity_participant_relid
qualityadmin_relid
expert_participant_rel
expert_participant_relid
qualityadmin_relid
quality_system_rel
qualitysystem_relid
qualityadmin_relid
Will contain two values, the ID number of the associated organization and the ID number of the corresponding quality system, expertise and activity.
These values will be compared against for what organizations match the user's search criteria.
This is my formhandler:
<?php
// add data from form 1
$sql = "INSERT INTO qualityadmin SET qualityadmin_id = NULL ";
foreach ($_SESSION['form1_data'] as $col =>$val ){
if($col <> 'submit'){
if ($col=='orgtype'){
//ignore and process later below for seperate table insert
}
elseif ($col=='activity'){
//ignore and process later below for seperate table insert
}
elseif ($col=='expert'){
//ignore and process later below for seperate table insert
}
elseif ($col=='qualitysystem'){
//ignore and process later below for seperate table insert
}
else{
$sql .=", $col = '$val' ";
}
}
}
//////////////////////////////////
mysql_query($sql, $connection) or die(mysql_error());
[B]//get last insert id for quality admin
$sql ="select LAST_INSERT_ID() as id from qualityadmin";
$result = mysql_query($sql, $connection) or die(mysql_error());
$row=mysql_fetch_array($result);
$id = $row['id'];[/B]
//get last insert id for activity_participant
$sql ="select LAST_INSERT_ID() as id from activity_participant";
$result = mysql_query($sql, $connection) or die(mysql_error());
$row=mysql_fetch_array($result);
$ap_id = $row['id'];
//get last insert id for expert_participant
$sql ="select LAST_INSERT_ID() as id from expert_participant";
$result = mysql_query($sql, $connection) or die(mysql_error());
$row=mysql_fetch_array($result);
$exp_id = $row['id'];
//get last insert id for quality_systems
$sql ="select LAST_INSERT_ID() as id from quality_systems";
$result = mysql_query($sql, $connection) or die(mysql_error());
$row=mysql_fetch_array($result);
$qs_id = $row['id'];
//update orgtype_participant table
if($_SESSION['form1_data']['orgtype']){
foreach($_SESSION['form1_data']['orgtype'] as $fvalue){
$orgtypeid_sql ="INSERT INTO orgtype_participant SET orgtype_participant_id = NULL , qualityadmin_id = '$id', orgtype = '$fvalue' ";
mysql_query($orgtypeid_sql, $connection) or die(mysql_error());
}
}
// update activity_participant_rel table
if($_SESSION['form1_data']['activity']){
foreach($_SESSION['form1_data']['activity'] as $fvalue){
$service_sql ="INSERT INTO activity_participant_rel SET activity_participant_relid = '$ap_id' , qualityadmin_relid = '$id'";
mysql_query($service_sql, $connection) or die(mysql_error());
}
}
// update expert_participant_rel table
if($_SESSION['form1_data']['expert']){
foreach($_SESSION['form1_data']['expert'] as $fvalue){
$service_sql ="INSERT INTO expert_participant_rel SET expert_participant_relid = '$exp_id' , qualityadmin_relid = '$id'";
mysql_query($service_sql, $connection) or die(mysql_error());
}
}
// update qualitysystem_rel
if($_SESSION['form1_data']['qualitysystem']){
foreach($_SESSION['form1_data']['qualitysystem'] as $fvalue){
$service_sql ="INSERT INTO quality_system_rel SET qualitysystem_relid = '$qs_id' , qualityadmin_relid = '$id'";
mysql_query($service_sql, $connection) or die(mysql_error());
}
}
?>
the error message I get is:
Duplicate entry '87' for key 1
thx in advance
