Avoiding orphan records

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I have a script that deletes a record, however the id for that record may exist in another table. What is the best way to handle this to avoid orphan records?

Code:

if ($op == "confirm") {


   $id = intval($_POST['id']);


   $result2 = $db->sql_query("DELETE FROM " . $prefix . "_milpacs_subunit WHERE subunit_id ='$id'");


   Header("Location: /milpacs.php");


   die(); 


} else {


?>


<form name="delsubunit" method="post" action="milpacs.php?aop=delsubunit">  


<table border="2" cellpadding="2" align="center" cellspacing="0" style="border-collapse: collapse;" bgcolor="#666633" bordercolor="#111111" width="100%">


   <tr>


      <td colspan=2 align="center" bgcolor="#777777">


      <b><font color="#000000">Are you sure you want to delete this record?</font></b>


      </td>


   </tr>   


</table>


<br>


<input type="hidden" name="op" value="confirm"/>


<input type="hidden" name="id" value="<?php echo $id ?>"/>


<input type="submit" align="center" name="Submit" value="Delete"/>


</form>

subunit_id may exist in my milpacs_members table as well. Should I SET them all to zero, or run a query to notify that

Quote:

echo " <p>$count members still belong to this Sub Unit. You will need to fix the roster after you delete this record</p>";

Code:

$result = $db->sql_query("SELECT Count(*) FROM " . $prefix . "_milpacs_members WHERE subunit_id ='$id'");


$count = $db->sql_fetchrow($result);

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

That would be your preference. If it were me, I would modify the top code where you ask them to confirm the deletion of the record to tell them right then and there that there are $count members still in the sub unit. Then, if they confirm it, add the delete query for the members table in the if ($op == "confirm") { logic.