Tables working together

[Johnnyboy123]

So I have 2 tables, a student table and a course table. When I add a new course to my course table it should be available to students to sign up for which goes into the student table when they do. However now I have to create a page which displays the different courses (course table) and when you click on a course it should display all the users registered for that specific course. Also when a course gets deleted all the student registered for that course should be deleted. So deleting a course in the course table should delete student in the student table..

 

I have already created a page which display the different course information and I am familiar with creating pages that allows you to delete table info from the page. However, I am lost on how to display the students when clicking on the course and how to link the 2 tables so when deleting a course it deletes the students aswell.

 

Where should I begin with this and how should I go about it? This is my code up until now for displaying the different courses:

 

<?php $query=mysql_query("SELECT * FROM course ");


while ($rows = mysql_fetch_array($query)):

$cname=$rows['cname'];
$cid=$rows['cid'];


echo "<table> 
<tr> 
<td> $cname </td>  
<td> $cid </td>
</tr>	
</table>
";
endwhile; 

[btherl]

You can implement automatic deletion by using foreign keys and the "cascade" option to the sql delete command.

 

I would expect you to have 3 tables - course, student, registration.  The registration table has a row with student id and course id for each student registered for a course.  And deletion of a course or a student would cascade and delete the registration table entries, but deleting a course would not delete a student and deleting a student would not delete a course.  Does that make sense?

 

BTW you are using the "old" php syntax for the while loop - there's nothing wrong with that, but you should be aware of it.  Also you are not checking the call to mysql_query() for errors, which may come back to bite you later.