Help needed for insert code

[Tezread]

I thought i had this cracked but now im in deeper strife.

Basically i have tables

qualityadmin
qualityadmin_id (PK)
address1
address2
etc

activity
activity_id (unique index)
activity

expert
expert_id (unique index)
expertise

qualitysystem
qualitysystem_id (unique index)
qualitysystem

the rule is one qualityadmin can choose one or more activities/expertises/qualitysystems.

I am trying to record the choices qualityadmin makes in 3 other 'child' tables:

activity_participant
id (PK)
activity_participant_relid (stores the unique id from activity)
qualityadmin_relid (stores the PK from qualityadmin)

expert_participant
id (PK)
expert_participant_relid (stores the unique id from expert)
qualityadmin_relid (stores the PK from qualityadmin)

etc

herein my problem occurs with the insert code. For example, for each qualityadmin im trying to insert into activity_participant the list of activities they chose i.e cany anyone help a loser like myself?

here is my pathetic attempt so far:

<?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()); 

//get last insert id from qualityadmin
$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'];
//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($sql, $connection) or die(mysql_error()); 

	}
	}

	//get id from activity_participant
$sql ="select activity_participant_id from activity_participant";
$result = mysql_query($sql, $connection) or die(mysql_error()); 
$row=mysql_fetch_array($result);
$ap_id = $row['activity_participant_id'];

//update activity_participant_rel table
if($_SESSION['form1_data']['activity']){
foreach($_SESSION['form1_data']['activity'] as  $fvalue){
		$sql ="INSERT INTO activity_participant_rel SET id = NULL , activity_participant_relid = '$ap_id', qualityadmin_relid = '$fvalue' ";

	mysql_query($sql, $connection) or die(mysql_error()); 

	}
	}
			//get id from expert_participant
$sql ="select expert_participant_id from expert_participant";
$result = mysql_query($sql, $connection) or die(mysql_error()); 
$row=mysql_fetch_array($result);
$exp_id = $row['expert_participant_id'];
		// update expert_participant_rel table
if($_SESSION['form1_data']['expert']){
foreach($_SESSION['form1_data']['expert'] as  $fvalue){
		$service_sql ="INSERT INTO expert_participant SET id = NULL , expert_participant_relid = '$exp_id', qualityadmin_relid  = '$fvalue' ";
		mysql_query($service_sql, $connection) or die(mysql_error()); 

	}
	}
					//get id from quality_systems table
$sql ="select quality_system_id from quality_system";
$result = mysql_query($sql, $connection) or die(mysql_error()); 
$row=mysql_fetch_array($result);
$qs_id = $row['quality_system_id'];
				// update qualitysystem_participant table
if($_SESSION['form1_data']['qualitysystem']){
foreach($_SESSION['form1_data']['qualitysystem'] as  $fvalue){
		$service_sql ="INSERT INTO qualitysystem_participant SET id = NULL , quality_system_relid = '$qs_id', qualityadmin_relid  = '$fvalue' ";
		mysql_query($service_sql, $connection) or die(mysql_error()); 

	}
	}

?>

thx in advance

[jdorsch]

Lets say I have monkeys, and a monkey can like 1 or more foods.

lets say I get this array from a form or session or something

$foodIDsMyMonkeyLikes = array
(
   0=>10
   1=>50
   2=>49
   3=>37
) ;

and I want to insert them into a many to many relationships table I can do

// Pretend monkeyID is 107
$monkeyID = doSomeMagicAndGetMyMonkey() ;

foreach( $foodIDsMyMonkeyLikes as $foodID )
{
   $vals[] = "( {$monkeyID}, {$foodID} )" ;
}


$sqlVals = implode( ',', $vals ) ;
// $sqlVals is now '(107,10),(107,50),(107,49),(107,37)'

$sql = "INSERT INTO monkeyFoods( monkeyID, foodID ) VALUES {$sqlVals}" ;

//now run your sql 

[Tezread]

Thankyou for your help. I tried this but I come up with a called to undefined function on line 140 ---- $getqualityadminid = getqualityadminid() ;

// insert array of activities chosen
$activityarray = array ($_POST['activity']); 

$getqualityadminid = getqualityadminid() ; 

foreach( $activityidchosen as $activityid ) 
{ 
   $vals[] = "( {$qualityadminid}, {$activityid} )" ; 
} 


$sqlVals = implode( ',', $vals ) ; 


$sql = "INSERT INTO activity_participant ( qualityadmin_relid, activity_participant_relid ) VALUES {$sqlVals}" ; 

[jdorsch]

Thats because 'getqualityadminid() ; ' isn't really a function. In my example 'doSomeMagicAndGetMyMonkey() ;' was just a placeholder. In that spot you are supposed to do some magic and get your primary key by doing a query. I just didn't want to type all that out because you were already fetching it with last_insert_id in your OP.

Edit: Also in your foreach, shouldn't the first argument be $activityarray?

[Tezread]

i see- i think I was in error when retrieving my primary key id for each activity last time i tried- could you show me the argument needed for that? thankyou

i.e- I dont think the following is quite right as it needs to have a foreach clause on it which will say foreach activity get the id

//get id from activity_participant 
$sql ="select activity_participant_id from activity_participant"; 
$result = mysql_query($sql, $connection) or die(mysql_error()); 
$row=mysql_fetch_array($result); 
$ap_id = $row['activity_participant_id'];