I have a page for a PHPNuke module called MILPACS. It is my editsoldier page and I have a problem with how it joins records from different tables. My weapon table has weapon_id and weapon_name. My member table has unique_id for each soldier and weapon_id. When I go to editsoldier I pull what weapon_id that soldier is assigned by matching a.weapon_id to b.weapon.id using some aliases.
Let say a soldier is assigned a weapon such as Springfield with a weapon_id of say 3, but an administrator decides to delete Springfield from the weapon table. His weapon_id is still 3 in the members table but there is no match in the weapon table. My admin roster I can click on his name to pull up the editsoldier page but none of his data is there because it can't match the weapon_id.
$result = $db->sql_query("SELECT * FROM " . $prefix . "_milpacs_members mm JOIN " . $prefix . "_milpacs_units mu JOIN " . $prefix . "_milpacs_weapons mw WHERE mm.uniqueid ='$id' AND mm.unit_id = mu.unit_id AND mm.weapon_id = mw.weapon_id");
Someone said use and OUTER JOIN to fix this but I have tried and can't get things to work. I will have this same issue with units if for some reason an admin deletes a unit a soldier is assigned to.
You can see the module in action on a test site I have set up for development work.
http://milpacs.3rd-infantry-division.net
Login using the block
Login: Admin
Password: demo
I am also interested in getting a hired professional to help with this. I have a little $$ to spend for your time and effort. Must have references.
I need help on protecting against orphaned records (deleting records).
Form validation and CSS & HTML 4.01 Compliance
Regards,
-Donovan
