Delete a row from a related table on $_POST['action'] === 'remove'

crush123

What I would like to do is delete an orphan record in a joined table if it exists.
The $_POST['action'] === 'remove' only passes back the id (PK) of the main table to the server, which is not referenced in my joined table.
Can I return an additional value ?
Eg my contacts table contains contactid and memberid.
On deleting the contact, I may want to delete the memberid in the members table.

I have tried using the contactid to lookup the memberid and then perform a delete, but it doesn’t quite work (seems to take 2 attempts to delete) and seems a bit convoluted.
Is there a better way, or have I got my code running in the wrong order ?

if ( isset($_POST['action']) && $_POST['action'] === 'remove' ) {
//retrieve value(s) from editor and format as a comma separated string
//these are the selected contactids being removed from the contacts table
//we need to pass these values back into a lookup query to determine which memberids they correspond to
//we then use these memberids to delete rows from tblmembers
$rowid = ltrim(str_replace('row_',',',implode($_POST['id'])),",");
//print_r($rowid);

$lookup = $db->sql( "SELECT MemberID FROM tblcontacts WHERE ContactID IN (".$rowid.")");
//print_r($lookup);
while($row = $lookup->fetch()) {
    //echo $row['MemberID'];
    $sql = $db->sql( "DELETE FROM tblmembers WHERE MemberID = ".$row['MemberID']."");
}

allan

You could combine it into a single SQL statement - DELETE FROM ... WHERE MemberID IN (SELECT ... ). Sub-queries are awesome for this kind of thing and that is probably the best way.

Editor 1.5 the submitted data on delete will include the row details - although it is worth being aware that for the new features of 1.5 I've had to alter the data change protocol. I will be publishing code to add backwards compatibility and a full description of the change when 1.5 is released.

Allan

crush123

Thanks Allan.

I tried the sub-query, its a bit neater.

As an aside, I am assuming that I should run this code snippet within the ajax editor instance and before $data->process( $_POST ) ?

Reason I ask is if put it after that, the delete doesn't seem to occur, presumably because the ContactID I am using as a filter no longer exists in the table.

allan

Yes - you'll need to run it before process() for exactly the reason you state.

Allan

crush123

Great, thx