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

Addressed to: Beau Hartshorne <beauh@home.com> php-db@lists.php.net ** Reply to note from Beau Hartshorne <beauh@home.com> Tue, 29 Aug 2000 22:01:53 -0700 > > Hi, > > 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. > This is not so hard... Assuming CourseID is the field that links the tables. SELECT whatever FROM course_type LEFT JOIN course USING( CourseID ) WHERE course_type.CourseID IS NULL This returns the entire list of orphan course entries, not just the ones orphaned by the last delete. If you want orphans from a single CourseID: SELECT whatever FROM course WHERE CourseID = '$CourseID' > 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? I would suggest this: The user indicates he wants to delete a course_type. You paint a page that displayes the course type, and the course information associated with that course_type. SELECT * from course_type WHERE CourseID = '$CourseID' # Display it SELECT * from course WHERE CourseID = '$CourseID' # Display them At the bottom ask the question "Do you want to delete these entries?", and provide buttons for Yes and No. If the user selects Yes, then you do: DELETE FROM course_type WHERE CourseID = '$CourseID' and DELETE FROM course WHERE CourseID = '$CourseID' And they are all gone. If you are going to re-connect them to a new course_type, why not just UPDATE the existing course_type and not pay the delete/insert penalty. One thing you DO NOT want to do is lock ANY kind of resource during user think time. EVER! What happens if they walk away from the computer while they are holding the lock? Nobody gets to do anything till they come back and unlock it. It could take nours, or months if they left for vacation. (NEVER under-estimate the power of human stupidity!) Rick Widmer Internet Marketing Specialists www.developersdesk.com