If anyone can assist me with the proper query it would be greatly appreciated!
I am trying to get the name of the current teacher out of the database. I have two tables:
Teachers & Students
this code is supposed to get the teacher name from the teacher table by comparring the teacher_id from the 2 tables:
<td>Current Teacher</td>";
$oldteacher_query = ("
SELECT teachers.teacher_id, students.teacher_id, teachers.teacher_name
FROM teachers, students
WHERE students.teacher_id ='$row['teacher_id']'
");
//ORDER BY teachers.teacher_name
$oldresults = mysql_query($oldteacher_query) or die(mysql_error());
while ($oldteacher_row = mysql_fetch_assoc($oldresults)
{
echo "current teacher is:{$oldteacher_row['teacher_name']}";
}
echo "<td><input type=\"text\" name=\"teacherid\" value=\"{$row['teacher_id']}\" size=\"40\" /></td>
Here is the whole form:
if (is_numeric ($_GET['id']) )
{
// Define the query.
$query = "SELECT * FROM students WHERE student_id={$_GET['id']}";
echo "the query was {$query}";
if ($r = mysql_query ($query)) { // Run the query.
$row = mysql_fetch_array ($r); // Retrieve the information.
// Make the form.
echo "<h1>Edit Students Name and Information:</h1>
<form action=\"edit_student.php\" method=\"post\">
<table width=\"100%\">
<tr>
<td>Students First Name:</td>
<td><input type=\"text\" name=\"fname\" value=\"{$row['student_fname']}\" size=\"40\" /></td>
</tr>
<tr>
<td>Students Last Name:</td>
<td><input type=\"text\" name=\"lname\" value=\"{$row['student_lname']}\" size=\"40\" /></td>
</tr>
<tr>
<td>Birthdate:</td>
<td><input type=\"text\" name=\"bdate\" value=\"{$row['student_bdate']}\" size=\"40\" /></td>
</tr>
<tr>
<td>Age</td>
<td><input type=\"text\" name=\"age\" value=\"{$row['student_age']}\" size=\"3\" /></td>
</tr>
<tr>
<td>Address 1:</td>
<td><input type=\"text\" name=\"address1\" value=\"{$row['student_address1']}\" size=\"40\" /></td>
</tr>
<tr>
<td>Address 2:</td>
<td><input type=\"text\" name=\"address2\" value=\"{$row['student_address2']}\" size=\"40\" /></td>
</tr>
<tr>
<td>City:</td>
<td><input type=\"text\" name=\"city\" value=\"{$row['student_city']}\" size=\"40\" /></td>
</tr>
<tr>
<td>State:</td>
<td><input type=\"text\" name=\"state\" value=\"{$row['student_state']}\" size=\"40\" /></td>
</tr>
<tr>
<td>Zip Code:</td>
<td><input type=\"text\" name=\"zip\" value=\"{$row['student_zip']}\" size=\"5\" /></td>
</tr>
<tr>
<td>Father's Name:</td>
<td><input type=\"text\" name=\"father\" value=\"{$row['student_father']}\" size=\"40\" /></td>
</tr>
<tr>
<td>Mother's Name:</td>
<td><input type=\"text\" name=\"mother\" value=\"{$row['student_mother']}\" size=\"40\" /></td>
</tr>
<tr>
<td>Contact Number</td>
<td><input type=\"text\" name=\"phone\" value=\"{$row['student_contact']}\" size=\"40\" /></td>
</tr>
<tr>
<tr>
<td>Current Teacher</td>";
$oldteacher_query = ("
SELECT teachers.teacher_id, students.teacher_id, teachers.teacher_name
FROM teachers, students
WHERE students.teacher_id ='$row['teacher_id']'
");
//ORDER BY teachers.teacher_name
$oldresults = mysql_query($oldteacher_query) or die(mysql_error());
while ($oldteacher_row = mysql_fetch_assoc($oldresults)
{
echo "current teacher is:{$oldteacher_row['teacher_name']}";
}
echo "<td><input type=\"text\" name=\"teacherid\" value=\"{$row['teacher_id']}\" size=\"40\" /></td>
</tr>
<tr>
<td> New Teacher:</td>
<td>";
$newteacher_query = 'SELECT teacher_id, teacher_name FROM teachers ORDER BY teacher_name';
$s = mysql_query($newteacher_query) or exit(mysql_error());
echo '<select name="teacherid">';
echo '<option value="">Choose New Teacher</option>';
while ($rows = mysql_fetch_assoc($s))
{
echo '<option value="' . $rows['teacher_id'] . '">' . $rows['teacher_name'] . '</option>';
}
echo '</select>';
echo"</tr>
<tr>
<td align=\"right\">
<input type=\"hidden\" name=\"id\" value=\"{$row['student_id']}\" />
<input type=\"submit\" name=\"submit\" value=\"Update this student\" />
</td>
</table>
<a href=\"view_student.php\">View Current Students</a>
</form>";
echo $row['student_id'];
}
else
{ // Couldn't get the information.
echo "<p>Could retrieve the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>";
}
if I remove the first code I inserted here, the form works to where I can change the teacher and it puts the new value into the students table, and I can show the current teacher_id from the table, but I want to show the name!
Thanks,
Mike
