Avoiding orphan records

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

If I want to delete a record but want to find out if that record_id is assigned to any other record in my db how do I appraoch this?

So far my login is this:

Code:

$sql = "SELECT COUNT (*) as numrows FROM " . $prefix . "_milpacs_members WHERE weapon_id = '$id'"; 


   $row = $db->sql_query($sql);


   $return = $row['numrows'];

I then display:

Quote:

There are <?php echo $row['numrows'] ?> soldiers who have been assigned this weapon. Deleting this weapon will erase this from their record!

Since my roster is displayed by matching unit_id = unit_id and weapon_id = weapon_id and such in various tables, I could have several members not displayed in my roster if I delete a weapon from my weapon table.

I think I need to enter a weapon_id of 0 into my weapon table and make the name "unassigned" then just update my members table for those who are assigned to the weapon I am attempting to delete.

Did I explain this right?

Posted: Thu Mar 30, 2006 12:13 pm

Here is my code so far.

Code:

// Delete values in weapons table


$weapon_id = intval($_GET['id']);


if ($op == "ConfirmDelWeapon") {


   $result = $db->sql_query("UPDATE " . $prefix . "_milpacs_members SET weapon_id='0' WHERE weapon_id='$weapon_id'");


   


   $result = $db->sql_query("DELETE FROM " . $prefix . "_milpacs_weapons WHERE weapon_id ='$weapon_id'");


   Header("Location: /admin.php?op=milpacs");


   die();


} else {


   //Count if any soldier has been assigned this weapon. 


   $sql = "SELECT COUNT (*) as numrows FROM " . $prefix . "_milpacs_members WHERE weapon_id = '$weapon_id'"; 


   $row = $db->sql_query($sql);   


   


echo "<form name=\"delweapon\" action=\"".$admin_file.".php\" method=\"post\">";


OpenTable();


?>


<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">There are <?php echo $row['numrows'] ?> soldiers who have been assigned this weapon.  Deleting this weapon will erase this from their record and set there weapon to unassigned!</font></b>


      </td>


   </tr>


   <tr>


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


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


      </td>


   </tr>   


</table>


<br>


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


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


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


</form>

registered · Posted: Fri Mar 31, 2006 7:42 am

Quote:

I think I need to enter a weapon_id of 0 into my weapon table and make the name "unassigned" then just update my members table for those who are assigned to the weapon I am attempting to delete.

That is one approach and may be your best option. However, another option is to change your roster SQL to use an outer join. I gave you an example of this in a previous post a few days back. You should be able to modify that SQL I provided to still display your full roster even if a weapon is not assigned.