Multiple Select List - Retreiving Data From Db

[NigeW]

Further to thread434-1199750 I have a similar problem as follows :

I have a form to create new individual records and within form this there is a multiple select list for qualifications.

I also have a form to edit existing records and so need to be able to list all the qualifications and "select" those applicable to the individual record.

The number of qualifications currently stands at 36 but can increase. The number of qualifications a user has is not restricted in any way although it unlikely to exceed 4 or 5.

Are there any clever techniques to avoid a main loop for the qualifications and then a nested loop that must run 'x' number of times for each qualification ?

Thanks

Nigel Wilson
Christchurch Web Design

 

[jpadie]

i think the answer depends largely on your database structure. are you using a separate table to store joins between the qualifications table and the user table? or all the qualifications stored in a column definition (for a set column)? do you store all qualifications in a delimited manner inside a single column etc.

in general you can sometimes get away without a loop (certainly the nested loop) but php is so fast that looping over less than 100 (or less than 1000) entries is not going to produce any noticeable delay over the inherent latency of a broadband or even t1 connection.

let us have your database structure and perhaps a snip of the html form you use and we'll see whether anyone has any bright ideas on optimisation for you.

 

[NigeW]

Hi jpadie

Thanks for your reply.

My database structure is as follows :

Table -> individuals
Table -> qualifications
Table -> individual_qualification_links

So obviously there is no direct storage of data in the individual table.

I haven't written the code yet but envisage something like :

--START LOOP
--Output all qualifications creating options within a
--select list.

-----SUB LOOP
-----Check current qualification ID to linkage table to see
-----if individual is linked - if so output "SELECTED"
-----END SUB LOOP

--END LOOP

When I write the above it doesn't seem such a bad piece of code than I first envisaged as the sub loop query will be very specific , ie "SELECT id FROM qualifications WHERE individualID = 12345 and qualificationsID = 69" - there can only be 2 results - 0 or 1.

I think I may have solved my own problem but if anyone has developed a clever solution I'm keen to here about it.

Thanks



Nigel Wilson
Christchurch Web Design

 

[jpadie]

there may be a simpler solution.

for a coded solution (which i have not checked at all, even for parse errors)

<?
/*
this works by saying 
1. is a form submitted.  if so then process the form result.
	in processing the form result we first delete all joins in the jointable and then add new joins by iterating through the submitted checkboxes
2. then we display the results.  this call takes a boolean argument to decide whether you want an editable form or not.  
	the main database calls are performed only once for the entire dataset.  the only 'loop' after this is the loop needed to render the qualifications each time.
*/

if (isset($_POST['submit'])){
	processFormResult();
}

echo displayResults(false); //change to true to generate forms/editable rather than text output

function processFormResult(){
	//first delete all qualifications from the join table that have the id in them
	mysql_query('delete from jointable where individualID = "' . mysql_escape_string(trim($_POST['individualID'])) .'"') or die(mysql_error());
	
	//now update the individuals table however you want
	
	//
	//
	
	//now do the qualifications
	$values = '';
	if (isset($_POST['qualifications'])){
		foreach ($_POST['qualifications'] as $q){
			$values .= '('.mysql_escape_string(trim($_POST['individualID'])) .','.mysql_escape_string(trim($q)).'),'; 
		}
	
	//get rid of trailing comma
	$values = trim($values, ',');
	mysql_query  ("	insert 
					into 
						jointable 
						(individualID, qualificationID) 
						values 
						$values") 
				or die(mysql_error());
	} //end of if
}

function getListofQualifications(){
	$qualsRS = mysql_query("
							select 
								qualificationID, 
								qualificationName 
							from 
								qualifications") 
						or die (mysql_error());
	while ($row = myqsl_fetch_array($quals)){
 		$listOfQualifications[$row[0]] = $row[1];	
	}
	mysql_free_result($qualsRS);
	return $listOfQualifications;
}

function getIQJoins(){
	//returns an array of joins with the key as the individualID
	$result = mysql_query("
							select 
								individualID, 
								qualificationID 
							from 
								jointable")
						or die (mysql_error());
						
	while ($row = mysql_fetch_array($result)){
	  $individualQualifications[$row[0]][$row[1]] = true;
	}
	
	mysql_free_result ($result);
	return $individualQualifications;
}

function displayResults ($editable=false){
	//this function is a wrapper essentially.  the heavy lifting for the rendering is done by buildResult
	//grab some recordsets
	$listOfQualifications = getListofQualifications();
	$individualQualifications = getIQJoins();
	
	//dummy variable
	$output = '';

	$result = mysql_query ("Select 
								individualID, 
								individualName 
							from 
								individuals") 
						or die(mysql_error());
	while ($row = mysql_fetch_assoc($result)) {
		//check to see whether user has any quals listed. if so pass the array in, otherwise pass a dummy array
		$tmp = isset($individualQualifications[$row[0]]) ? $individualQualifications[$row[0]] : array();
		$output .= buildResult($row, $editable, $listofQualifications, $tmp);
		
	}
	return $output;
}

function buildResult ($data, $editable, $listofQualifications, $individualQualifications){
	//hopefully this is self-explanatory.   it just builds the html.

	switch ($editable){
		case true:
			$start = '<form action="'.$_SERVER['PHP_SELF'].'" method="post" enctype="multipart/form-data">';

			$name = <<<NAME
			<input type="hidden" name="inidividualID" value="{$data[0]}"  />
			<input type="text" name="individualName" value="{$data[1]}"  />&nbsp;Name<br/>
NAME;
			$quals = '';
			foreach ($listOfQualifications as $qid=>$qname){
				$checked = isset($individualQualifications[$qid]) ? 'checked="checked"' : '';
				$quals .= '
			<input type="checkbox" name="qualifications" value="'.$qid.'" '.$checked.' />&nbsp;'.$qName.' <br/>
';
			}
			$quals .= '	<input type="submit" name="submit" value="Save" />';
			$stop = '</form>';
		break;
		default:
		$name = $data[0];
		$quals = '';
		foreach ($listOfQualifications as $qid=>$qname){
			$checked = isset($individualQualifications[$data[0]][$qid]) ? '[X]' : '[&nbsp;]';
			$quals .= "$checked &nbsp; $qname";
		}
		$start = $stop = '';
	}
	$output = <<<OP
$start
<table border='1'>
	<tr>
		<th>Name</th>
		<th>Qualifications</th>
	</tr>
	<tr>
		<td>$name</td>
		<td>$quals</td>
	</tr>
</table>
$stop;
OP;
	
return $output;
}
?>