MySQL get all affected rows for multiple statements in one query

Question

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

AFAIK, you'll have to execute each `DELETE` and fetch the affected rows, one by one. Correct me if I'm wrong. :-) — acm, Nov 12 '10 at 10:04
If these tables all have 1-many relationships from `persons`, then you could use a multi-table delete. MySQL in this case will delete the rows from all of the tables in one statement and return the count of rows removed as you require. — a'r, Nov 12 '10 at 18:08

score 2 · Accepted Answer · answered Nov 12 '10 at 10:06

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

mickmackusa · Answer 2 · 2014-03-14T06:56:07.873

1

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.

edited Mar 14 '14 at 06:56

answered Mar 14 '14 at 04:59

mickmackusa

43,625
12
83
136

1

@YevgeniyAfanasyev I wonder if you have an error in your code. FYI, my snippet of code is not built for handling queries with result sets, just affected rows. According to http://php.net/manual/en/mysqli.affected-rows.php mysqli_affected_rows() Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query. See also this post: http://stackoverflow.com/questions/14715889/strict-standards-mysqli-next-result-error-with-mysqli-multi-query/22469722#22469722 I know I tested that one before posting. I hope this helps you; if not, post your code. – mickmackusa Dec 13 '14 at 15:39
OK, I owe an apology. The way you suggest is working. Please, forgive me. I've also found a source of my confusion. Here is the strange thing: I put 'select "+" as ok from dual;' as the last query in my multi-query script and "$mysqli->affected_rows" returned "-1". Which is confusing. In addition, I've tried "set @t=1;" and "START TRANSACTION;" - they were returning 0 as they should. – Yevgeniy Afanasyev Dec 17 '14 at 05:51
I was fighting another problem when I was reading your answer. I mean - ok, we can count affected rows, but how we can be sure if whole the multi-query-script was performed successfully? I put my vision into an answer, please, have a look: http://stackoverflow.com/questions/7395326/how-do-i-ensure-i-caught-all-errors-from-mysqlimulti-query/27394448#27394448 – Yevgeniy Afanasyev Dec 17 '14 at 05:57
@YevgeniyAfanasyev As explained and shown @ http://stackoverflow.com/questions/14715889/strict-standards-mysqli-next-result-error-with-mysqli-multi-query/22469722#22469722 if there is a syntax error on any query within the mysqli_multi_query(), it will immediately stop running queries and mysqli_error() will hold the error detail. After mysqli_multi_query() finishes, check mysqli_error($mysqli), if it is false then COMPLETE SUCCESS; otherwise there was a syntax failure. – mickmackusa Dec 17 '14 at 15:52
Sorry, in my experimen $mysqli->error always return same text "Commands out of sync; you can't run this command now", it doesn't change if the $mysqli->multi_query($query); was successful or failed. I tried with SQL: "START TRANSACTION; set @t=1; INSERT INTO test_table (ftext) VALUES ("two5");INSERT INTO test_table (ftext) VALUES ("two6"); select "+" as ok from dual ; COMMIT;" And to make it fail, I just use a table name that doesn't exist in database. – Yevgeniy Afanasyev Dec 23 '14 at 02:21
@YevgeniyAfanasyev, because mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); converts errors into exceptions, multi_query doesn't brake and crashes headfirst into the next mysqli function it meets generating the out-of-sync message. If you want to use multi_query without offering it access to errors, you will have to create custom breakpoints within the multi_query process when you encounter an exception message. Does this help? – mickmackusa Jan 17 '15 at 12:37
Sorry, I don't understand, multi_query is not a process, it is a single function, you cannot create custom breakpoints in it. And I did not encounter an exception message, there was no exception, even for faulty SQL. $mysqli->error - on the other hand, is always ready to show the error, even if it did not occur. That is why I don't trust $mysqli->error. – Yevgeniy Afanasyev Jan 19 '15 at 03:59
@YevgeniyAfanasyev, I am referring to multi_query coupled with the do-while loop as a process. The conditions in my while statement are the only current breakpoints. Does the "Commands out of sync" not break the multi_query loop? or are you saying that all of the queries are still being processed? – mickmackusa Jan 28 '15 at 03:57
@YevgeniyAfanasyev, I am curious about something else based on your comment from Dec 23 '14. Are you perhaps calling a query or multi_query before the one in question? Is everything "in sync" to begin with? $myslqi-error is not self-cleansing. If you are running any queries prior, please check that that $mysqli->error is empty before multi_query is started. Just thought I would try debugging in the dark. – mickmackusa Jan 28 '15 at 04:14
thanks, I did not know that I should clean $myslqi-error manually, It explains everything, thank you. – Yevgeniy Afanasyev Jan 28 '15 at 23:11
@YevgeniyAfanasyev Whoo-hoo! What a lucky guess. Happy to help. – mickmackusa Jan 30 '15 at 13:25
p.s. for the record, I would advise you to address the part that is actually causing the mysqli error; versus manually "cleaning" it. – mickmackusa Jan 30 '15 at 15:03

Jon Black · Answer 3 · 2010-11-12T10:37:33.573

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

MySQL get all affected rows for multiple statements in one query

3 Answers3

Linked