Using selection of listbox to populate another list box

[SFDonovan]

I have a testing application I inherited and have to make improvements. I have a question, course, user, and group table.

Users are assigned to groups based on their access level (Staff/Faculty)
Questions are created by users and assigned to courses.

Users
user_id
user_group
user_lname
user_level

Group
group_id
group_name

Course
course_id
course_fullname

Questions
question_id
cat_id
course_id
created_by

I have two list boxes. One is a list of all courses (easy enough) The other I need populated with all "authors" (users) who created questions that belong to these courses.

If I choose a Course "Math" I want the other listbox to populate with only those authors who have created questions for the Math course.

As an observation I think Ajax would be perfect here, but I have never used it before.

The second listbox would not even appear until a user selected the course they wanted.

If anybody has some examples I would appreciate it.

Here is what I have so far.

$sql = " SELECT * FROM ".COURSE_TABLE."";

		if ($result_course = $db->sql_query($sql) ) {
			while ($course = $db->sql_fetchrow($result_course)) {
				$course_select_option .= '<option value="'.$course['course_id'].'">'.$course['course_fullname'].'</option>';
			}
		}

		$sql2 = " SELECT * FROM ".USERS_TABLE." JOIN ".GROUPS_TABLE." ON user_group = group_id WHERE group_id = 2";

		if ($result_users = $db->sql_query($sql2) ) {
			while ($users = $db->sql_fetchrow($result_users)) {
				$users_select_option .= '<option value="'.$users['user_id'].'">'.$users['user_lname'].'</option>';
			}
		}

		$template -> assign_block_vars('user.info', array('C_INFO' => 'Course Name', 'C_INPUT' => '<select name="course_id">'.$course_select_option.'</select>'));
		$template -> assign_block_vars('user.info', array('C_INFO' => 'Author Name', 'C_INPUT' => '<select name="user_id">'.$users_select_option.'</select>'));
		

[pete_bisby]

There are a couple of ways you can do this.

First way, when a selection is made on the first drop-down box the form is POSTed back to itself, which runs a query to populate the second drop-down box.

Second way, populate some JavaScript arrays and use some "clever" JavaScript to populate the second drop-down box when the first one has been selected - this will not require the form to be POSTed.

Third way, when the selection is made on the first drop-down box run some AJAX code that will dynamically populate the second drop-down box. There are some tutorials on this website covering AJAX and PHP - unsure whether this particular subject is covered. Alternatively, click here to get some very good tutorials on AJAX and PHP.

I think the third way is probably the way to go forward.