MySQL get all affected rows for multiple statement

问题:

MySQL/PHP:

For a query with multiple statements, which deletes rows in four different tables, I want to know the combined number of affected rows. The PHP manual says I'll only get the result from the last 'operation', which suggests it will only tell me how many rows were affected by the last of the DELETE statements. How to get around this?

$deleteContactSQL = "DELETE FROM `persons` WHERE `persons`.`id` = '$person' AND `owner = '$user' AND `userOrContact` = 'contact';
             DELETE FROM `tabs` WHERE `person` = '$person' AND `ownerIdentity` = '$user' AND `selfOrOther` = 'other';
             DELETE FROM `tabAccess` WHERE `person`= '$person' AND `givenToIdentity` = '$user';
             DELETE FROM `personAccess` WHERE `viewedPerson` = '$person' AND `viewerIdentity` = '$user';
             ;";
include $_SERVER['DOCUMENT_ROOT'].'/goalview/includes/db.inc.php';
$deleteContacts = mysqli_query($link, $deleteContactSQL);
$success = mysqli_affected_rows($link);

回答1:

Something like this maybe?

include $_SERVER['DOCUMENT_ROOT'] . '/goalview/includes/db.inc.php';

$sql = array();
$sql[] = "DELETE FROM `persons` WHERE `persons`.`id` = '$person' AND `owner = '$user' AND `userOrContact` = 'contact';"
$sql[] = "DELETE FROM `tabs` WHERE `person` = '$person' AND `ownerIdentity` = '$user' AND `selfOrOther` = 'other';"
$sql[] = "DELETE FROM `tabAccess` WHERE `person`= '$person' AND `givenToIdentity` = '$user';"
$sql[] = "DELETE FROM `personAccess` WHERE `viewedPerson` = '$person' AND `viewerIdentity` = '$user';"

$aff_rows = 0;

foreach($sql as $current_sql)
{
 $deleteContacts = mysqli_query($link, $current_sql); 
 $aff_rows = $aff_rows + mysqli_affected_rows($link);
}

回答2:

Here is a compact, procedural-style mysqli_multi_query() solution for counting combined affected rows:

$deleteContactSQL="DELETE FROM `persons` WHERE `id`='$person' AND `owner='$user' AND `userOrContact`='contact';
         DELETE FROM `tabs` WHERE `person`='$person' AND `ownerIdentity`='$user' AND `selfOrOther`='other';
         DELETE FROM `tabAccess` WHERE `person`='$person' AND `givenToIdentity`='$user';
         DELETE FROM `personAccess` WHERE `viewedPerson`='$person' AND `viewerIdentity`='$user';";
include $_SERVER['DOCUMENT_ROOT'].'/goalview/includes/db.inc.php';
if(mysqli_multi_query($link,$deleteContactSQL)){
    do{
        $success+=mysqli_affected_rows($link);
    }while(mysqli_more_results($link) && mysqli_next_result($link));
}

Alternatively, this group of queries may be a good candidate for some TRIGGERs in the persons table.

回答3:

I'd be doing it like this, but, I do like to keep things simple which not everyone can appreciate ;)

$deleteContactSQL = sprintf("call cascade_delete_persons(%d,%d)", $person, $user);
$deleteContacts = mysqli_query($link, $deleteContactSQL);

drop procedure if exists cascade_delete_persons;

delimiter #

create procedure cascade_delete_persons
(
in p_pid int unsigned,
in p_oid int unsigned
)
begin

declare v_persons_count int unsigned default 0;
declare v_tabs_count int unsigned default 0;

    delete from persons where id = p_pid and owner = p_oid and userOrContact = 'contact';
    set v_persons_count = row_count();

    delete from tabs where person = p_pid and ownerIdentity = p_oid and selfOrOther = 'other';
    set v_tabs_count = row_count();

    -- etc...

    select v_persons_count as person_count, v_tabs_count as tabs_count;

end #

delimiter ;

You can use this method too if you must : http://php.net/manual/en/mysqli.multi-query.php