i have a drop down list called "courses" with options ( math101 , eng102 .. etc) and i want to put another drop down list called ( student name ) but this one should be hidden , so when user select one value from the courses list , the student name's list will now appear to show the students names that only taken this course so the user can pick one .. of course all the data will be taken from the database
my code so far is
<?php
include('../connect.php');
$id=$_SESSION['login_user'];
$sql = "SELECT CourseName from Course ";
$result = mysql_query ($sql, $connection);
echo "<tr><th>Course Name </th>";
echo "<td><select id='CourseName' name='v1' >";
while( $row = mysql_fetch_array($result))
{
echo "<option value='$row[CourseName]' selected='selected'>$row[CourseName]</option> ";
}
echo "</select>";
echo "</td>";
echo "</tr>" ;
$sql = "SELECT StudentName from Student ";
$result = mysql_query ($sql, $connection);
echo "<tr><th>Student Name </th>";
echo "<td><select id='StudentName' name='v2' >";
while( $row = mysql_fetch_array($result))
{
echo "<option value='$row[StudentName]' selected='selected'>$row[StudentName]</option> ";
}
echo "</select>";
echo "</td>";
echo "</tr>" ;
echo "</table>" ;
echo "</font>" ;
?>
my two tables are
Course: CourseName var(30) CourseID int(7)
Student: StudentName var(40) Student ID int(7) CourseID int(7)
so my question is , how to make "student name" a hide drop down list depends on 'course' list , so when user choose one course it appear all students names who are taken this course ( by course id ) ?
The way I would do this is using javascript/jQuery/Ajax. Note this is untested, as I just copied and pasted these examples.
In your original file make the following changes-
$sql = "SELECT CourseName from Course ";
$result = mysql_query ($sql, $connection);
echo "<tr><th>Course Name </th>";
echo "<td><select id='CourseName' name='v1' onchange='students()' >"; // Added on Change
echo "<option value='' selected='selected'>Select</option> ";
while( $row = mysql_fetch_array($result))
{
echo "<option value='$row[CourseID]'>$row[CourseName]</option> "; // make the value = to CourseID
}
echo "</select>";
echo "</td>";
echo "</tr>" ;
// removed sql query to get students,
echo "<tr><th>Student Name </th>";
echo "<td><select id='StudentName' name='v2' >"; //builds a blank student select
echo "</select>";
echo "</td>";
echo "</tr>" ;
echo "</table>" ;
In the head of your file add the following javascript/jQuery/Ajax
<head>
...
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script> //can be changed to other version ie. 1.7.../1.8.. etc
<script type="text/javascript">
function students(){
$("#StudentName option").remove(); // empties out the StudentName select so we can rebuild
var course = $('#CourseName').val(); //this gets the value of the selected Course
if(course != ""){ // we will only get students if a Course is selected
jQuery.ajax({
type: "POST",
url: "students.php",
data: 'course='+course,
cache: false,
success: function(response){
var student_array = JSON.parse(response);
$.each(student_array, function() {
$("<option />").attr("value", student_array).text(student_array).appendTo("#StudentName"); // adds new StudentName select options
});
}
});
}
</script>
...
</head>
And create a separate file students.php - (or rename url in Ajax to match)
<?php
include('../connect.php');
$course = mysql_real_escape_string($_POST['course']); // gets course that was sent via Ajax
$sql = "SELECT StudentName from Student";
$sql .= " WHERE CourseID = '$course'"; // get only students with selected CourseID
$result = mysql_query ($sql, $connection);
while( $row = mysql_fetch_array($result))
{
$student_array[] = $row[StudentName];
}
$student_array = json_encode($student_array); // encodes it to send back
print_r($student_array); // prints the array to use
?>
Please be aware that you should not be writing new code using mysql_* functions. Instead, the MySQLi or PDO_MySQL extension should be used. See MySQL: choosing an API guide
Here is another way to do it, if you prefer not to use the Ajax/jQuery answer I provided. After you select the course, it will reload the page with StudentName select based off the CourseID. This is accomplished by adding 1 javascript code onchange='this.form.submit();' to the CourseID select, and then adding isset($_POST['v1']) around the StudentName select.
<?php
include('../connect.php');
$id=$_SESSION['login_user'];
$sql = "SELECT CourseName from Course ";
$result = mysql_query ($sql, $connection);
echo "<form action='' method='post'>";
echo "<table>";
echo "<tr><th>Course Name </th>";
echo "<td><select id='CourseName' name='v1' onchange='this.form.submit();'>"; // add onchange function to submit
echo "<option value=''>Select Course</option> "; // empty value option
while( $row = mysql_fetch_array($result)){
$sel = ($_POST['v1'] == $row[CourseName])? "selected='selected'":""; // adds selected='selected' if post course same as this course
echo "<option value='$row[CourseName]'$sel>$row[CourseName]</option> ";
}
echo "</select>";
echo "</td>";
echo "</tr>";
if ((isset($_POST['v1'])) && ($_POST['v1'] != "")){ // checks if a course is selected
$sql = "SELECT StudentName from Student WHERE CourseID = ".mysql_real_escape_string($_POST['v1']); // only selects StudentName which has CourseID
$result = mysql_query ($sql, $connection);
echo "<tr><th>Student Name </th>";
echo "<td><select id='StudentName' name='v2' >";
while( $row = mysql_fetch_array($result)){
echo "<option value='$row[StudentName]' selected='selected'>$row[StudentName]</option> ";}
}
echo "</select>";
echo "</td>";
echo "</tr>" ;
echo "</table>" ;
echo "</form>" ;
?>