SQL update/insert

[max_power]

Hi,

 

I have about 40 select boxes and I've allocated each of them an individual id because the amount of select boxes will never change.  I've put each value in a JS array and then receive it in PHP through AJAX.

 

The problem I am having is to do with inserting/updating data in the db. The problem is currently I have the following logic set when the user presses the save button:

 

[center]
                        $sql = "SELECT * FROM tbl_supervision WHERE workload_id = '".$_SESSION['active_workload']."' AND semester = '1';";        
		$result = mysql_query($sql) or die ("Error in query: $sql. " . mysql_error()); 
		$rows = mysql_num_rows($result);
		$supervision_data = array();
			  
		while($row = mysql_fetch_array($result))
		{		 
			$supervision_data[] = $row;
		}

		if(!empty($supervision_data))
		{
			for($i = 0; $i < count($supervision_data); $i++)
			{
				$sql4 = "UPDATE tbl_supervision SET workload_id = '".$_SESSION['active_workload']."', degree = '".$degree1array[$i]."', student_name = '".$student1array[$i]."', student_eft = '".$eft1array[$i]."', supervision_role = '".$role1array[$i]."' WHERE supervision_id = '".$supervision_data[$i]['supervision_id']."' AND semester = '1';";
				$result4 = mysql_query($sql4) or die ("Error in query: $sql4. " . mysql_error()); 

				if (!$result4)
				{
					die('Error: ' . mysql_error());
				}
				else
				{
					$success = 1;
				}
			}					
		}
		else
		{
			for($i = 0; $i < count($degree1array); $i++)
			{
				if($degree1array[$i] != "None" || $student1array[$i] != "" || !empty($eft1array[$i]) || $role1array[$i] != "")
				{					
					if($degree1array[$i] != "None")
					{
						$sql7 = "INSERT into tbl_supervision (workload_id, degree, student_name, student_eft, supervision_role, semester) VALUES ('".$_SESSION['active_workload']."', '".$degree1array[$i]."', '".$student1array[$i]."', '".$eft1array[$i]."', '".$role1array[$i]."', '1');";
						$result7= mysql_query($sql7) or die ("Error in query: $sql7. " . mysql_error()); 

						if (!$result7)
						{
							die('Error: ' . mysql_error());
						}
						else
						{
							$success = 1;
						}
					}
				}
				else
				{
					$success = 0;
				}
			}
		}[/center]

 

What I want to achieve is:

 

1. If there are the same number of inputs in the db compared to the number of inputs received from the select boxes, the update query will simply kick in.

2. If there there are less number of inputs in the db compared to the number of inputs received from the select boxes, the insert query will only insert those values that aren't already in the db.

 

Currently, with the code I have posted above,  I can only insert values from scratch and only update those values that are already there in the db but I can't add in new values after the initial data has been inserted.

 

I've tried using count() and sizeof() functions but I can't seem to work out how could I manipulate the code to achieve what should be straight forward functionality. Could anybody please help me out?

 

The degree1array, student1array, eft1array & role1array are the arrays that contain the select box values..

 

var degree1 = new Array (document.getElementById('degreetype1').value, document.getElementById('degreetype2').value, document.getElementById('degreetype3').value, 
					document.getElementById('degreetype4').value, document.getElementById('degreetype5').value, document.getElementById('degreetype6').value, 
					document.getElementById('degreetype7').value, document.getElementById('degreetype8').value, document.getElementById('degreetype9').value, 
					document.getElementById('degreetype10').value);

   var student1 = new Array (escape(document.getElementById('studentname1').value), escape(document.getElementById('studentname2').value), escape(document.getElementById('studentname3').value), 
					escape(document.getElementById('studentname4').value), escape(document.getElementById('studentname5').value), escape(document.getElementById('studentname6').value), 
					escape(document.getElementById('studentname7').value), escape(document.getElementById('studentname8').value), escape(document.getElementById('studentname9').value), 
					escape(document.getElementById('studentname10').value));

   var eft1 = new Array (document.getElementById('eft1').value, document.getElementById('eft2').value, document.getElementById('eft3').value, 
					document.getElementById('eft4').value, document.getElementById('eft5').value, document.getElementById('eft6').value, 
					document.getElementById('eft7').value, document.getElementById('eft8').value, document.getElementById('eft9').value, 
					document.getElementById('eft10').value);

   var role1 = new Array (document.getElementById('suprole1').value, document.getElementById('suprole2').value, document.getElementById('suprole3').value, 
					document.getElementById('suprole4').value, document.getElementById('suprole5').value, document.getElementById('suprole6').value, 
					document.getElementById('suprole7').value, document.getElementById('suprole8').value, document.getElementById('suprole9').value, 
					document.getElementById('suprole10').value);

 

Thanks