Assuming your tables are of the sort...
People: PersonID, PersonName, ...
Education: EducationID, EducationName, ....
Since each person can have more than one education, and each education can have more than one person, one solution is to create a new table detailing this relationship
PeopleToEducation: ID, PersonID, EducationID
Now to find the education of a specific Person, first grab the personal information for that Person (I don't know how you want this, but maybe someone selected the surname...) You then use the PersonID with the PeopleToEducation table to get all the EducationIDs you want, you can then display these.
$query = "SELECT * FROM People WHERE PersonName = \"$person\"";
$result = mysql_query($query);
if($result)
{
$personName = mysql_result($result, "PersonName");
... etc. for all personal details
$personID = mysql_result($result, "PersonID");
print"$personName attended these places:\n";
$query = "SELECT EducationName FROM PeopleToEducation LEFT JOIN Education ON PeopleToEducation.EducationID = Education.EducationID WHERE PeopleToEducation.PersonID = $personID";
$result = mysql_query($query);
$num_rows = mysql_numrows($result);
// Go through and print the educations, and links to self with delete option
for($i = 0; $i < $num_rows, $i++) {
$cur_educationID = mysql_result($result, "EducationID");
$cur_educationName = mysql_result($result, "EducationName");
// Print the education
print "$cur_educationName";
// And print a link
print "<a href=\"$PHP_SELF?action=delete&personID=$personID&educationID=$educationID\">delete</a><br>"
}
} else {
print "Person not found";
}
So your PHP script would have this at the end to list the educations for the person but would also contain a switch statement at the beginning to look at $action (which is passed from the delete link). If $action="delete" then delete the record in PeopleToEducation with EducationID = $educationID and PersonID = $personID (which are also passed from the delete link).
switch($action) {
case "delete":
$query = "DELETE FROM PeopleToEducation WHERE EducationID = $educationID AND PersonID = $personID";
$result = mysql_query($query);
// Probably should do some error checking here
break;
}
... then the stuff above
Using this method you could create other actions such as add, rename etc. and handle these at the start of the script.
Hope the ramblings are of some help
Dom
🙂
