Problems with nested loops and arrays

[tjodolv]

Hi. I am trying to create a web-application with a member database. In the information for each member, is also information about what courses the member has. This is organized with one table for members, one for all existing courses, and a relations table containing the information on which course each member has. The courses are sorted by categories.

 

Retrieving the data and listing it in a table is no problem, but when I want to edit a member I run into problems. The member information is loaded into a form similar to the "Create new member"-form I have set up.

 

My problem starts when I try to fill inn what courses have been taken and when. Each course has a checkbox with an associated text field for when the member completed the course. The first – and only the first – category where the member has more than one course, will not pre-check the first course in that category. I believe it is related to my crummy, nested loops and arrays, but I have no idea how to make it work. Any pointers or better solutions are appreciated

 

Categories

(dbSelect() is a function with the following arguments ($select, $table, $where, $order_by, $limit)

<?php
$result = dbSelect("DISTINCT category","courses"," 1 = 1","weight");
$cCategories = array();
while ($row = mysql_fetch_assoc($result)) {
	$cCategories[] = $row['category'];
}
mysql_free_result($result);
?>

 

This member's completed courses

<?php
$sql = "SELECT m.id, c.course_name, c.category, r.year FROM $members AS m LEFT JOIN $relations AS r ON r.member_id = m.id LEFT JOIN $courses c ON r.course_id = c.id WHERE m.id = $id ORDER BY c.category, r.year DESC";
?>

 

Set up some arrays to use in loops later on

<?php
$result = mysql_query($sql, $db['connection']);
$courses = array();
$coursesCourseByCat = array();
$coursesCourseByYear = array();
$coursesYear = array();
while ($row = mysql_fetch_assoc($result)) {
	$courses[$row['category']][$row['course_name']] = $row['year'];
	$coursesCourseByCat[$row['category']] = $row['course_name'];
	$coursesCourseByYear[$row['year']] = $row['course_name'];
	$coursesYear[$row['course_name']] = $row['year'];
}
mysql_free_result($result);
?>

 

Then the form, sorted by categories:

- For each category, all courses of that category is fetched

- For each course, we check to see if the member has completed this course

<?php
foreach ($cCategories as $key => $value) {
$result = dbSelect("*","courses","category = '$value'");
$list = "";
while ($row = mysql_fetch_assoc($result)) {
	if ((in_array($row['course_name'],$coursesCourseByCat))||(in_array($row['course_name'],$coursesCourseByYear))) { // This is the conditional check I believe is related to the problem. It's awfully crummy...
		$y = $coursesYear[$row['course_name']];
		$c = " checked=\"checked\"";
	} else {
		$y = "";
		$c = "";
	}
	$list .= "\n\t\t\t\t<tr><td><input type=\"checkbox\" name=\"".$row['course_name']."[1]\" value=\"".$row['id']."\" id=\"".$row['course_name']."_1\"$c />";
	$list .= "<label for=\"".$row['course_name']."_1\">".$loc[$row['course_name']]."</label></td>";
	$list .= "<td><input type=\"text\" name=\"".$row['course_name']."[2]\" id=\"".$row['course_name']."_2\" value=\"$y\" size=\"2\" /></td></tr>";
}
$content .= "\t\t\t<table class=\"nmCourse\"><caption>".$loc[$value].":</caption>$list\n\t\t\t</table>\n";
}
mysql_free_result($result);
?>