MySQL在一个查询中获取多个语句的所有受影响的行 [英] MySQL get all affected rows for multiple statements in one query

问题描述

MySQL/PHP:

对于具有多个语句的查询，该查询删除了四个不同表中的行，我想知道受影响的行的总数. PHP手册说，我只会从最后一个操作"中获得结果，这表明它只会告诉我最后一个DELETE语句影响了多少行. 如何解决这个问题?

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);

推荐答案

像这样的事情?

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);
}

这篇关于MySQL在一个查询中获取多个语句的所有受影响的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！