Re: PHP/MySQL: How do I find orphaned records?

Beau, What you could prevent the course_type being deleted then there are course attached to if. Force the user to re-assign the course to a different course_type. Or Create a course_type called "Unknown" with an id of say, 57 After you delete a course_type do: update courses set course_type_id = 57 where course_type_id = deleted_course_id Which will re-assign all the orpans to an "Unknown" cource_type; a course trash bin if you will. hth, David. ----- Original Message ----- From: Beau Hartshorne <beauh@home.com> To: PHP <php-db@lists.php.net> Sent: Wednesday, August 30, 2000 6:01 AM Subject: [PHP-DB] PHP/MySQL: How do I find orphaned records? > I have a database with two tables. One is called course_type, and the other > is called course. Each course is assigned a course type by the user. The > user can insert or delete course types, so is able to delete a course type > that still has courses attached to it. Once they do this, all of the courses > that were once attached to that type become orphaned. > > I would like a list of these orphaned courses to be displayed to the user, > so they can reattach course types to them or delete them if they like. > > I have two ideas in mind: > > 1. Write some SQL query that finds all of the orphaned records and returns a > result array. I believe this is either difficult, messy or impossible. > > 2. Write some PHP function that locks the table, and marks each course that > is attached to the type that is about to be deleted. I would then write an > SQL query that found all of the marked courses, and return a result array. > > What is the best way to accomplish this in PHP/MySQL?